Debian Codegraph @

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

raw
1 #
2 # Inserts some rules, tests them and then inserts another one.
3 #
4 use test;
5 CREATE TABLE t1 ( c1 VARCHAR(10), c2 VARCHAR(10) );
6 INSERT INTO t1 VALUES ( 'abc', 'def' ),
7 ( 'ghi', 'klm' ),
8 ( 'nop', 'qrs' );
9 CREATE TABLE t2 ( c1 VARCHAR(10) );
10 INSERT INTO t2 VALUES ( 'abc' ),
11 ( 'klm' );
12 # Query rewrite plugin was installed.
13 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
14 VALUES ( 'SELECT * FROM test.t1 WHERE ( c1 = ? AND TRUE ) OR c2 = ?',
15 'SELECT c1 FROM test.t1 WHERE c1 = ?' ),
16 ( 'SELECT * FROM test.t2',
17 'SELECT * FROM test.t1 JOIN test.t2 ON test.t1.c1 = t2.c1' );
18 CALL query_rewrite.flush_rewrite_rules();
19 SHOW STATUS LIKE 'Rewriter_number_loaded_rules';
20 Variable_name Value
21 Rewriter_number_loaded_rules 2
22 SHOW STATUS LIKE 'Rewriter_number_reloads';
23 Variable_name Value
24 Rewriter_number_reloads 2
25 SELECT c2 FROM test.t1;
26 c2
27 def
28 klm
29 qrs
30 # Check that a rewrite works.
31 SELECT * FROM test.t2;
32 c1 c2 c1
33 abc def abc
34 Warnings:
35 Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON test.t1.c1 = t2.c1' by a query rewrite plugin
36 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
37 VALUES ( 'SELECT c2 FROM test.t1', 'SELECT * FROM test.t1' );
38 CALL query_rewrite.flush_rewrite_rules();
39 SHOW STATUS LIKE 'Rewriter_number_loaded_rules';
40 Variable_name Value
41 Rewriter_number_loaded_rules 3
42 SHOW STATUS LIKE 'Rewriter_number_reloads';
43 Variable_name Value
44 Rewriter_number_reloads 3
45 SET @@global.rewriter_verbose = 2;
46 # Check that the new rule works.
47 SELECT c2 FROM test.t1;
48 c1 c2
49 abc def
50 ghi klm
51 nop qrs
52 Warnings:
53 Note 1105 Query 'SELECT c2 FROM test.t1' rewritten to 'SELECT * FROM test.t1' by a query rewrite plugin
54 # Check that the old rewrites still work.
55 SELECT * FROM test.t2;
56 c1 c2 c1
57 abc def abc
58 Warnings:
59 Note 1105 Query 'SELECT * FROM test.t2' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON test.t1.c1 = t2.c1' by a query rewrite plugin
60 DROP TABLE t1, t2;
61 Warnings:
62 Warning 1620 Plugin is busy and will be uninstalled on shutdown
63 # Query rewrite plugin was queued for uninstalling.