Server IP : 104.21.38.3 / Your IP : 108.162.226.215 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/query_rewrite_plugins/r/ |
Upload File : |
# This test aims to rewrite queries with parameters in many # different places. CREATE TABLE t1 ( c1 INTEGER, c2 CHAR(1), c3 CHAR(1) ); INSERT INTO t1 VALUES ( 1, 'd', 'a' ), ( 3, 'c', 'b' ), ( 2, 'b', 'c' ), ( 4, 'a', 'a' ), ( 6, 'd', 'b' ), ( 5, 'f', 'c' ), ( 8, 'd', 'a' ), ( 7, 'e', 'b' ); Warnings: Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. Warnings: Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. # Query rewrite plugin was installed. INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1, c2 FROM test.t1 ORDER BY ?', 'SELECT c1, c2 FROM test.t1 ORDER BY 2, ? DESC' ); CALL query_rewrite.flush_rewrite_rules(); Warnings: Warning 1681 'RESET QUERY CACHE' is deprecated and will be removed in a future release. # Should be rewritten. SELECT c1, c2 FROM test.t1 ORDER BY 1; c1 c2 4 a 2 b 3 c 8 d 6 d 1 d 7 e 5 f Warnings: Note 1105 Query 'SELECT c1, c2 FROM test.t1 ORDER BY 1' rewritten to 'SELECT c1, c2 FROM test.t1 ORDER BY 2, 1 DESC' by a query rewrite plugin # Should not be rewritten. SELECT c1, c2 FROM test.t1 ORDER BY c1; c1 c2 1 d 2 b 3 c 4 a 5 f 6 d 7 e 8 d INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1, c2 FROM test.t1 LIMIT ?', 'SELECT c1, c2 FROM test.t1 ORDER BY 1 LIMIT 1, ?' ); CALL query_rewrite.flush_rewrite_rules(); # Should be rewritten. SELECT c1, c2 FROM test.t1 LIMIT 2; c1 c2 2 b 3 c Warnings: Note 1105 Query 'SELECT c1, c2 FROM test.t1 LIMIT 2' rewritten to 'SELECT c1, c2 FROM test.t1 ORDER BY 1 LIMIT 1, 2' by a query rewrite plugin # Should be rewritten. SELECT c1, c2 FROM test.t1 LIMIT 4; c1 c2 2 b 3 c 4 a 5 f Warnings: Note 1105 Query 'SELECT c1, c2 FROM test.t1 LIMIT 4' rewritten to 'SELECT c1, c2 FROM test.t1 ORDER BY 1 LIMIT 1, 4' by a query rewrite plugin # Should not be rewritten. SELECT c1, c2 FROM test.t1 LIMIT 2, 1; c1 c2 2 b INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1 + ? AS c FROM test.t1', 'SELECT c1 + ? * 2 AS c FROM test.t1 ORDER BY c' ); CALL query_rewrite.flush_rewrite_rules(); # Should be rewritten. SELECT c1 + 3 AS c FROM test.t1; c 7 8 9 10 11 12 13 14 Warnings: Note 1105 Query 'SELECT c1 + 3 AS c FROM test.t1' rewritten to 'SELECT c1 + 3 * 2 AS c FROM test.t1 ORDER BY c' by a query rewrite plugin # Should be rewritten. SELECT c1 + 20 AS c FROM test.t1; c 41 42 43 44 45 46 47 48 Warnings: Note 1105 Query 'SELECT c1 + 20 AS c FROM test.t1' rewritten to 'SELECT c1 + 20 * 2 AS c FROM test.t1 ORDER BY c' by a query rewrite plugin # Should not be rewritten. SELECT c1 + 3 * 2 AS c FROM test.t1; c 7 9 8 10 12 11 14 13 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1 * ? FROM test.t1', 'SELECT c1 + ? FROM test.t1' ); CALL query_rewrite.flush_rewrite_rules(); # Should be rewritten. SELECT c1 * 3 FROM test.t1; c1 + 3 4 6 5 7 9 8 11 10 Warnings: Note 1105 Query 'SELECT c1 * 3 FROM test.t1' rewritten to 'SELECT c1 + 3 FROM test.t1' by a query rewrite plugin # Should not be rewritten. SELECT c1 + 3 FROM test.t1; c1 + 3 4 6 5 7 9 8 11 10 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?', 'SELECT SUM( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?' ); CALL query_rewrite.flush_rewrite_rules(); # Should be rewritten. SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 2, 3; SUM( c1 ) c2 c3 4 a a 2 b c 3 c b 9 d a 6 d b 7 e b 5 f c Warnings: Note 1105 Query 'SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 2, 3' rewritten to 'SELECT SUM( c1 ), c2, c3 FROM test.t1 GROUP BY 2, 3' by a query rewrite plugin # Should be rewritten. SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 3, 2; SUM( c1 ) c2 c3 4 a a 9 d a 3 c b 6 d b 7 e b 2 b c 5 f c Warnings: Note 1105 Query 'SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 3, 2' rewritten to 'SELECT SUM( c1 ), c2, c3 FROM test.t1 GROUP BY 3, 2' by a query rewrite plugin # Should not be rewritten. SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY c2, c3; COUNT( c1 ) c2 c3 1 a a 1 b c 1 c b 2 d a 1 d b 1 e b 1 f c INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c > ?', 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c < ?' ); CALL query_rewrite.flush_rewrite_rules(); SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 2, 3 HAVING c > 2; c c2 c3 1 a a 1 b c 1 c b 1 d b 1 e b 1 f c Warnings: Note 1105 Query 'SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 2, 3 HAVING c > 2' rewritten to 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY 2, 3 HAVING c < 2' by a query rewrite plugin SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 3, 2 HAVING c > 3; c c2 c3 1 a a 2 d a 1 c b 1 d b 1 e b 1 b c 1 f c Warnings: Note 1105 Query 'SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 3, 2 HAVING c > 3' rewritten to 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY 3, 2 HAVING c < 3' by a query rewrite plugin CREATE TABLE t2 ( c1 INTEGER, c2 CHAR ( 1 ), c3 CHAR ( 1 ) ); INSERT INTO t2 VALUES ( 1, 'd', 'a' ), ( 2, 'b', 'c' ), ( 6, 'd', 'b' ), ( 5, 'f', 'c' ); INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON(c.c1=d.c1)WHERE c.c1=1', 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 )' ); CALL query_rewrite.flush_rewrite_rules(); SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 ) WHERE c.c1=1; c1 c2 1 d 2 b 6 d 5 f Warnings: Note 1105 Query 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 ) WHERE c.c1=1' rewritten to 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 )' by a query rewrite plugin SELECT c.c1, c.c2 FROM test.t1 AS d JOIN test.t2 AS c ON ( c.c1=d.c1 ) WHERE c.c1=1; c1 c2 1 d # Finish extraction in the from clause. INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=? ) c', 'SELECT c1 FROM test.t1 WHERE c1=?' ); CALL query_rewrite.flush_rewrite_rules(); SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=3 ) c; c1 3 Warnings: Note 1105 Query 'SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=3 ) c' rewritten to 'SELECT c1 FROM test.t1 WHERE c1=3' by a query rewrite plugin SET sql_mode = 'PIPES_AS_CONCAT'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1,c2,c3 FROM test.t1 WHERE c1=? ' || 'UNION ' || 'SELECT c1 FROM test.t1 WHERE c1=? ORDER BY ?', 'SELECT c1,c2, c3 FROM test.t1 WHERE c1 IN ( ?,? ) ORDER BY ?' ); CALL query_rewrite.flush_rewrite_rules(); # That's right, query does not compile unless rewritten. SELECT c1, c2, c3 FROM test.t1 WHERE c1 = 1 UNION SELECT c1 FROM test.t1 WHERE c1 = 2 ORDER BY 2; c1 c2 c3 2 b c 1 d a Warnings: Note 1105 Query 'SELECT c1, c2, c3 FROM test.t1 WHERE c1 = 1 UNION SELECT c1 FROM test.t1 WHERE c1 = 2 ORDER BY 2' rewritten to 'SELECT c1,c2, c3 FROM test.t1 WHERE c1 IN ( 1,2 ) ORDER BY 2' by a query rewrite plugin # Test with many parameters. INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT c1, c2 FROM test.t1 WHERE c1 IN ' || '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )', 'SELECT c1, c2 FROM test.t1 WHERE c1 NOT IN ' || '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' ); CALL query_rewrite.flush_rewrite_rules(); SELECT c1, c2 FROM test.t1 WHERE c1 IN ( 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14 ); c1 c2 6 d Warnings: Note 1105 Query 'SELECT c1, c2 FROM test.t1 WHERE c1 IN ( 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14 )' rewritten to 'SELECT c1, c2 FROM test.t1 WHERE c1 NOT IN ( 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14 )' by a query rewrite plugin INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ( 'SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=? ) t' ' FROM test.t1, ' ' ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=? ) tb ) tc' ' WHERE test.t1.c2=tc.c2 AND tc.c1 < ?', 'SELECT test.t1.c1, test.t2.c1, test.t2.c2, test.t1.c2' ' FROM test.t1 JOIN test.t2 ON ( test.t1.c1=t2.c1 AND test.t2.c1 IN ( ?, ? ) )' ' WHERE test.t1.c1 > ?' ); CALL query_rewrite.flush_rewrite_rules(); SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=2 ) t FROM test.t1, ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=1 ) tb ) tc WHERE test.t1.c2=tc.c2 AND tc.c1 < 0; c1 c1 c2 c2 1 1 d d 2 2 b b Warnings: Note 1105 Query 'SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=2 ) t FROM test.t1, ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=1 ) tb ) tc WHERE test.t1.c2=tc.c2 AND tc.c1 < 0' rewritten to 'SELECT test.t1.c1, test.t2.c1, test.t2.c2, test.t1.c2 FROM test.t1 JOIN test.t2 ON ( test.t1.c1=t2.c1 AND test.t2.c1 IN ( 2, 1 ) ) WHERE test.t1.c1 > 0' by a query rewrite plugin # Check our status variables:. SHOW STATUS LIKE 'Rewriter%'; Variable_name Value Rewriter_number_loaded_rules 11 Rewriter_number_reloads 12 Rewriter_number_rewritten_queries 15 Rewriter_reload_error OFF DROP TABLE t1, t2; SET sql_mode = DEFAULT; Warnings: Warning 1620 Plugin is busy and will be uninstalled on shutdown # Query rewrite plugin was queued for uninstalling.