Debian Codegraph @

mysql-test/suite/query_rewrite_plugins/r/special.result

raw
1 # This test aims to rewrite queries with parameters in many
2 # different places.
3 CREATE TABLE t1 ( c1 INTEGER, c2 CHAR(1), c3 CHAR(1) );
4 INSERT INTO t1 VALUES ( 1, 'd', 'a' ),
5 ( 3, 'c', 'b' ),
6 ( 2, 'b', 'c' ),
7 ( 4, 'a', 'a' ),
8 ( 6, 'd', 'b' ),
9 ( 5, 'f', 'c' ),
10 ( 8, 'd', 'a' ),
11 ( 7, 'e', 'b' );
12 # Query rewrite plugin was installed.
13 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
14 VALUES ( 'SELECT c1, c2 FROM test.t1 ORDER BY ?',
15 'SELECT c1, c2 FROM test.t1 ORDER BY 2, ? DESC' );
16 CALL query_rewrite.flush_rewrite_rules();
17 # Should be rewritten.
18 SELECT c1, c2 FROM test.t1 ORDER BY 1;
19 c1 c2
20 4 a
21 2 b
22 3 c
23 8 d
24 6 d
25 1 d
26 7 e
27 5 f
28 Warnings:
29 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
30 # Should not be rewritten.
31 SELECT c1, c2 FROM test.t1 ORDER BY c1;
32 c1 c2
33 1 d
34 2 b
35 3 c
36 4 a
37 5 f
38 6 d
39 7 e
40 8 d
41 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
42 VALUES ( 'SELECT c1, c2 FROM test.t1 LIMIT ?',
43 'SELECT c1, c2 FROM test.t1 ORDER BY 1 LIMIT 1, ?' );
44 CALL query_rewrite.flush_rewrite_rules();
45 # Should be rewritten.
46 SELECT c1, c2 FROM test.t1 LIMIT 2;
47 c1 c2
48 2 b
49 3 c
50 Warnings:
51 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
52 # Should be rewritten.
53 SELECT c1, c2 FROM test.t1 LIMIT 4;
54 c1 c2
55 2 b
56 3 c
57 4 a
58 5 f
59 Warnings:
60 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
61 # Should not be rewritten.
62 SELECT c1, c2 FROM test.t1 LIMIT 2, 1;
63 c1 c2
64 2 b
65 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
66 VALUES ( 'SELECT c1 + ? AS c FROM test.t1',
67 'SELECT c1 + ? * 2 AS c FROM test.t1 ORDER BY c' );
68 CALL query_rewrite.flush_rewrite_rules();
69 # Should be rewritten.
70 SELECT c1 + 3 AS c FROM test.t1;
71 c
72 7
73 8
74 9
75 10
76 11
77 12
78 13
79 14
80 Warnings:
81 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
82 # Should be rewritten.
83 SELECT c1 + 20 AS c FROM test.t1;
84 c
85 41
86 42
87 43
88 44
89 45
90 46
91 47
92 48
93 Warnings:
94 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
95 # Should not be rewritten.
96 SELECT c1 + 3 * 2 AS c FROM test.t1;
97 c
98 7
99 9
100 8
101 10
102 12
103 11
104 14
105 13
106 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
107 VALUES ( 'SELECT c1 * ? FROM test.t1', 'SELECT c1 + ? FROM test.t1' );
108 CALL query_rewrite.flush_rewrite_rules();
109 # Should be rewritten.
110 SELECT c1 * 3 FROM test.t1;
111 c1 + 3
112 4
113 6
114 5
115 7
116 9
117 8
118 11
119 10
120 Warnings:
121 Note 1105 Query 'SELECT c1 * 3 FROM test.t1' rewritten to 'SELECT c1 + 3 FROM test.t1' by a query rewrite plugin
122 # Should not be rewritten.
123 SELECT c1 + 3 FROM test.t1;
124 c1 + 3
125 4
126 6
127 5
128 7
129 9
130 8
131 11
132 10
133 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
134 VALUES ( 'SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?',
135 'SELECT SUM( c1 ), c2, c3 FROM test.t1 GROUP BY ?, ?' );
136 CALL query_rewrite.flush_rewrite_rules();
137 # Should be rewritten.
138 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 2, 3;
139 SUM( c1 ) c2 c3
140 2 b c
141 3 c b
142 4 a a
143 5 f c
144 6 d b
145 7 e b
146 9 d a
147 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
148 Warnings:
149 # Should be rewritten.
150 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY 3, 2;
151 SUM( c1 ) c2 c3
152 2 b c
153 3 c b
154 4 a a
155 5 f c
156 6 d b
157 7 e b
158 9 d a
159 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
160 Warnings:
161 # Should not be rewritten.
162 SELECT COUNT( c1 ), c2, c3 FROM test.t1 GROUP BY c2, c3;
163 COUNT( c1 ) c2 c3
164 1 a a
165 1 b c
166 1 c b
167 1 d b
168 1 e b
169 1 f c
170 2 d a
171 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
172 VALUES ( 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c > ?',
173 'SELECT COUNT(c1) as c, c2, c3 FROM test.t1 GROUP BY ?, ? HAVING c < ?'
174 );
175 CALL query_rewrite.flush_rewrite_rules();
176 SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 2, 3 HAVING c > 2;
177 c c2 c3
178 1 a a
179 1 b c
180 1 c b
181 1 d b
182 1 e b
183 1 f c
184 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
185 Warnings:
186 SELECT COUNT( c1 ) as c, c2, c3 FROM test.t1 GROUP BY 3, 2 HAVING c > 3;
187 c c2 c3
188 1 a a
189 1 b c
190 1 c b
191 1 d b
192 1 e b
193 1 f c
194 2 d a
195 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
196 Warnings:
197 CREATE TABLE t2 ( c1 INTEGER, c2 CHAR ( 1 ), c3 CHAR ( 1 ) );
198 INSERT INTO t2 VALUES ( 1, 'd', 'a' ),
199 ( 2, 'b', 'c' ),
200 ( 6, 'd', 'b' ),
201 ( 5, 'f', 'c' );
202 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
203 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',
204 'SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 )' );
205 CALL query_rewrite.flush_rewrite_rules();
206 SELECT c.c1, c.c2 FROM test.t1 AS c JOIN test.t2 AS d ON ( c.c1=d.c1 ) WHERE c.c1=1;
207 c1 c2
208 1 d
209 2 b
210 6 d
211 5 f
212 Warnings:
213 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
214 SELECT c.c1, c.c2 FROM test.t1 AS d JOIN test.t2 AS c ON ( c.c1=d.c1 ) WHERE c.c1=1;
215 c1 c2
216 1 d
217 # Finish extraction in the from clause.
218 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
219 VALUES ( 'SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=? ) c',
220 'SELECT c1 FROM test.t1 WHERE c1=?' );
221 CALL query_rewrite.flush_rewrite_rules();
222 SELECT c.c1 FROM ( SELECT c1 FROM test.t1 WHERE c1=3 ) c;
223 c1
224 3
225 Warnings:
226 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
227 SET sql_mode = 'PIPES_AS_CONCAT';
228 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
229 VALUES ( 'SELECT c1,c2,c3 FROM test.t1 WHERE c1=? ' ||
230 'UNION ' ||
231 'SELECT c1 FROM test.t1 WHERE c1=? ORDER BY ?',
232 'SELECT c1,c2, c3 FROM test.t1 WHERE c1 IN ( ?,? ) ORDER BY ?' );
233 CALL query_rewrite.flush_rewrite_rules();
234 # That's right, query does not compile unless rewritten.
235 SELECT c1, c2, c3 FROM test.t1 WHERE c1 = 1
236 UNION
237 SELECT c1 FROM test.t1 WHERE c1 = 2 ORDER BY 2;
238 c1 c2 c3
239 2 b c
240 1 d a
241 Warnings:
242 Note 1105 Query 'SELECT c1, c2, c3 FROM test.t1 WHERE c1 = 1
243 UNION
244 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
245 # Test with many parameters.
246 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
247 VALUES ( 'SELECT c1, c2 FROM test.t1 WHERE c1 IN ' ||
248 '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )',
249 'SELECT c1, c2 FROM test.t1 WHERE c1 NOT IN ' ||
250 '( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' );
251 CALL query_rewrite.flush_rewrite_rules();
252 SELECT c1, c2
253 FROM test.t1
254 WHERE c1 IN ( 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14 );
255 c1 c2
256 6 d
257 Warnings:
258 Note 1105 Query 'SELECT c1, c2
259 FROM test.t1
260 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
261 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
262 VALUES ( 'SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=? ) t'
263 ' FROM test.t1, '
264 ' ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=? ) tb ) tc'
265 ' WHERE test.t1.c2=tc.c2 AND tc.c1 < ?',
266 'SELECT test.t1.c1, test.t2.c1, test.t2.c2, test.t1.c2'
267 ' FROM test.t1 JOIN test.t2 ON ( test.t1.c1=t2.c1 AND test.t2.c1 IN ( ?, ? ) )'
268 ' WHERE test.t1.c1 > ?' );
269 CALL query_rewrite.flush_rewrite_rules();
270 SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=2 ) t
271 FROM test.t1,
272 ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=1 ) tb ) tc
273 WHERE test.t1.c2=tc.c2 AND tc.c1 < 0;
274 c1 c1 c2 c2
275 1 1 d d
276 2 2 b b
277 Warnings:
278 Note 1105 Query 'SELECT test.t1.c1, ( SELECT c1 FROM test.t2 WHERE c1=2 ) t
279 FROM test.t1,
280 ( SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=1 ) tb ) tc
281 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
282 # Check our status variables:.
283 SHOW STATUS LIKE 'Rewriter%';
284 Variable_name Value
285 Rewriter_number_loaded_rules 11
286 Rewriter_number_reloads 12
287 Rewriter_number_rewritten_queries 15
288 Rewriter_reload_error OFF
289 DROP TABLE t1, t2;
290 SET sql_mode = DEFAULT;
291 Warnings:
292 Warning 1620 Plugin is busy and will be uninstalled on shutdown
293 # Query rewrite plugin was queued for uninstalling.