1# 2# Tests that the optional column columns work both of they are present 3# and absent. PATTERN_DIGEST and NORMALIZED_PATTERN are optional write- 4# only columns that are not present by default. The MESSAGE column is 5# optional, but included in the install script by default. 6# 7# Query rewrite plugin was installed. 8# 9# Testing with PATTERN_DIGEST and NORMALIZED_PATTERN columns. 10# 11CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 12INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 13CREATE TABLE t2 ( a VARCHAR(10) ); 14INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 15# Test of literals matching. 16INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 17VALUES ( 'SELECT ?', 'SELECT "literal"' ); 18SELECT * FROM query_rewrite.rewrite_rules; 19id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 201 SELECT ? NULL SELECT "literal" YES NULL NULL NULL 21CALL query_rewrite.flush_rewrite_rules(); 22SELECT * FROM query_rewrite.rewrite_rules; 23id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 241 SELECT ? NULL SELECT "literal" YES NULL d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae select ? 25SELECT NULL; 26literal 27literal 28Warnings: 29Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 30SELECT 'abc'; 31literal 32literal 33Warnings: 34Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 35SELECT 1; 36literal 37literal 38Warnings: 39Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 40SELECT 1.1; 41literal 42literal 43Warnings: 44Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 45SELECT 123456789123456789123456789123456789123456789123456789; 46literal 47literal 48Warnings: 49Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 50# Check our status variables. 51SHOW STATUS LIKE 'Rewriter%'; 52Variable_name Value 53Rewriter_number_loaded_rules 1 54Rewriter_number_reloads 2 55Rewriter_number_rewritten_queries 5 56Rewriter_reload_error OFF 57DELETE FROM query_rewrite.rewrite_rules; 58INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 59VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 60'SELECT a FROM test.t1 WHERE a = ?' ), 61( 'SELECT a FROM test.t1', 62'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 63( 'SELECT a FROM test.t1 WHERE b = ?', 64'SELECT * FROM test.t1 WHERE b = ?' ), 65( 'SELECT * FROM test.t2', 66'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 67( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 68'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 69SELECT * FROM query_rewrite.rewrite_rules; 70id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 712 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL NULL 723 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL NULL 734 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL NULL 745 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL NULL 756 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL NULL 76CALL query_rewrite.flush_rewrite_rules(); 77SELECT * FROM query_rewrite.rewrite_rules; 78id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 792 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL 013244d601fea8fdb057f1a6fcf1222b6fe0214597fe5ef4aaf96aeabe09069f select `*` from `test`.`t1` where (((`a` = ?) and true) or (`b` = ?)) 803 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL 28939802f16d07e5b5ba42bded44d5bab1108022d2541e345a29040a323b01c8 select `a` from `test`.`t1` 814 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL 07f316122bd78ef48f419a40d301b7ade32dda8162ba40a11711ca428d16a02b select `a` from `test`.`t1` where (`b` = ?) 825 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL 666794284a40552eef7ab3173f130f1eec095c1fd1d5412c2d63dbc3a9f3a0b1 select `*` from `test`.`t2` 836 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL 9e2f0e4b7156eda97bdab476e09da81907bde37c25ebd8e1ada5b4a1b39db71e select `*` from `test`.`t1` where ((`a` = ?) or (`b` = ?)) 84# Check a query that doesn't have a rewrite rule doesn't get rewritten. 85SELECT b FROM test.t1; 86b 87def 88klm 89qrs 90# Check a query with just one parameter. 91SELECT a FROM test.t1 WHERE b = 'qrs'; 92a b 93nop qrs 94Warnings: 95Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 96# Check a rule with parameter truncation (fewer parameters in the 97# output query than in the input query); 98SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 99a 100abc 101Warnings: 102Note 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 103SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 104a b 105ghi klm 106# Check a non parameterized query. 107SELECT * from t1 WHERE a = 'abc'; 108a b 109abc def 110# Check that a non-rewritten query does not yield a warning. 111SELECT b FROM test.t1; 112b 113def 114klm 115qrs 116# Check that a query is not rewritten if the query corresponds to a 117# replacement. 118SELECT a FROM test.t1; 119a b 120abc def 121Warnings: 122Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 123# Check that we can execute a rewrite more than once. 124SELECT * FROM test.t2; 125a b a 126abc def abc 127Warnings: 128Note 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 129SELECT * FROM test.t2; 130a b a 131abc def abc 132Warnings: 133Note 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 134# Remove the warnings. 135SELECT b FROM test.t1; 136b 137def 138klm 139qrs 140# Check parameter switching in a query rewrite 141SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 142a b 143abc def 144ghi klm 145Warnings: 146Note 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 147# Verify with whitespace. 148SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 149a b 150abc def 151ghi klm 152Warnings: 153Note 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 154# Verify with comments. 155SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 156a b 157abc def 158ghi klm 159Warnings: 160Note 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 161# Check our status variables. 162SHOW STATUS LIKE 'Rewriter%'; 163Variable_name Value 164Rewriter_number_loaded_rules 5 165Rewriter_number_reloads 3 166Rewriter_number_rewritten_queries 13 167Rewriter_reload_error OFF 168DROP TABLE t1; 169DROP TABLE t2; 170DELETE FROM query_rewrite.rewrite_rules; 171# Test of literals matching. 172INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 173VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 174( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 175( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 176SELECT * FROM query_rewrite.rewrite_rules; 177id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 1787 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL NULL NULL 1798 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL NULL 1809 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL NULL 181CALL query_rewrite.flush_rewrite_rules(); 182SELECT * FROM query_rewrite.rewrite_rules; 183id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 1847 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL efb26d6aca0766da4a6d37f3e3aa5332542ff42893ff98fbb393da4e8cbd6a10 select ?,? 1858 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL efb26d6aca0766da4a6d37f3e3aa5332542ff42893ff98fbb393da4e8cbd6a10 select ?,? 1869 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae select ? 187SELECT 1, 1; 188rewritten w/rule 1 189rewritten w/rule 1 190Warnings: 191Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 192SELECT 1, 2; 193rewritten w/rule 1 194rewritten w/rule 1 195Warnings: 196Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 197SELECT 2, 1; 198rewritten w/rule 2 199rewritten w/rule 2 200Warnings: 201Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 202SELECT 2, 2; 203rewritten w/rule 2 204rewritten w/rule 2 205Warnings: 206Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 207SELECT 3, 1; 2083 1 2093 1 210SELECT 3, 2; 2113 2 2123 2 213SELECT 'The_original_query'; 214The_rewritten_query 215The_rewritten_query 216Warnings: 217Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 218DELETE FROM query_rewrite.rewrite_rules; 219DELETE FROM query_rewrite.rewrite_rules; 220# 221# Testing with a NORMALIZED_PATTERN column. 222# 223ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN pattern_digest; 224CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 225INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 226CREATE TABLE t2 ( a VARCHAR(10) ); 227INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 228# Test of literals matching. 229INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 230VALUES ( 'SELECT ?', 'SELECT "literal"' ); 231SELECT * FROM query_rewrite.rewrite_rules; 232id pattern pattern_database replacement enabled message normalized_pattern 23310 SELECT ? NULL SELECT "literal" YES NULL NULL 234CALL query_rewrite.flush_rewrite_rules(); 235SELECT * FROM query_rewrite.rewrite_rules; 236id pattern pattern_database replacement enabled message normalized_pattern 23710 SELECT ? NULL SELECT "literal" YES NULL select ? 238SELECT NULL; 239literal 240literal 241Warnings: 242Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 243SELECT 'abc'; 244literal 245literal 246Warnings: 247Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 248SELECT 1; 249literal 250literal 251Warnings: 252Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 253SELECT 1.1; 254literal 255literal 256Warnings: 257Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 258SELECT 123456789123456789123456789123456789123456789123456789; 259literal 260literal 261Warnings: 262Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 263# Check our status variables. 264SHOW STATUS LIKE 'Rewriter%'; 265Variable_name Value 266Rewriter_number_loaded_rules 1 267Rewriter_number_reloads 5 268Rewriter_number_rewritten_queries 23 269Rewriter_reload_error OFF 270DELETE FROM query_rewrite.rewrite_rules; 271INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 272VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 273'SELECT a FROM test.t1 WHERE a = ?' ), 274( 'SELECT a FROM test.t1', 275'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 276( 'SELECT a FROM test.t1 WHERE b = ?', 277'SELECT * FROM test.t1 WHERE b = ?' ), 278( 'SELECT * FROM test.t2', 279'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 280( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 281'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 282SELECT * FROM query_rewrite.rewrite_rules; 283id pattern pattern_database replacement enabled message normalized_pattern 28411 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL 28512 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL 28613 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL 28714 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL 28815 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL 289CALL query_rewrite.flush_rewrite_rules(); 290SELECT * FROM query_rewrite.rewrite_rules; 291id pattern pattern_database replacement enabled message normalized_pattern 29211 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` = ?)) 29312 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL select `a` from `test`.`t1` 29413 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL select `a` from `test`.`t1` where (`b` = ?) 29514 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL select `*` from `test`.`t2` 29615 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` = ?)) 297# Check a query that doesn't have a rewrite rule doesn't get rewritten. 298SELECT b FROM test.t1; 299b 300def 301klm 302qrs 303# Check a query with just one parameter. 304SELECT a FROM test.t1 WHERE b = 'qrs'; 305a b 306nop qrs 307Warnings: 308Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 309# Check a rule with parameter truncation (fewer parameters in the 310# output query than in the input query); 311SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 312a 313abc 314Warnings: 315Note 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 316SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 317a b 318ghi klm 319# Check a non parameterized query. 320SELECT * from t1 WHERE a = 'abc'; 321a b 322abc def 323# Check that a non-rewritten query does not yield a warning. 324SELECT b FROM test.t1; 325b 326def 327klm 328qrs 329# Check that a query is not rewritten if the query corresponds to a 330# replacement. 331SELECT a FROM test.t1; 332a b 333abc def 334Warnings: 335Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 336# Check that we can execute a rewrite more than once. 337SELECT * FROM test.t2; 338a b a 339abc def abc 340Warnings: 341Note 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 342SELECT * FROM test.t2; 343a b a 344abc def abc 345Warnings: 346Note 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 347# Remove the warnings. 348SELECT b FROM test.t1; 349b 350def 351klm 352qrs 353# Check parameter switching in a query rewrite 354SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 355a b 356abc def 357ghi klm 358Warnings: 359Note 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 360# Verify with whitespace. 361SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 362a b 363abc def 364ghi klm 365Warnings: 366Note 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 367# Verify with comments. 368SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 369a b 370abc def 371ghi klm 372Warnings: 373Note 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 374# Check our status variables. 375SHOW STATUS LIKE 'Rewriter%'; 376Variable_name Value 377Rewriter_number_loaded_rules 5 378Rewriter_number_reloads 6 379Rewriter_number_rewritten_queries 31 380Rewriter_reload_error OFF 381DROP TABLE t1; 382DROP TABLE t2; 383DELETE FROM query_rewrite.rewrite_rules; 384# Test of literals matching. 385INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 386VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 387( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 388( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 389SELECT * FROM query_rewrite.rewrite_rules; 390id pattern pattern_database replacement enabled message normalized_pattern 39116 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL NULL 39217 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL 39318 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL 394CALL query_rewrite.flush_rewrite_rules(); 395SELECT * FROM query_rewrite.rewrite_rules; 396id pattern pattern_database replacement enabled message normalized_pattern 39716 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL select ?,? 39817 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL select ?,? 39918 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL select ? 400SELECT 1, 1; 401rewritten w/rule 1 402rewritten w/rule 1 403Warnings: 404Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 405SELECT 1, 2; 406rewritten w/rule 1 407rewritten w/rule 1 408Warnings: 409Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 410SELECT 2, 1; 411rewritten w/rule 2 412rewritten w/rule 2 413Warnings: 414Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 415SELECT 2, 2; 416rewritten w/rule 2 417rewritten w/rule 2 418Warnings: 419Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 420SELECT 3, 1; 4213 1 4223 1 423SELECT 3, 2; 4243 2 4253 2 426SELECT 'The_original_query'; 427The_rewritten_query 428The_rewritten_query 429Warnings: 430Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 431DELETE FROM query_rewrite.rewrite_rules; 432DELETE FROM query_rewrite.rewrite_rules; 433# 434# Testing with a NORMALIZED_PATTERN column but no MESSAGE column. 435# 436ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN message; 437CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 438INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 439CREATE TABLE t2 ( a VARCHAR(10) ); 440INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 441# Test of literals matching. 442INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 443VALUES ( 'SELECT ?', 'SELECT "literal"' ); 444SELECT * FROM query_rewrite.rewrite_rules; 445id pattern pattern_database replacement enabled normalized_pattern 44619 SELECT ? NULL SELECT "literal" YES NULL 447CALL query_rewrite.flush_rewrite_rules(); 448SELECT * FROM query_rewrite.rewrite_rules; 449id pattern pattern_database replacement enabled normalized_pattern 45019 SELECT ? NULL SELECT "literal" YES select ? 451SELECT NULL; 452literal 453literal 454Warnings: 455Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 456SELECT 'abc'; 457literal 458literal 459Warnings: 460Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 461SELECT 1; 462literal 463literal 464Warnings: 465Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 466SELECT 1.1; 467literal 468literal 469Warnings: 470Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 471SELECT 123456789123456789123456789123456789123456789123456789; 472literal 473literal 474Warnings: 475Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 476# Check our status variables. 477SHOW STATUS LIKE 'Rewriter%'; 478Variable_name Value 479Rewriter_number_loaded_rules 1 480Rewriter_number_reloads 8 481Rewriter_number_rewritten_queries 41 482Rewriter_reload_error OFF 483DELETE FROM query_rewrite.rewrite_rules; 484INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 485VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 486'SELECT a FROM test.t1 WHERE a = ?' ), 487( 'SELECT a FROM test.t1', 488'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 489( 'SELECT a FROM test.t1 WHERE b = ?', 490'SELECT * FROM test.t1 WHERE b = ?' ), 491( 'SELECT * FROM test.t2', 492'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 493( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 494'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 495SELECT * FROM query_rewrite.rewrite_rules; 496id pattern pattern_database replacement enabled normalized_pattern 49720 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL 49821 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL 49922 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL 50023 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL 50124 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL 502CALL query_rewrite.flush_rewrite_rules(); 503SELECT * FROM query_rewrite.rewrite_rules; 504id pattern pattern_database replacement enabled normalized_pattern 50520 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` = ?)) 50621 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES select `a` from `test`.`t1` 50722 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES select `a` from `test`.`t1` where (`b` = ?) 50823 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES select `*` from `test`.`t2` 50924 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` = ?)) 510# Check a query that doesn't have a rewrite rule doesn't get rewritten. 511SELECT b FROM test.t1; 512b 513def 514klm 515qrs 516# Check a query with just one parameter. 517SELECT a FROM test.t1 WHERE b = 'qrs'; 518a b 519nop qrs 520Warnings: 521Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 522# Check a rule with parameter truncation (fewer parameters in the 523# output query than in the input query); 524SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 525a 526abc 527Warnings: 528Note 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 529SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 530a b 531ghi klm 532# Check a non parameterized query. 533SELECT * from t1 WHERE a = 'abc'; 534a b 535abc def 536# Check that a non-rewritten query does not yield a warning. 537SELECT b FROM test.t1; 538b 539def 540klm 541qrs 542# Check that a query is not rewritten if the query corresponds to a 543# replacement. 544SELECT a FROM test.t1; 545a b 546abc def 547Warnings: 548Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 549# Check that we can execute a rewrite more than once. 550SELECT * FROM test.t2; 551a b a 552abc def abc 553Warnings: 554Note 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 555SELECT * FROM test.t2; 556a b a 557abc def abc 558Warnings: 559Note 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 560# Remove the warnings. 561SELECT b FROM test.t1; 562b 563def 564klm 565qrs 566# Check parameter switching in a query rewrite 567SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 568a b 569abc def 570ghi klm 571Warnings: 572Note 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 573# Verify with whitespace. 574SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 575a b 576abc def 577ghi klm 578Warnings: 579Note 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 580# Verify with comments. 581SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 582a b 583abc def 584ghi klm 585Warnings: 586Note 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 587# Check our status variables. 588SHOW STATUS LIKE 'Rewriter%'; 589Variable_name Value 590Rewriter_number_loaded_rules 5 591Rewriter_number_reloads 9 592Rewriter_number_rewritten_queries 49 593Rewriter_reload_error OFF 594DROP TABLE t1; 595DROP TABLE t2; 596DELETE FROM query_rewrite.rewrite_rules; 597# Test of literals matching. 598INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 599VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 600( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 601( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 602SELECT * FROM query_rewrite.rewrite_rules; 603id pattern pattern_database replacement enabled normalized_pattern 60425 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL 60526 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL 60627 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL 607CALL query_rewrite.flush_rewrite_rules(); 608SELECT * FROM query_rewrite.rewrite_rules; 609id pattern pattern_database replacement enabled normalized_pattern 61025 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES select ?,? 61126 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES select ?,? 61227 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES select ? 613SELECT 1, 1; 614rewritten w/rule 1 615rewritten w/rule 1 616Warnings: 617Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 618SELECT 1, 2; 619rewritten w/rule 1 620rewritten w/rule 1 621Warnings: 622Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 623SELECT 2, 1; 624rewritten w/rule 2 625rewritten w/rule 2 626Warnings: 627Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 628SELECT 2, 2; 629rewritten w/rule 2 630rewritten w/rule 2 631Warnings: 632Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 633SELECT 3, 1; 6343 1 6353 1 636SELECT 3, 2; 6373 2 6383 2 639SELECT 'The_original_query'; 640The_rewritten_query 641The_rewritten_query 642Warnings: 643Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 644DELETE FROM query_rewrite.rewrite_rules; 645DELETE FROM query_rewrite.rewrite_rules; 646# 647# Testing with no optional columns. 648# 649ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN normalized_pattern; 650CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 651INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 652CREATE TABLE t2 ( a VARCHAR(10) ); 653INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 654# Test of literals matching. 655INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 656VALUES ( 'SELECT ?', 'SELECT "literal"' ); 657SELECT * FROM query_rewrite.rewrite_rules; 658id pattern pattern_database replacement enabled 65928 SELECT ? NULL SELECT "literal" YES 660CALL query_rewrite.flush_rewrite_rules(); 661SELECT * FROM query_rewrite.rewrite_rules; 662id pattern pattern_database replacement enabled 66328 SELECT ? NULL SELECT "literal" YES 664SELECT NULL; 665literal 666literal 667Warnings: 668Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 669SELECT 'abc'; 670literal 671literal 672Warnings: 673Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 674SELECT 1; 675literal 676literal 677Warnings: 678Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 679SELECT 1.1; 680literal 681literal 682Warnings: 683Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 684SELECT 123456789123456789123456789123456789123456789123456789; 685literal 686literal 687Warnings: 688Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 689# Check our status variables. 690SHOW STATUS LIKE 'Rewriter%'; 691Variable_name Value 692Rewriter_number_loaded_rules 1 693Rewriter_number_reloads 11 694Rewriter_number_rewritten_queries 59 695Rewriter_reload_error OFF 696DELETE FROM query_rewrite.rewrite_rules; 697INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 698VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 699'SELECT a FROM test.t1 WHERE a = ?' ), 700( 'SELECT a FROM test.t1', 701'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 702( 'SELECT a FROM test.t1 WHERE b = ?', 703'SELECT * FROM test.t1 WHERE b = ?' ), 704( 'SELECT * FROM test.t2', 705'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 706( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 707'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 708SELECT * FROM query_rewrite.rewrite_rules; 709id pattern pattern_database replacement enabled 71029 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES 71130 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES 71231 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES 71332 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES 71433 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES 715CALL query_rewrite.flush_rewrite_rules(); 716SELECT * FROM query_rewrite.rewrite_rules; 717id pattern pattern_database replacement enabled 71829 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES 71930 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES 72031 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES 72132 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES 72233 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES 723# Check a query that doesn't have a rewrite rule doesn't get rewritten. 724SELECT b FROM test.t1; 725b 726def 727klm 728qrs 729# Check a query with just one parameter. 730SELECT a FROM test.t1 WHERE b = 'qrs'; 731a b 732nop qrs 733Warnings: 734Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 735# Check a rule with parameter truncation (fewer parameters in the 736# output query than in the input query); 737SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 738a 739abc 740Warnings: 741Note 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 742SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 743a b 744ghi klm 745# Check a non parameterized query. 746SELECT * from t1 WHERE a = 'abc'; 747a b 748abc def 749# Check that a non-rewritten query does not yield a warning. 750SELECT b FROM test.t1; 751b 752def 753klm 754qrs 755# Check that a query is not rewritten if the query corresponds to a 756# replacement. 757SELECT a FROM test.t1; 758a b 759abc def 760Warnings: 761Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 762# Check that we can execute a rewrite more than once. 763SELECT * FROM test.t2; 764a b a 765abc def abc 766Warnings: 767Note 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 768SELECT * FROM test.t2; 769a b a 770abc def abc 771Warnings: 772Note 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 773# Remove the warnings. 774SELECT b FROM test.t1; 775b 776def 777klm 778qrs 779# Check parameter switching in a query rewrite 780SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 781a b 782abc def 783ghi klm 784Warnings: 785Note 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 786# Verify with whitespace. 787SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 788a b 789abc def 790ghi klm 791Warnings: 792Note 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 793# Verify with comments. 794SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 795a b 796abc def 797ghi klm 798Warnings: 799Note 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 800# Check our status variables. 801SHOW STATUS LIKE 'Rewriter%'; 802Variable_name Value 803Rewriter_number_loaded_rules 5 804Rewriter_number_reloads 12 805Rewriter_number_rewritten_queries 67 806Rewriter_reload_error OFF 807DROP TABLE t1; 808DROP TABLE t2; 809DELETE FROM query_rewrite.rewrite_rules; 810# Test of literals matching. 811INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 812VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 813( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 814( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 815SELECT * FROM query_rewrite.rewrite_rules; 816id pattern pattern_database replacement enabled 81734 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES 81835 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES 81936 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES 820CALL query_rewrite.flush_rewrite_rules(); 821SELECT * FROM query_rewrite.rewrite_rules; 822id pattern pattern_database replacement enabled 82334 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES 82435 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES 82536 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES 826SELECT 1, 1; 827rewritten w/rule 1 828rewritten w/rule 1 829Warnings: 830Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 831SELECT 1, 2; 832rewritten w/rule 1 833rewritten w/rule 1 834Warnings: 835Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 836SELECT 2, 1; 837rewritten w/rule 2 838rewritten w/rule 2 839Warnings: 840Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 841SELECT 2, 2; 842rewritten w/rule 2 843rewritten w/rule 2 844Warnings: 845Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 846SELECT 3, 1; 8473 1 8483 1 849SELECT 3, 2; 8503 2 8513 2 852SELECT 'The_original_query'; 853The_rewritten_query 854The_rewritten_query 855Warnings: 856Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 857DELETE FROM query_rewrite.rewrite_rules; 858DELETE FROM query_rewrite.rewrite_rules; 859Warnings: 860Warning 1620 Plugin is busy and will be uninstalled on shutdown 861# Query rewrite plugin was queued for uninstalling. 862