Debian Codegraph @

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

raw
1 CREATE TABLE t1 ( c1 INTEGER, c2 INTEGER );
2 CREATE TABLE t2 ( c1 INTEGER, c2 INTEGER );
3 INSERT INTO t1 VALUES (1, 2), (2, 0), (3, 1), (4, 3), (5, 4), (6, 1), (7, 2);
4 INSERT INTO t2 VALUES (1, 1), (3, 3), (4, 0), (6, 3), (9, 1), (8, 10);
5 # Query rewrite plugin was installed.
6 SET sql_mode = 'PIPES_AS_CONCAT';
7 INSERT INTO query_rewrite.rewrite_rules( pattern, replacement )
8 VALUES ( 'SELECT * FROM test.t1 JOIN test.t2 ON ' ||
9 '( test.t1.c1 = test.t2.c1 AND test.t1.c2 > ? )',
10 'SELECT * FROM test.t1 JOIN test.t2 ON ' ||
11 '( test.t1.c1 = test.t2.c1 AND test.t2.c2 > ? )' ),
12 ( 'SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=? ) b',
13 'SELECT * FROM test.t1 WHERE c1=?' ),
14 ( 'SELECT ( SELECT c1 FROM test.t2 WHERE c2=? ) c1',
15 'SELECT c1, c2 FROM test.t1 WHERE c2=?' ),
16 ( 'SELECT * FROM test.t1 ' ||
17 'JOIN ( SELECT * FROM test.t2 WHERE c1 > ? ) a ' ||
18 'ON ( test.t1.c1 = test.t2.c1 AND test.t1.c2 <> ? )',
19 'SELECT * FROM test.t1 JOIN test.t2 ' ||
20 'ON ( test.t1.c1 = test.t2.c1 AND test.t2.c1 > ? AND test.t1.c2 <> ? )'),
21 ( 'WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = ? ) SELECT c1 FROM qn',
22 'SELECT c1, c2+1 FROM test.t1 WHERE c2=?' ),
23 ( 'WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = ? ) SELECT * FROM qn, qn AS qn1',
24 'WITH qn(a,b) AS ( SELECT c1,c2+1 FROM test.t1 WHERE c2 = ? ) SELECT * FROM qn, qn AS qn1'
25 );
26 CALL query_rewrite.flush_rewrite_rules();
27 SELECT * FROM query_rewrite.rewrite_rules;
28 id pattern pattern_database replacement enabled message
29 1 SELECT * FROM test.t1 JOIN test.t2 ON ( test.t1.c1 = test.t2.c1 AND test.t1.c2 > ? ) NULL SELECT * FROM test.t1 JOIN test.t2 ON ( test.t1.c1 = test.t2.c1 AND test.t2.c2 > ? ) YES NULL
30 2 SELECT * FROM ( SELECT * FROM test.t2 WHERE c1=? ) b NULL SELECT * FROM test.t1 WHERE c1=? YES NULL
31 3 SELECT ( SELECT c1 FROM test.t2 WHERE c2=? ) c1 NULL SELECT c1, c2 FROM test.t1 WHERE c2=? YES NULL
32 4 SELECT * FROM test.t1 JOIN ( SELECT * FROM test.t2 WHERE c1 > ? ) a ON ( test.t1.c1 = test.t2.c1 AND test.t1.c2 <> ? ) NULL SELECT * FROM test.t1 JOIN test.t2 ON ( test.t1.c1 = test.t2.c1 AND test.t2.c1 > ? AND test.t1.c2 <> ? ) YES NULL
33 5 WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = ? ) SELECT c1 FROM qn NULL SELECT c1, c2+1 FROM test.t1 WHERE c2=? YES NULL
34 6 WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = ? ) SELECT * FROM qn, qn AS qn1 NULL WITH qn(a,b) AS ( SELECT c1,c2+1 FROM test.t1 WHERE c2 = ? ) SELECT * FROM qn, qn AS qn1 YES NULL
35 SELECT * FROM t1 JOIN t2 ON ( t1.c1 = t2.c1 AND t2.c2 > 2 );
36 c1 c2 c1 c2
37 3 1 3 3
38 6 1 6 3
39 SELECT * FROM ( SELECT * FROM test.t2 WHERE c1 = 3 ) b;
40 c1 c2
41 3 1
42 Warnings:
43 Note 1105 Query 'SELECT * FROM ( SELECT * FROM test.t2 WHERE c1 = 3 ) b' rewritten to 'SELECT * FROM test.t1 WHERE c1=3' by a query rewrite plugin
44 SELECT ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) c1;
45 c1 c2
46 3 1
47 6 1
48 Warnings:
49 Note 1105 Query 'SELECT ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) c1' rewritten to 'SELECT c1, c2 FROM test.t1 WHERE c2=1' by a query rewrite plugin
50 SELECT *
51 FROM test.t1 JOIN ( SELECT * FROM test.t2 WHERE c1 > 1 ) a
52 ON ( test.t1.c1 = test.t2.c1 AND test.t1.c2 <> 3 );
53 c1 c2 c1 c2
54 3 1 3 3
55 6 1 6 3
56 Warnings:
57 Note 1105 Query 'SELECT *
58 FROM test.t1 JOIN ( SELECT * FROM test.t2 WHERE c1 > 1 ) a
59 ON ( test.t1.c1 = test.t2.c1 AND test.t1.c2 <> 3 )' rewritten to 'SELECT * FROM test.t1 JOIN test.t2 ON ( test.t1.c1 = test.t2.c1 AND test.t2.c1 > 1 AND test.t1.c2 <> 3 )' by a query rewrite plugin
60 WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) SELECT c1 FROM qn;
61 c1 c2+1
62 3 2
63 6 2
64 Warnings:
65 Note 1105 Query 'WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) SELECT c1 FROM qn' rewritten to 'SELECT c1, c2+1 FROM test.t1 WHERE c2=1' by a query rewrite plugin
66 WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) SELECT * FROM qn, qn AS qn1;
67 a b a b
68 3 2 3 2
69 3 2 6 2
70 6 2 3 2
71 6 2 6 2
72 Note 1105 Query 'WITH qn AS ( SELECT c1 FROM test.t2 WHERE c2 = 1 ) SELECT * FROM qn, qn AS qn1' rewritten to 'WITH qn(a,b) AS ( SELECT c1,c2+1 FROM test.t1 WHERE c2 = 1 ) SELECT * FROM qn, qn AS qn1' by a query rewrite plugin
73 Warnings:
74 DROP TABLE t1;
75 DROP TABLE t2;
76 SET sql_mode = DEFAULT;
77 Warnings:
78 Warning 1620 Plugin is busy and will be uninstalled on shutdown
79 # Query rewrite plugin was queued for uninstalling.