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# 7Warnings: 8Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. 9Warnings: 10Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. 11# Query rewrite plugin was installed. 12# 13# Testing with PATTERN_DIGEST and NORMALIZED_PATTERN columns. 14# 15CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 16INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 17CREATE TABLE t2 ( a VARCHAR(10) ); 18INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 19# Test of literals matching. 20INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 21VALUES ( 'SELECT ?', 'SELECT "literal"' ); 22SELECT * FROM query_rewrite.rewrite_rules; 23id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 241 SELECT ? NULL SELECT "literal" YES NULL NULL NULL 25CALL query_rewrite.flush_rewrite_rules(); 26Warnings: 27Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. 28SELECT * FROM query_rewrite.rewrite_rules; 29id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 301 SELECT ? NULL SELECT "literal" YES NULL 3fff4c5a5ca5e1e484663cab257efd1e select ? 31SELECT NULL; 32literal 33literal 34Warnings: 35Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 36SELECT 'abc'; 37literal 38literal 39Warnings: 40Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 41SELECT 1; 42literal 43literal 44Warnings: 45Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 46SELECT 1.1; 47literal 48literal 49Warnings: 50Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 51SELECT 123456789123456789123456789123456789123456789123456789; 52literal 53literal 54Warnings: 55Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 56# Check our status variables. 57SHOW STATUS LIKE 'Rewriter%'; 58Variable_name Value 59Rewriter_number_loaded_rules 1 60Rewriter_number_reloads 2 61Rewriter_number_rewritten_queries 5 62Rewriter_reload_error OFF 63DELETE FROM query_rewrite.rewrite_rules; 64INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 65VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 66'SELECT a FROM test.t1 WHERE a = ?' ), 67( 'SELECT a FROM test.t1', 68'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 69( 'SELECT a FROM test.t1 WHERE b = ?', 70'SELECT * FROM test.t1 WHERE b = ?' ), 71( 'SELECT * FROM test.t2', 72'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 73( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 74'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 75SELECT * FROM query_rewrite.rewrite_rules; 76id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 772 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL NULL 783 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL NULL 794 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL NULL 805 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL NULL 816 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL NULL 82CALL query_rewrite.flush_rewrite_rules(); 83SELECT * FROM query_rewrite.rewrite_rules; 84id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 852 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL b893182789d620f41dbbf72aac0b7ebe select `*` from `test`.`t1` where (((`a` = ?) and ?) or (`b` = ?)) 863 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL 320e6e9686ed89a59f8415e110691107 select `a` from `test`.`t1` 874 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL 4f66faff1f968eef78e5540f3c6cf914 select `a` from `test`.`t1` where (`b` = ?) 885 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL 34d498a84c9928b740bbed830bc02923 select `*` from `test`.`t2` 896 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL b42ba82c98f4675675f3d104d344b6f2 select `*` from `test`.`t1` where ((`a` = ?) or (`b` = ?)) 90# Check a query that doesn't have a rewrite rule doesn't get rewritten. 91SELECT b FROM test.t1; 92b 93def 94klm 95qrs 96# Check a query with just one parameter. 97SELECT a FROM test.t1 WHERE b = 'qrs'; 98a b 99nop qrs 100Warnings: 101Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 102# Check a rule with parameter truncation (fewer parameters in the 103# output query than in the input query); 104SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 105a 106abc 107Warnings: 108Note 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 109SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 110a b 111ghi klm 112# Check a non parameterized query. 113SELECT * from t1 WHERE a = 'abc'; 114a b 115abc def 116# Check that a non-rewritten query does not yield a warning. 117SELECT b FROM test.t1; 118b 119def 120klm 121qrs 122# Check that a query is not rewritten if the query corresponds to a 123# replacement. 124SELECT a FROM test.t1; 125a b 126abc def 127Warnings: 128Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 129# Check that we can execute a rewrite more than once. 130SELECT * FROM test.t2; 131a b a 132abc def abc 133Warnings: 134Note 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 135SELECT * FROM test.t2; 136a b a 137abc def abc 138Warnings: 139Note 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 140# Remove the warnings. 141SELECT b FROM test.t1; 142b 143def 144klm 145qrs 146# Check parameter switching in a query rewrite 147SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 148a b 149abc def 150ghi klm 151Warnings: 152Note 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 153# Verify with whitespace. 154SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 155a b 156abc def 157ghi klm 158Warnings: 159Note 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 160# Verify with comments. 161SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 162a b 163abc def 164ghi klm 165Warnings: 166Note 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 167# Check our status variables. 168SHOW STATUS LIKE 'Rewriter%'; 169Variable_name Value 170Rewriter_number_loaded_rules 5 171Rewriter_number_reloads 3 172Rewriter_number_rewritten_queries 13 173Rewriter_reload_error OFF 174DROP TABLE t1; 175DROP TABLE t2; 176DELETE FROM query_rewrite.rewrite_rules; 177# Test of literals matching. 178INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 179VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 180( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 181( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 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 NULL NULL 1858 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL NULL 1869 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL NULL 187CALL query_rewrite.flush_rewrite_rules(); 188SELECT * FROM query_rewrite.rewrite_rules; 189id pattern pattern_database replacement enabled message pattern_digest normalized_pattern 1907 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL cea49e11d5a38cbb4d487197d644cd00 select ?,? 1918 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL cea49e11d5a38cbb4d487197d644cd00 select ?,? 1929 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL 3fff4c5a5ca5e1e484663cab257efd1e select ? 193SELECT 1, 1; 194rewritten w/rule 1 195rewritten w/rule 1 196Warnings: 197Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 198SELECT 1, 2; 199rewritten w/rule 1 200rewritten w/rule 1 201Warnings: 202Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 203SELECT 2, 1; 204rewritten w/rule 2 205rewritten w/rule 2 206Warnings: 207Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 208SELECT 2, 2; 209rewritten w/rule 2 210rewritten w/rule 2 211Warnings: 212Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 213SELECT 3, 1; 2143 1 2153 1 216SELECT 3, 2; 2173 2 2183 2 219SELECT 'The_original_query'; 220The_rewritten_query 221The_rewritten_query 222Warnings: 223Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 224DELETE FROM query_rewrite.rewrite_rules; 225DELETE FROM query_rewrite.rewrite_rules; 226# 227# Testing with a NORMALIZED_PATTERN column. 228# 229ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN pattern_digest; 230CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 231INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 232CREATE TABLE t2 ( a VARCHAR(10) ); 233INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 234# Test of literals matching. 235INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 236VALUES ( 'SELECT ?', 'SELECT "literal"' ); 237SELECT * FROM query_rewrite.rewrite_rules; 238id pattern pattern_database replacement enabled message normalized_pattern 23910 SELECT ? NULL SELECT "literal" YES NULL NULL 240CALL query_rewrite.flush_rewrite_rules(); 241SELECT * FROM query_rewrite.rewrite_rules; 242id pattern pattern_database replacement enabled message normalized_pattern 24310 SELECT ? NULL SELECT "literal" YES NULL select ? 244SELECT NULL; 245literal 246literal 247Warnings: 248Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 249SELECT 'abc'; 250literal 251literal 252Warnings: 253Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 254SELECT 1; 255literal 256literal 257Warnings: 258Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 259SELECT 1.1; 260literal 261literal 262Warnings: 263Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 264SELECT 123456789123456789123456789123456789123456789123456789; 265literal 266literal 267Warnings: 268Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 269# Check our status variables. 270SHOW STATUS LIKE 'Rewriter%'; 271Variable_name Value 272Rewriter_number_loaded_rules 1 273Rewriter_number_reloads 5 274Rewriter_number_rewritten_queries 23 275Rewriter_reload_error OFF 276DELETE FROM query_rewrite.rewrite_rules; 277INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 278VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 279'SELECT a FROM test.t1 WHERE a = ?' ), 280( 'SELECT a FROM test.t1', 281'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 282( 'SELECT a FROM test.t1 WHERE b = ?', 283'SELECT * FROM test.t1 WHERE b = ?' ), 284( 'SELECT * FROM test.t2', 285'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 286( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 287'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 288SELECT * FROM query_rewrite.rewrite_rules; 289id pattern pattern_database replacement enabled message normalized_pattern 29011 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL NULL 29112 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL NULL 29213 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL NULL 29314 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL NULL 29415 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL NULL 295CALL query_rewrite.flush_rewrite_rules(); 296SELECT * FROM query_rewrite.rewrite_rules; 297id pattern pattern_database replacement enabled message normalized_pattern 29811 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 ?) or (`b` = ?)) 29912 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL select `a` from `test`.`t1` 30013 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL select `a` from `test`.`t1` where (`b` = ?) 30114 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL select `*` from `test`.`t2` 30215 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` = ?)) 303# Check a query that doesn't have a rewrite rule doesn't get rewritten. 304SELECT b FROM test.t1; 305b 306def 307klm 308qrs 309# Check a query with just one parameter. 310SELECT a FROM test.t1 WHERE b = 'qrs'; 311a b 312nop qrs 313Warnings: 314Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 315# Check a rule with parameter truncation (fewer parameters in the 316# output query than in the input query); 317SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 318a 319abc 320Warnings: 321Note 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 322SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 323a b 324ghi klm 325# Check a non parameterized query. 326SELECT * from t1 WHERE a = 'abc'; 327a b 328abc def 329# Check that a non-rewritten query does not yield a warning. 330SELECT b FROM test.t1; 331b 332def 333klm 334qrs 335# Check that a query is not rewritten if the query corresponds to a 336# replacement. 337SELECT a FROM test.t1; 338a b 339abc def 340Warnings: 341Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 342# Check that we can execute a rewrite more than once. 343SELECT * FROM test.t2; 344a b a 345abc def abc 346Warnings: 347Note 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 348SELECT * FROM test.t2; 349a b a 350abc def abc 351Warnings: 352Note 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 353# Remove the warnings. 354SELECT b FROM test.t1; 355b 356def 357klm 358qrs 359# Check parameter switching in a query rewrite 360SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 361a b 362abc def 363ghi klm 364Warnings: 365Note 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 366# Verify with whitespace. 367SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 368a b 369abc def 370ghi klm 371Warnings: 372Note 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 373# Verify with comments. 374SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 375a b 376abc def 377ghi klm 378Warnings: 379Note 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 380# Check our status variables. 381SHOW STATUS LIKE 'Rewriter%'; 382Variable_name Value 383Rewriter_number_loaded_rules 5 384Rewriter_number_reloads 6 385Rewriter_number_rewritten_queries 31 386Rewriter_reload_error OFF 387DROP TABLE t1; 388DROP TABLE t2; 389DELETE FROM query_rewrite.rewrite_rules; 390# Test of literals matching. 391INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 392VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 393( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 394( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 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 NULL 39817 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL NULL 39918 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL NULL 400CALL query_rewrite.flush_rewrite_rules(); 401SELECT * FROM query_rewrite.rewrite_rules; 402id pattern pattern_database replacement enabled message normalized_pattern 40316 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL select ?,? 40417 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL select ?,? 40518 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL select ? 406SELECT 1, 1; 407rewritten w/rule 1 408rewritten w/rule 1 409Warnings: 410Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 411SELECT 1, 2; 412rewritten w/rule 1 413rewritten w/rule 1 414Warnings: 415Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 416SELECT 2, 1; 417rewritten w/rule 2 418rewritten w/rule 2 419Warnings: 420Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 421SELECT 2, 2; 422rewritten w/rule 2 423rewritten w/rule 2 424Warnings: 425Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 426SELECT 3, 1; 4273 1 4283 1 429SELECT 3, 2; 4303 2 4313 2 432SELECT 'The_original_query'; 433The_rewritten_query 434The_rewritten_query 435Warnings: 436Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 437DELETE FROM query_rewrite.rewrite_rules; 438DELETE FROM query_rewrite.rewrite_rules; 439# 440# Testing with a NORMALIZED_PATTERN column but no MESSAGE column. 441# 442ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN message; 443CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 444INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 445CREATE TABLE t2 ( a VARCHAR(10) ); 446INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 447# Test of literals matching. 448INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 449VALUES ( 'SELECT ?', 'SELECT "literal"' ); 450SELECT * FROM query_rewrite.rewrite_rules; 451id pattern pattern_database replacement enabled normalized_pattern 45219 SELECT ? NULL SELECT "literal" YES NULL 453CALL query_rewrite.flush_rewrite_rules(); 454SELECT * FROM query_rewrite.rewrite_rules; 455id pattern pattern_database replacement enabled normalized_pattern 45619 SELECT ? NULL SELECT "literal" YES select ? 457SELECT NULL; 458literal 459literal 460Warnings: 461Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 462SELECT 'abc'; 463literal 464literal 465Warnings: 466Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 467SELECT 1; 468literal 469literal 470Warnings: 471Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 472SELECT 1.1; 473literal 474literal 475Warnings: 476Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 477SELECT 123456789123456789123456789123456789123456789123456789; 478literal 479literal 480Warnings: 481Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 482# Check our status variables. 483SHOW STATUS LIKE 'Rewriter%'; 484Variable_name Value 485Rewriter_number_loaded_rules 1 486Rewriter_number_reloads 8 487Rewriter_number_rewritten_queries 41 488Rewriter_reload_error OFF 489DELETE FROM query_rewrite.rewrite_rules; 490INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 491VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 492'SELECT a FROM test.t1 WHERE a = ?' ), 493( 'SELECT a FROM test.t1', 494'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 495( 'SELECT a FROM test.t1 WHERE b = ?', 496'SELECT * FROM test.t1 WHERE b = ?' ), 497( 'SELECT * FROM test.t2', 498'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 499( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 500'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 501SELECT * FROM query_rewrite.rewrite_rules; 502id pattern pattern_database replacement enabled normalized_pattern 50320 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL 50421 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL 50522 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL 50623 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL 50724 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL 508CALL query_rewrite.flush_rewrite_rules(); 509SELECT * FROM query_rewrite.rewrite_rules; 510id pattern pattern_database replacement enabled normalized_pattern 51120 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 ?) or (`b` = ?)) 51221 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES select `a` from `test`.`t1` 51322 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES select `a` from `test`.`t1` where (`b` = ?) 51423 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES select `*` from `test`.`t2` 51524 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` = ?)) 516# Check a query that doesn't have a rewrite rule doesn't get rewritten. 517SELECT b FROM test.t1; 518b 519def 520klm 521qrs 522# Check a query with just one parameter. 523SELECT a FROM test.t1 WHERE b = 'qrs'; 524a b 525nop qrs 526Warnings: 527Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 528# Check a rule with parameter truncation (fewer parameters in the 529# output query than in the input query); 530SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 531a 532abc 533Warnings: 534Note 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 535SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 536a b 537ghi klm 538# Check a non parameterized query. 539SELECT * from t1 WHERE a = 'abc'; 540a b 541abc def 542# Check that a non-rewritten query does not yield a warning. 543SELECT b FROM test.t1; 544b 545def 546klm 547qrs 548# Check that a query is not rewritten if the query corresponds to a 549# replacement. 550SELECT a FROM test.t1; 551a b 552abc def 553Warnings: 554Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 555# Check that we can execute a rewrite more than once. 556SELECT * FROM test.t2; 557a b a 558abc def abc 559Warnings: 560Note 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 561SELECT * FROM test.t2; 562a b a 563abc def abc 564Warnings: 565Note 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 566# Remove the warnings. 567SELECT b FROM test.t1; 568b 569def 570klm 571qrs 572# Check parameter switching in a query rewrite 573SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 574a b 575abc def 576ghi klm 577Warnings: 578Note 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 579# Verify with whitespace. 580SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 581a b 582abc def 583ghi klm 584Warnings: 585Note 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 586# Verify with comments. 587SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 588a b 589abc def 590ghi klm 591Warnings: 592Note 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 593# Check our status variables. 594SHOW STATUS LIKE 'Rewriter%'; 595Variable_name Value 596Rewriter_number_loaded_rules 5 597Rewriter_number_reloads 9 598Rewriter_number_rewritten_queries 49 599Rewriter_reload_error OFF 600DROP TABLE t1; 601DROP TABLE t2; 602DELETE FROM query_rewrite.rewrite_rules; 603# Test of literals matching. 604INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 605VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 606( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 607( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 608SELECT * FROM query_rewrite.rewrite_rules; 609id pattern pattern_database replacement enabled normalized_pattern 61025 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL 61126 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL 61227 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL 613CALL query_rewrite.flush_rewrite_rules(); 614SELECT * FROM query_rewrite.rewrite_rules; 615id pattern pattern_database replacement enabled normalized_pattern 61625 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES select ?,? 61726 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES select ?,? 61827 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES select ? 619SELECT 1, 1; 620rewritten w/rule 1 621rewritten w/rule 1 622Warnings: 623Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 624SELECT 1, 2; 625rewritten w/rule 1 626rewritten w/rule 1 627Warnings: 628Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 629SELECT 2, 1; 630rewritten w/rule 2 631rewritten w/rule 2 632Warnings: 633Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 634SELECT 2, 2; 635rewritten w/rule 2 636rewritten w/rule 2 637Warnings: 638Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 639SELECT 3, 1; 6403 1 6413 1 642SELECT 3, 2; 6433 2 6443 2 645SELECT 'The_original_query'; 646The_rewritten_query 647The_rewritten_query 648Warnings: 649Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 650DELETE FROM query_rewrite.rewrite_rules; 651DELETE FROM query_rewrite.rewrite_rules; 652# 653# Testing with no optional columns. 654# 655ALTER TABLE query_rewrite.rewrite_rules DROP COLUMN normalized_pattern; 656CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) ); 657INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' ); 658CREATE TABLE t2 ( a VARCHAR(10) ); 659INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' ); 660# Test of literals matching. 661INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 662VALUES ( 'SELECT ?', 'SELECT "literal"' ); 663SELECT * FROM query_rewrite.rewrite_rules; 664id pattern pattern_database replacement enabled 66528 SELECT ? NULL SELECT "literal" YES 666CALL query_rewrite.flush_rewrite_rules(); 667SELECT * FROM query_rewrite.rewrite_rules; 668id pattern pattern_database replacement enabled 66928 SELECT ? NULL SELECT "literal" YES 670SELECT NULL; 671literal 672literal 673Warnings: 674Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin 675SELECT 'abc'; 676literal 677literal 678Warnings: 679Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin 680SELECT 1; 681literal 682literal 683Warnings: 684Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin 685SELECT 1.1; 686literal 687literal 688Warnings: 689Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin 690SELECT 123456789123456789123456789123456789123456789123456789; 691literal 692literal 693Warnings: 694Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin 695# Check our status variables. 696SHOW STATUS LIKE 'Rewriter%'; 697Variable_name Value 698Rewriter_number_loaded_rules 1 699Rewriter_number_reloads 11 700Rewriter_number_rewritten_queries 59 701Rewriter_reload_error OFF 702DELETE FROM query_rewrite.rewrite_rules; 703INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 704VALUES ( 'SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ?', 705'SELECT a FROM test.t1 WHERE a = ?' ), 706( 'SELECT a FROM test.t1', 707'SELECT * FROM test.t1 WHERE a = \'abc\'' ), 708( 'SELECT a FROM test.t1 WHERE b = ?', 709'SELECT * FROM test.t1 WHERE b = ?' ), 710( 'SELECT * FROM test.t2', 711'SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a' ), 712( 'SELECT * FROM test.t1 WHERE a = ? OR b = ?', 713'SELECT * FROM test.t1 WHERE b = ? OR a = ?' ); 714SELECT * FROM query_rewrite.rewrite_rules; 715id pattern pattern_database replacement enabled 71629 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES 71730 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES 71831 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES 71932 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES 72033 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES 721CALL query_rewrite.flush_rewrite_rules(); 722SELECT * FROM query_rewrite.rewrite_rules; 723id pattern pattern_database replacement enabled 72429 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES 72530 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES 72631 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES 72732 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES 72833 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES 729# Check a query that doesn't have a rewrite rule doesn't get rewritten. 730SELECT b FROM test.t1; 731b 732def 733klm 734qrs 735# Check a query with just one parameter. 736SELECT a FROM test.t1 WHERE b = 'qrs'; 737a b 738nop qrs 739Warnings: 740Note 1105 Query 'SELECT a FROM test.t1 WHERE b = 'qrs'' rewritten to 'SELECT * FROM test.t1 WHERE b = 'qrs'' by a query rewrite plugin 741# Check a rule with parameter truncation (fewer parameters in the 742# output query than in the input query); 743SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm'; 744a 745abc 746Warnings: 747Note 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 748SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm'; 749a b 750ghi klm 751# Check a non parameterized query. 752SELECT * from t1 WHERE a = 'abc'; 753a b 754abc def 755# Check that a non-rewritten query does not yield a warning. 756SELECT b FROM test.t1; 757b 758def 759klm 760qrs 761# Check that a query is not rewritten if the query corresponds to a 762# replacement. 763SELECT a FROM test.t1; 764a b 765abc def 766Warnings: 767Note 1105 Query 'SELECT a FROM test.t1' rewritten to 'SELECT * FROM test.t1 WHERE a = 'abc'' by a query rewrite plugin 768# Check that we can execute a rewrite more than once. 769SELECT * FROM test.t2; 770a b a 771abc def abc 772Warnings: 773Note 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 774SELECT * FROM test.t2; 775a b a 776abc def abc 777Warnings: 778Note 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 779# Remove the warnings. 780SELECT b FROM test.t1; 781b 782def 783klm 784qrs 785# Check parameter switching in a query rewrite 786SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 787a b 788abc def 789ghi klm 790Warnings: 791Note 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 792# Verify with whitespace. 793SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi'; 794a b 795abc def 796ghi klm 797Warnings: 798Note 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 799# Verify with comments. 800SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi'; 801a b 802abc def 803ghi klm 804Warnings: 805Note 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 806# Check our status variables. 807SHOW STATUS LIKE 'Rewriter%'; 808Variable_name Value 809Rewriter_number_loaded_rules 5 810Rewriter_number_reloads 12 811Rewriter_number_rewritten_queries 67 812Rewriter_reload_error OFF 813DROP TABLE t1; 814DROP TABLE t2; 815DELETE FROM query_rewrite.rewrite_rules; 816# Test of literals matching. 817INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) 818VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ), 819( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ), 820( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"'); 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 826CALL query_rewrite.flush_rewrite_rules(); 827SELECT * FROM query_rewrite.rewrite_rules; 828id pattern pattern_database replacement enabled 82934 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES 83035 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES 83136 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES 832SELECT 1, 1; 833rewritten w/rule 1 834rewritten w/rule 1 835Warnings: 836Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 837SELECT 1, 2; 838rewritten w/rule 1 839rewritten w/rule 1 840Warnings: 841Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin 842SELECT 2, 1; 843rewritten w/rule 2 844rewritten w/rule 2 845Warnings: 846Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 847SELECT 2, 2; 848rewritten w/rule 2 849rewritten w/rule 2 850Warnings: 851Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin 852SELECT 3, 1; 8533 1 8543 1 855SELECT 3, 2; 8563 2 8573 2 858SELECT 'The_original_query'; 859The_rewritten_query 860The_rewritten_query 861Warnings: 862Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin 863DELETE FROM query_rewrite.rewrite_rules; 864DELETE FROM query_rewrite.rewrite_rules; 865Warnings: 866Warning 1620 Plugin is busy and will be uninstalled on shutdown 867# Query rewrite plugin was queued for uninstalling. 868