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