862 lines
34 KiB
Plaintext
862 lines
34 KiB
Plaintext
#
|
|
# Tests that the optional column columns work both of they are present
|
|
# and absent. PATTERN_DIGEST and NORMALIZED_PATTERN are optional write-
|
|
# only columns that are not present by default. The MESSAGE column is
|
|
# optional, but included in the install script by default.
|
|
#
|
|
# Query rewrite plugin was installed.
|
|
#
|
|
# Testing with PATTERN_DIGEST and NORMALIZED_PATTERN columns.
|
|
#
|
|
CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) );
|
|
INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' );
|
|
CREATE TABLE t2 ( a VARCHAR(10) );
|
|
INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' );
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT ?', 'SELECT "literal"' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
1 SELECT ? NULL SELECT "literal" YES NULL NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
1 SELECT ? NULL SELECT "literal" YES NULL f1ea6cee1b4090c784ccd0e75f7a9775104fe39bfb43145466867e56b7d469e7 select ?
|
|
SELECT NULL;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 'abc';
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1.1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 123456789123456789123456789123456789123456789123456789;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 1
|
|
Rewriter_number_reloads 2
|
|
Rewriter_number_rewritten_queries 5
|
|
Rewriter_reload_error OFF
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?',
|
|
'SELECT a FROM test.t1 WHERE a = ?' ),
|
|
( 'SELECT a FROM test.t1',
|
|
'SELECT * FROM test.t1 WHERE a = \'abc\'' ),
|
|
( 'SELECT a FROM test.t1 WHERE b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ?' ),
|
|
( 'SELECT * FROM test.t2',
|
|
'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ),
|
|
( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ? OR a = ?' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
2 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL NULL
|
|
3 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL NULL
|
|
4 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL NULL
|
|
5 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL NULL
|
|
6 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
2 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL ef8564911c7169abd9f303424ccaaa1a3aa7e8488d1468a2a044c125e85bca99 select `*` from `test`.`t1` where (((`a` = ?) and true) or (`b` = ?))
|
|
3 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL 7c39c64e14e0f3a25abc71f32279432f05da697f248e8d82d7854c68470478c1 select `a` from `test`.`t1`
|
|
4 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL 7413ff2ffaba988de84f5d7d361eeae2df827aa3f091cd518dfd57ea6164a965 select `a` from `test`.`t1` where (`b` = ?)
|
|
5 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL 884ac51a7452a8d827905d492c376c57166a8e4f50be34167d573bb0411a9abf select `*` from `test`.`t2`
|
|
6 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL 4d973ae20c094322c00503502d627487ec9ffa81dc510c2e7c3b1ee537414620 select `*` from `test`.`t1` where ((`a` = ?) or (`b` = ?))
|
|
# Check a query that doesn't have a rewrite rule doesn't get rewritten.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check a query with just one parameter.
|
|
SELECT a FROM test.t1 WHERE b = 'qrs';
|
|
a b
|
|
nop qrs
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin
|
|
# Check a rule with parameter truncation (fewer parameters in the
|
|
# output query than in the input query);
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm';
|
|
a
|
|
abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'' rewritten to 'SELECT a FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm';
|
|
a b
|
|
ghi klm
|
|
# Check a non parameterized query.
|
|
SELECT * from t1 WHERE a = 'abc';
|
|
a b
|
|
abc def
|
|
# Check that a non-rewritten query does not yield a warning.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check that a query is not rewritten if the query corresponds to a
|
|
# replacement.
|
|
SELECT a FROM test.t1;
|
|
a b
|
|
abc def
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
# Check that we can execute a rewrite more than once.
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
# Remove the warnings.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check parameter switching in a query rewrite
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with whitespace.
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with comments.
|
|
SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 5
|
|
Rewriter_number_reloads 3
|
|
Rewriter_number_rewritten_queries 13
|
|
Rewriter_reload_error OFF
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ),
|
|
( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ),
|
|
( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"');
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
7 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL NULL NULL
|
|
8 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL NULL
|
|
9 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message pattern_digest normalized_pattern
|
|
7 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL e3877e22e388a2adadc61a082fb14f50c1c8d7f81341d82195f4a606c15b58f3 select ?,?
|
|
8 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL e3877e22e388a2adadc61a082fb14f50c1c8d7f81341d82195f4a606c15b58f3 select ?,?
|
|
9 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL f1ea6cee1b4090c784ccd0e75f7a9775104fe39bfb43145466867e56b7d469e7 select ?
|
|
SELECT 1, 1;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 1, 2;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 2, 1;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 2, 2;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 3, 1;
|
|
3 1
|
|
3 1
|
|
SELECT 3, 2;
|
|
3 2
|
|
3 2
|
|
SELECT 'The_original_query';
|
|
The_rewritten_query
|
|
The_rewritten_query
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
#
|
|
# Testing with a NORMALIZED_PATTERN column.
|
|
#
|
|
ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN pattern_digest;
|
|
CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) );
|
|
INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' );
|
|
CREATE TABLE t2 ( a VARCHAR(10) );
|
|
INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' );
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT ?', 'SELECT "literal"' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
10 SELECT ? NULL SELECT "literal" YES NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
10 SELECT ? NULL SELECT "literal" YES NULL select ?
|
|
SELECT NULL;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 'abc';
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1.1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 123456789123456789123456789123456789123456789123456789;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 1
|
|
Rewriter_number_reloads 5
|
|
Rewriter_number_rewritten_queries 23
|
|
Rewriter_reload_error OFF
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?',
|
|
'SELECT a FROM test.t1 WHERE a = ?' ),
|
|
( 'SELECT a FROM test.t1',
|
|
'SELECT * FROM test.t1 WHERE a = \'abc\'' ),
|
|
( 'SELECT a FROM test.t1 WHERE b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ?' ),
|
|
( 'SELECT * FROM test.t2',
|
|
'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ),
|
|
( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ? OR a = ?' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
11 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL
|
|
12 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL
|
|
13 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL
|
|
14 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL
|
|
15 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
11 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL select `*` from `test`.`t1` where (((`a` = ?) and true) or (`b` = ?))
|
|
12 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL select `a` from `test`.`t1`
|
|
13 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL select `a` from `test`.`t1` where (`b` = ?)
|
|
14 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL select `*` from `test`.`t2`
|
|
15 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL select `*` from `test`.`t1` where ((`a` = ?) or (`b` = ?))
|
|
# Check a query that doesn't have a rewrite rule doesn't get rewritten.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check a query with just one parameter.
|
|
SELECT a FROM test.t1 WHERE b = 'qrs';
|
|
a b
|
|
nop qrs
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin
|
|
# Check a rule with parameter truncation (fewer parameters in the
|
|
# output query than in the input query);
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm';
|
|
a
|
|
abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'' rewritten to 'SELECT a FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm';
|
|
a b
|
|
ghi klm
|
|
# Check a non parameterized query.
|
|
SELECT * from t1 WHERE a = 'abc';
|
|
a b
|
|
abc def
|
|
# Check that a non-rewritten query does not yield a warning.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check that a query is not rewritten if the query corresponds to a
|
|
# replacement.
|
|
SELECT a FROM test.t1;
|
|
a b
|
|
abc def
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
# Check that we can execute a rewrite more than once.
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
# Remove the warnings.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check parameter switching in a query rewrite
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with whitespace.
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with comments.
|
|
SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 5
|
|
Rewriter_number_reloads 6
|
|
Rewriter_number_rewritten_queries 31
|
|
Rewriter_reload_error OFF
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ),
|
|
( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ),
|
|
( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"');
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
16 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL NULL
|
|
17 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL
|
|
18 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled message normalized_pattern
|
|
16 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL select ?,?
|
|
17 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL select ?,?
|
|
18 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL select ?
|
|
SELECT 1, 1;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 1, 2;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 2, 1;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 2, 2;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 3, 1;
|
|
3 1
|
|
3 1
|
|
SELECT 3, 2;
|
|
3 2
|
|
3 2
|
|
SELECT 'The_original_query';
|
|
The_rewritten_query
|
|
The_rewritten_query
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
#
|
|
# Testing with a NORMALIZED_PATTERN column but no MESSAGE column.
|
|
#
|
|
ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN message;
|
|
CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) );
|
|
INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' );
|
|
CREATE TABLE t2 ( a VARCHAR(10) );
|
|
INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' );
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT ?', 'SELECT "literal"' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
19 SELECT ? NULL SELECT "literal" YES NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
19 SELECT ? NULL SELECT "literal" YES select ?
|
|
SELECT NULL;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 'abc';
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1.1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 123456789123456789123456789123456789123456789123456789;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 1
|
|
Rewriter_number_reloads 8
|
|
Rewriter_number_rewritten_queries 41
|
|
Rewriter_reload_error OFF
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?',
|
|
'SELECT a FROM test.t1 WHERE a = ?' ),
|
|
( 'SELECT a FROM test.t1',
|
|
'SELECT * FROM test.t1 WHERE a = \'abc\'' ),
|
|
( 'SELECT a FROM test.t1 WHERE b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ?' ),
|
|
( 'SELECT * FROM test.t2',
|
|
'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ),
|
|
( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ? OR a = ?' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
20 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL
|
|
21 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL
|
|
22 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL
|
|
23 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL
|
|
24 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
20 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES select `*` from `test`.`t1` where (((`a` = ?) and true) or (`b` = ?))
|
|
21 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES select `a` from `test`.`t1`
|
|
22 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES select `a` from `test`.`t1` where (`b` = ?)
|
|
23 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES select `*` from `test`.`t2`
|
|
24 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES select `*` from `test`.`t1` where ((`a` = ?) or (`b` = ?))
|
|
# Check a query that doesn't have a rewrite rule doesn't get rewritten.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check a query with just one parameter.
|
|
SELECT a FROM test.t1 WHERE b = 'qrs';
|
|
a b
|
|
nop qrs
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin
|
|
# Check a rule with parameter truncation (fewer parameters in the
|
|
# output query than in the input query);
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm';
|
|
a
|
|
abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'' rewritten to 'SELECT a FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm';
|
|
a b
|
|
ghi klm
|
|
# Check a non parameterized query.
|
|
SELECT * from t1 WHERE a = 'abc';
|
|
a b
|
|
abc def
|
|
# Check that a non-rewritten query does not yield a warning.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check that a query is not rewritten if the query corresponds to a
|
|
# replacement.
|
|
SELECT a FROM test.t1;
|
|
a b
|
|
abc def
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
# Check that we can execute a rewrite more than once.
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
# Remove the warnings.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check parameter switching in a query rewrite
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with whitespace.
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with comments.
|
|
SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 5
|
|
Rewriter_number_reloads 9
|
|
Rewriter_number_rewritten_queries 49
|
|
Rewriter_reload_error OFF
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ),
|
|
( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ),
|
|
( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"');
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
25 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL
|
|
26 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL
|
|
27 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled normalized_pattern
|
|
25 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES select ?,?
|
|
26 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES select ?,?
|
|
27 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES select ?
|
|
SELECT 1, 1;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 1, 2;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 2, 1;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 2, 2;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 3, 1;
|
|
3 1
|
|
3 1
|
|
SELECT 3, 2;
|
|
3 2
|
|
3 2
|
|
SELECT 'The_original_query';
|
|
The_rewritten_query
|
|
The_rewritten_query
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
#
|
|
# Testing with no optional columns.
|
|
#
|
|
ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN normalized_pattern;
|
|
CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) );
|
|
INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' );
|
|
CREATE TABLE t2 ( a VARCHAR(10) );
|
|
INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' );
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT ?', 'SELECT "literal"' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
28 SELECT ? NULL SELECT "literal" YES
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
28 SELECT ? NULL SELECT "literal" YES
|
|
SELECT NULL;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 'abc';
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 1.1;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
SELECT 123456789123456789123456789123456789123456789123456789;
|
|
literal
|
|
literal
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 1
|
|
Rewriter_number_reloads 11
|
|
Rewriter_number_rewritten_queries 59
|
|
Rewriter_reload_error OFF
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?',
|
|
'SELECT a FROM test.t1 WHERE a = ?' ),
|
|
( 'SELECT a FROM test.t1',
|
|
'SELECT * FROM test.t1 WHERE a = \'abc\'' ),
|
|
( 'SELECT a FROM test.t1 WHERE b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ?' ),
|
|
( 'SELECT * FROM test.t2',
|
|
'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ),
|
|
( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?',
|
|
'SELECT * FROM test.t1 WHERE b = ? OR a = ?' );
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
29 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES
|
|
30 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES
|
|
31 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES
|
|
32 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES
|
|
33 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
29 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES
|
|
30 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES
|
|
31 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES
|
|
32 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES
|
|
33 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES
|
|
# Check a query that doesn't have a rewrite rule doesn't get rewritten.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check a query with just one parameter.
|
|
SELECT a FROM test.t1 WHERE b = 'qrs';
|
|
a b
|
|
nop qrs
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin
|
|
# Check a rule with parameter truncation (fewer parameters in the
|
|
# output query than in the input query);
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm';
|
|
a
|
|
abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'' rewritten to 'SELECT a FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm';
|
|
a b
|
|
ghi klm
|
|
# Check a non parameterized query.
|
|
SELECT * from t1 WHERE a = 'abc';
|
|
a b
|
|
abc def
|
|
# Check that a non-rewritten query does not yield a warning.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check that a query is not rewritten if the query corresponds to a
|
|
# replacement.
|
|
SELECT a FROM test.t1;
|
|
a b
|
|
abc def
|
|
Warnings:
|
|
Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin
|
|
# Check that we can execute a rewrite more than once.
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
SELECT * FROM test.t2;
|
|
a b a
|
|
abc def abc
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' by a query rewrite plugin
|
|
# Remove the warnings.
|
|
SELECT b FROM test.t1;
|
|
b
|
|
def
|
|
klm
|
|
qrs
|
|
# Check parameter switching in a query rewrite
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with whitespace.
|
|
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Verify with comments.
|
|
SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi';
|
|
a b
|
|
abc def
|
|
ghi klm
|
|
Warnings:
|
|
Note 1105 Query 'SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'def' OR a = 'ghi'' by a query rewrite plugin
|
|
# Check our status variables.
|
|
SHOW STATUS LIKE 'Rewriter%';
|
|
Variable_name Value
|
|
Rewriter_number_loaded_rules 5
|
|
Rewriter_number_reloads 12
|
|
Rewriter_number_rewritten_queries 67
|
|
Rewriter_reload_error OFF
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
# Test of literals matching.
|
|
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
|
|
VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ),
|
|
( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ),
|
|
( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"');
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
34 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES
|
|
35 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES
|
|
36 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES
|
|
CALL query_rewrite.flush_rewrite_rules();
|
|
SELECT * FROM query_rewrite.rewrite_rules;
|
|
id pattern pattern_database replacement enabled
|
|
34 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES
|
|
35 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES
|
|
36 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES
|
|
SELECT 1, 1;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 1, 2;
|
|
rewritten w/rule 1
|
|
rewritten w/rule 1
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
|
|
SELECT 2, 1;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 2, 2;
|
|
rewritten w/rule 2
|
|
rewritten w/rule 2
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
|
|
SELECT 3, 1;
|
|
3 1
|
|
3 1
|
|
SELECT 3, 2;
|
|
3 2
|
|
3 2
|
|
SELECT 'The_original_query';
|
|
The_rewritten_query
|
|
The_rewritten_query
|
|
Warnings:
|
|
Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
DELETE FROM query_rewrite.rewrite_rules;
|
|
Warnings:
|
|
Warning 1620 Plugin is busy and will be uninstalled on shutdown
|
|
# Query rewrite plugin was queued for uninstalling.
|