Debian Codegraph @

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

raw
1 #
2 # Checks the basic functionality of the plugin.
3 #
4 # Query rewrite plugin was installed.
5 # checks that the plugin is well installed
6 SELECT PLUGIN_NAME, PLUGIN_TYPE
7 FROM information_schema.plugins
8 WHERE plugin_name = 'Rewriter';
9 PLUGIN_NAME PLUGIN_TYPE
10 Rewriter AUDIT
11 CREATE TABLE t1 ( a VARCHAR(10), b VARCHAR(10) );
12 INSERT INTO t1 VALUES ( 'abc', 'def' ), ( 'ghi', 'klm' ), ( 'nop', 'qrs' );
13 CREATE TABLE t2 ( a VARCHAR(10) );
14 INSERT INTO t2 VALUES ( 'abc' ), ( 'klm' );
15 # Test of literals matching.
16 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
17 VALUES ( 'SELECT ?', 'SELECT "literal"' );
18 SELECT * FROM query_rewrite.rewrite_rules;
19 id pattern pattern_database replacement enabled message
20 1 SELECT ? NULL SELECT "literal" YES NULL
21 CALL query_rewrite.flush_rewrite_rules();
22 SELECT * FROM query_rewrite.rewrite_rules;
23 id pattern pattern_database replacement enabled message
24 1 SELECT ? NULL SELECT "literal" YES NULL
25 SELECT NULL;
26 literal
27 literal
28 Warnings:
29 Note 1105 Query 'SELECT NULL' rewritten to 'SELECT "literal"' by a query rewrite plugin
30 SELECT 'abc';
31 literal
32 literal
33 Warnings:
34 Note 1105 Query 'SELECT 'abc'' rewritten to 'SELECT "literal"' by a query rewrite plugin
35 SELECT 1;
36 literal
37 literal
38 Warnings:
39 Note 1105 Query 'SELECT 1' rewritten to 'SELECT "literal"' by a query rewrite plugin
40 SELECT 1.1;
41 literal
42 literal
43 Warnings:
44 Note 1105 Query 'SELECT 1.1' rewritten to 'SELECT "literal"' by a query rewrite plugin
45 SELECT 123456789123456789123456789123456789123456789123456789;
46 literal
47 literal
48 Warnings:
49 Note 1105 Query 'SELECT 123456789123456789123456789123456789123456789123456789' rewritten to 'SELECT "literal"' by a query rewrite plugin
50 # Check our status variables.
51 SHOW STATUS LIKE 'Rewriter%';
52 Variable_name Value
53 Rewriter_number_loaded_rules 1
54 Rewriter_number_reloads 2
55 Rewriter_number_rewritten_queries 5
56 Rewriter_reload_error OFF
57 DELETE FROM query_rewrite.rewrite_rules;
58 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
59 VALUES ( '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 = ?' );
69 SELECT * FROM query_rewrite.rewrite_rules;
70 id pattern pattern_database replacement enabled message
71 2 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL
72 3 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL
73 4 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL
74 5 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL
75 6 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL
76 CALL query_rewrite.flush_rewrite_rules();
77 SELECT * FROM query_rewrite.rewrite_rules;
78 id pattern pattern_database replacement enabled message
79 2 SELECT * FROM test.t1 WHERE ( a = ? AND TRUE ) OR b = ? NULL SELECT a FROM test.t1 WHERE a = ? YES NULL
80 3 SELECT a FROM test.t1 NULL SELECT * FROM test.t1 WHERE a = 'abc' YES NULL
81 4 SELECT a FROM test.t1 WHERE b = ? NULL SELECT * FROM test.t1 WHERE b = ? YES NULL
82 5 SELECT * FROM test.t2 NULL SELECT * FROM test.t1 JOIN test.t2 ON t1.a = t2.a YES NULL
83 6 SELECT * FROM test.t1 WHERE a = ? OR b = ? NULL SELECT * FROM test.t1 WHERE b = ? OR a = ? YES NULL
84 # Check a query that doesn't have a rewrite rule doesn't get rewritten.
85 SELECT b FROM test.t1;
86 b
87 def
88 klm
89 qrs
90 # Check a query with just one parameter.
91 SELECT a FROM test.t1 WHERE b = 'qrs';
92 a b
93 nop qrs
94 Warnings:
95 Note 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);
98 SELECT * FROM test.t1 WHERE ( a = 'abc' AND TRUE ) OR b = 'klm';
99 a
100 abc
101 Warnings:
102 Note 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
103 SELECT * FROM test.t1 WHERE ( a = 'abc' AND FALSE ) OR b = 'klm';
104 a b
105 ghi klm
106 # Check a non parameterized query.
107 SELECT * from t1 WHERE a = 'abc';
108 a b
109 abc def
110 # Check that a non-rewritten query does not yield a warning.
111 SELECT b FROM test.t1;
112 b
113 def
114 klm
115 qrs
116 # Check that a query is not rewritten if the query corresponds to a
117 # replacement.
118 SELECT a FROM test.t1;
119 a b
120 abc def
121 Warnings:
122 Note 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.
124 SELECT * FROM test.t2;
125 a b a
126 abc def abc
127 Warnings:
128 Note 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
129 SELECT * FROM test.t2;
130 a b a
131 abc def abc
132 Warnings:
133 Note 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.
135 SELECT b FROM test.t1;
136 b
137 def
138 klm
139 qrs
140 # Check parameter switching in a query rewrite
141 SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
142 a b
143 abc def
144 ghi klm
145 Warnings:
146 Note 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.
148 SELECT * FROM test.t1 WHERE a = 'def' OR b = 'ghi';
149 a b
150 abc def
151 ghi klm
152 Warnings:
153 Note 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.
155 SELECT * FROM test.t1 WHERE a =/* 'def' ? */'def' OR b = 'ghi';
156 a b
157 abc def
158 ghi klm
159 Warnings:
160 Note 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.
162 SHOW STATUS LIKE 'Rewriter%';
163 Variable_name Value
164 Rewriter_number_loaded_rules 5
165 Rewriter_number_reloads 3
166 Rewriter_number_rewritten_queries 13
167 Rewriter_reload_error OFF
168 DROP TABLE t1;
169 DROP TABLE t2;
170 DELETE FROM query_rewrite.rewrite_rules;
171 # Test of literals matching.
172 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
173 VALUES ( 'SELECT 1, ?', 'SELECT "rewritten w/rule 1"' ),
174 ( 'SELECT 2, ?', 'SELECT "rewritten w/rule 2"' ),
175 ( 'SELECT "The_original_query"', 'SELECT "The_rewritten_query"');
176 SELECT * FROM query_rewrite.rewrite_rules;
177 id pattern pattern_database replacement enabled message
178 7 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL
179 8 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL
180 9 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL
181 CALL query_rewrite.flush_rewrite_rules();
182 SELECT * FROM query_rewrite.rewrite_rules;
183 id pattern pattern_database replacement enabled message
184 7 SELECT 1, ? NULL SELECT "rewritten w/rule 1" YES NULL
185 8 SELECT 2, ? NULL SELECT "rewritten w/rule 2" YES NULL
186 9 SELECT "The_original_query" NULL SELECT "The_rewritten_query" YES NULL
187 SELECT 1, 1;
188 rewritten w/rule 1
189 rewritten w/rule 1
190 Warnings:
191 Note 1105 Query 'SELECT 1, 1' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
192 SELECT 1, 2;
193 rewritten w/rule 1
194 rewritten w/rule 1
195 Warnings:
196 Note 1105 Query 'SELECT 1, 2' rewritten to 'SELECT "rewritten w/rule 1"' by a query rewrite plugin
197 SELECT 2, 1;
198 rewritten w/rule 2
199 rewritten w/rule 2
200 Warnings:
201 Note 1105 Query 'SELECT 2, 1' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
202 SELECT 2, 2;
203 rewritten w/rule 2
204 rewritten w/rule 2
205 Warnings:
206 Note 1105 Query 'SELECT 2, 2' rewritten to 'SELECT "rewritten w/rule 2"' by a query rewrite plugin
207 SELECT 3, 1;
208 3 1
209 3 1
210 SELECT 3, 2;
211 3 2
212 3 2
213 SELECT 'The_original_query';
214 The_rewritten_query
215 The_rewritten_query
216 Warnings:
217 Note 1105 Query 'SELECT 'The_original_query'' rewritten to 'SELECT "The_rewritten_query"' by a query rewrite plugin
218 DELETE FROM query_rewrite.rewrite_rules;
219 Warnings:
220 Warning 1620 Plugin is busy and will be uninstalled on shutdown
221 # Query rewrite plugin was queued for uninstalling.