| 1 |
CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); |
| 2 |
INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); |
| 3 |
|
| 4 |
CREATE TABLE t2 ( a VARCHAR(10) ); |
| 5 |
INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); |
| 6 |
|
| 7 |
--echo # Test of literals matching. |
| 8 |
|
| 9 |
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) |
| 10 |
VALUES ( 'SELECT ?', 'SELECT "literal"' ); |
| 11 |
|
| 12 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 13 |
CALL query_rewrite.flush_rewrite_rules(); |
| 14 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 15 |
|
| 16 |
SELECT NULL; |
| 17 |
SELECT 'abc'; |
| 18 |
SELECT 1; |
| 19 |
SELECT 1.1; |
| 20 |
SELECT 123456789123456789123456789123456789123456789123456789; |
| 21 |
|
| 22 |
--echo # Check our status variables. |
| 23 |
SHOW STATUS LIKE 'Rewriter%'; |
| 24 |
|
| 25 |
DELETE FROM query_rewrite.rewrite_rules; |
| 26 |
|
| 27 |
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) |
| 28 |
VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', |
| 29 |
'SELECT a FROM test.t1 WHERE a = ?' ), |
| 30 |
|
| 31 |
( 'SELECT a FROM test.t1', |
| 32 |
'SELECT * FROM test.t1 WHERE a = \'abc\'' ), |
| 33 |
|
| 34 |
( 'SELECT a FROM test.t1 WHERE b = ?', |
| 35 |
'SELECT * FROM test.t1 WHERE b = ?' ), |
| 36 |
|
| 37 |
( 'SELECT * FROM test.t2', |
| 38 |
'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), |
| 39 |
|
| 40 |
( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', |
| 41 |
'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); |
| 42 |
|
| 43 |
--sorted_result |
| 44 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 45 |
CALL query_rewrite.flush_rewrite_rules(); |
| 46 |
--sorted_result |
| 47 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 48 |
|
| 49 |
--echo # Check a query that doesn't have a rewrite rule doesn't get rewritten. |
| 50 |
SELECT b FROM test.t1; |
| 51 |
|
| 52 |
--echo # Check a query with just one parameter. |
| 53 |
SELECT a FROM test.t1 WHERE b = 'qrs'; |
| 54 |
|
| 55 |
--echo # Check a rule with parameter truncation (fewer parameters in the |
| 56 |
--echo # output query than in the input query); |
| 57 |
SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; |
| 58 |
SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; |
| 59 |
|
| 60 |
--echo # Check a non parameterized query. |
| 61 |
SELECT * from t1 WHERE a = 'abc'; |
| 62 |
|
| 63 |
--echo # Check that a non-rewritten query does not yield a warning. |
| 64 |
SELECT b FROM test.t1; |
| 65 |
|
| 66 |
--echo # Check that a query is not rewritten if the query corresponds to a |
| 67 |
--echo # replacement. |
| 68 |
SELECT a FROM test.t1; |
| 69 |
|
| 70 |
--echo # Check that we can execute a rewrite more than once. |
| 71 |
SELECT * FROM test.t2; |
| 72 |
|
| 73 |
SELECT * FROM test.t2; |
| 74 |
|
| 75 |
--echo # Remove the warnings. |
| 76 |
SELECT b FROM test.t1; |
| 77 |
|
| 78 |
--echo # Check parameter switching in a query rewrite |
| 79 |
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; |
| 80 |
|
| 81 |
--echo # Verify with whitespace. |
| 82 |
SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; |
| 83 |
|
| 84 |
--echo # Verify with comments. |
| 85 |
SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; |
| 86 |
|
| 87 |
--echo # Check our status variables. |
| 88 |
SHOW STATUS LIKE 'Rewriter%'; |
| 89 |
|
| 90 |
DROP TABLE t1; |
| 91 |
DROP TABLE t2; |
| 92 |
|
| 93 |
DELETE FROM query_rewrite.rewrite_rules; |
| 94 |
|
| 95 |
--echo # Test of literals matching. |
| 96 |
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) |
| 97 |
VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), |
| 98 |
( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), |
| 99 |
( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); |
| 100 |
|
| 101 |
--sorted_result |
| 102 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 103 |
CALL query_rewrite.flush_rewrite_rules(); |
| 104 |
--sorted_result |
| 105 |
SELECT * FROM query_rewrite.rewrite_rules; |
| 106 |
|
| 107 |
SELECT 1, 1; |
| 108 |
SELECT 1, 2; |
| 109 |
SELECT 2, 1; |
| 110 |
SELECT 2, 2; |
| 111 |
SELECT 3, 1; |
| 112 |
SELECT 3, 2; |
| 113 |
SELECT 'The_original_query'; |
| 114 |
|
| 115 |
DELETE FROM query_rewrite.rewrite_rules; |