Debian Codegraph @

mysql-test/suite/query_rewrite_plugins/t/special.test

raw
1 --source suite/query_rewrite_plugins/include/have_plugin_rewriter.inc
2
3 --echo # This test aims to rewrite queries with parameters in many
4 --echo # different places.
5
6 CREATE TABLE t1 ( c1 INTEGER, c2 CHAR(1), c3 CHAR(1) );
7
8 INSERT INTO t1 VALUES ( 1, 'd', 'a' ),
9 ( 3, 'c', 'b' ),
10 ( 2, 'b', 'c' ),
11 ( 4, 'a', 'a' ),
12 ( 6, 'd', 'b' ),
13 ( 5, 'f', 'c' ),
14 ( 8, 'd', 'a' ),
15 ( 7, 'e', 'b' );
16
17 --source suite/query_rewrite_plugins/include/install_rewriter.inc
18
19 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
20 VALUES ( 'SELECT c1, c2 FROM test.t1 ORDER BY ?',
21 'SELECT c1, c2 FROM test.t1 ORDER BY 2, ? DESC' );
22
23 CALL query_rewrite.flush_rewrite_rules();
24
25 --echo # Should be rewritten.
26 SELECT c1, c2 FROM test.t1 ORDER BY 1;
27
28 --echo # Should not be rewritten.
29 SELECT c1, c2 FROM test.t1 ORDER BY c1;
30
31 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
32 VALUES ( 'SELECT c1, c2 FROM test.t1 LIMIT ?',
33 'SELECT c1, c2 FROM test.t1 ORDER BY 1 LIMIT 1, ?' );
34
35 CALL query_rewrite.flush_rewrite_rules();
36
37 --echo # Should be rewritten.
38 SELECT c1, c2 FROM test.t1 LIMIT 2;
39
40 --echo # Should be rewritten.
41 SELECT c1, c2 FROM test.t1 LIMIT 4;
42
43 --echo # Should not be rewritten.
44 SELECT c1, c2 FROM test.t1 LIMIT 2, 1;
45
46
47 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
48 VALUES ( 'SELECT c1 + ? AS c FROM test.t1',
49 'SELECT c1 + ? * 2 AS c FROM test.t1 ORDER BY c' );
50
51 CALL query_rewrite.flush_rewrite_rules();
52
53 --echo # Should be rewritten.
54 SELECT c1 + 3 AS c FROM test.t1;
55
56 --echo # Should be rewritten.
57 SELECT c1 + 20 AS c FROM test.t1;
58
59 --echo # Should not be rewritten.
60 SELECT c1 + 3 * 2 AS c FROM test.t1;
61
62 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
63 VALUES ( 'SELECT c1 * ? FROM test.t1', 'SELECT c1 + ? FROM test.t1' );
64
65 CALL query_rewrite.flush_rewrite_rules();
66
67 --echo # Should be rewritten.
68 SELECT c1 * 3 FROM test.t1;
69
70 --echo # Should not be rewritten.
71 SELECT c1 + 3 FROM test.t1;
72
73 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
74 VALUES ( 'SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?',
75 'SELECT SUM( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?' );
76
77 CALL query_rewrite.flush_rewrite_rules();
78
79 --echo # Should be rewritten.
80 --sorted_result
81 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 2, 3;
82
83 --echo # Should be rewritten.
84 --sorted_result
85 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 3, 2;
86
87 --echo # Should not be rewritten.
88 --sorted_result
89 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY c2, c3;
90
91 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
92 VALUES ( 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c > ?',
93 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c < ?'
94 );
95
96 CALL query_rewrite.flush_rewrite_rules();
97
98 --sorted_result
99 SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 2, 3 HAVING c > 2;
100
101 --sorted_result
102 SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 3, 2 HAVING c > 3;
103
104
105 CREATE TABLE t2 ( c1 INTEGER, c2 CHAR ( 1 ), c3 CHAR ( 1 ) );
106
107 INSERT INTO t2 VALUES ( 1, 'd', 'a' ),
108 ( 2, 'b', 'c' ),
109 ( 6, 'd', 'b' ),
110 ( 5, 'f', 'c' );
111
112 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
113 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',
114 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 )' );
115
116 CALL query_rewrite.flush_rewrite_rules();
117
118 SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 ) WHERE c.c1=1;
119
120 SELECT c.c1, c.c2 FROM test.t1 AS d JOIN test.t2 AS c ON ( c.c1=d.c1 ) WHERE c.c1=1;
121
122 --echo # Finish extraction in the from clause.
123 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
124 VALUES ( 'SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=? ) c',
125 'SELECT c1 FROM test.t1 WHERE c1=?' );
126
127 CALL query_rewrite.flush_rewrite_rules();
128
129 SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=3 ) c;
130
131 SET sql_mode = 'PIPES_AS_CONCAT';
132 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
133 VALUES ( 'SELECT c1,c2,c3 FROM test.t1 WHERE c1=? ' ||
134 'UNION ' ||
135 'SELECT c1 FROM test.t1 WHERE c1=? ORDER BY ?',
136 'SELECT c1,c2, c3 FROM test.t1 WHERE c1 IN ( ?,? ) ORDER BY ?' );
137
138 CALL query_rewrite.flush_rewrite_rules();
139
140 --echo # That's right, query does not compile unless rewritten.
141 SELECT c1, c2, c3 FROM test.t1 WHERE c1 = 1
142 UNION
143 SELECT c1 FROM test.t1 WHERE c1 = 2 ORDER BY 2;
144
145 --echo # Test with many parameters.
146 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
147 VALUES ( 'SELECT c1, c2 FROM test.t1 WHERE c1 IN ' ||
148 '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )',
149
150 'SELECT c1, c2 FROM test.t1 WHERE c1 NOT IN ' ||
151 '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' );
152
153 CALL query_rewrite.flush_rewrite_rules();
154
155 SELECT c1, c2
156 FROM test.t1
157 WHERE c1 IN ( 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14 );
158
159 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
160 VALUES ( 'SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=? ) t'
161 ' FROM test.t1, '
162 ' ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=? ) tb ) tc'
163 ' WHERE test.t1.c2=tc.c2 AND tc.c1 < ?',
164 'SELECT test.t1.c1, test.t2.c1, test.t2.c2, test.t1.c2'
165 ' FROM test.t1 JOIN test.t2 ON ( test.t1.c1=t2.c1 AND test.t2.c1 IN ( ?, ? ) )'
166 ' WHERE test.t1.c1 > ?' );
167
168 CALL query_rewrite.flush_rewrite_rules();
169
170 SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=2 ) t
171 FROM test.t1,
172 ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=1 ) tb ) tc
173 WHERE test.t1.c2=tc.c2 AND tc.c1 < 0;
174
175 --echo # Check our status variables:.
176 SHOW STATUS LIKE 'Rewriter%';
177
178 DROP TABLE t1, t2;
179 SET sql_mode = DEFAULT;
180 --source suite/query_rewrite_plugins/include/uninstall_rewriter.inc