Debian Codegraph @

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

raw
1 #
2 # Test of rewrites of insert statements.
3 #
4 use test;
5 CREATE TABLE t1 ( a INT, b INT , c INT, PRIMARY KEY(a));
6 CREATE TABLE t2 ( c INT, d INT );
7 INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (4, 5);
8 INSERT INTO t2 VALUES (10, 20);
9 # Query rewrite plugin was installed.
10 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
11 VALUES ( 'INSERT INTO test.t1 ( a, b ) VALUES ( ?, ? )
12 ON DUPLICATE KEY UPDATE c = ?, a = ?',
13 'INSERT INTO test.t1 ( b, a ) VALUES ( ?, ? )
14 ON DUPLICATE KEY UPDATE c = ?, a = 25' ),
15 ( 'INSERT INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (?, ?)',
16 'INSERT INTO test.t1 ( a, b ) VALUES ( ?, ? )' ),
17 ( 'REPLACE INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (?, ?)',
18 'REPLACE INTO test.t1 ( a, b ) VALUES ( ?, ? )' ),
19 ('INSERT INTO test.t1 ( a, b ) SELECT c + 2222, 2 FROM test.t2',
20 'INSERT /*+ QB_NAME(hi) */ INTO test.t1 ( a, b )
21 SELECT c + 2222, 2 FROM test.t2'),
22 ('INSERT INTO test.t1(a, c) VALUES(?, ?), (?, ?)',
23 'INSERT INTO test.t1(a, b) VALUES(?, ?), (?, ?)'),
24 ('REPLACE INTO test.t1(a, c) VALUES(?, ?)',
25 'REPLACE INTO test.t1(a, c) VALUES(?, 2)'),
26 ('INSERT INTO test.t1 ( a, b ) SELECT ?, ? FROM test.t2',
27 'INSERT INTO test.t1 ( a, c ) SELECT ?, ? FROM test.t2');
28 CALL query_rewrite.flush_rewrite_rules();
29 SELECT * FROM query_rewrite.rewrite_rules;
30 id pattern pattern_database replacement enabled message
31 1 INSERT INTO test.t1 ( a, b ) VALUES ( ?, ? )
32 ON DUPLICATE KEY UPDATE c = ?, a = ? NULL INSERT INTO test.t1 ( b, a ) VALUES ( ?, ? )
33 ON DUPLICATE KEY UPDATE c = ?, a = 25 YES NULL
34 2 INSERT INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (?, ?) NULL INSERT INTO test.t1 ( a, b ) VALUES ( ?, ? ) YES NULL
35 3 REPLACE INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (?, ?) NULL REPLACE INTO test.t1 ( a, b ) VALUES ( ?, ? ) YES NULL
36 4 INSERT INTO test.t1 ( a, b ) SELECT c + 2222, 2 FROM test.t2 NULL INSERT /*+ QB_NAME(hi) */ INTO test.t1 ( a, b )
37 SELECT c + 2222, 2 FROM test.t2 YES NULL
38 5 INSERT INTO test.t1(a, c) VALUES(?, ?), (?, ?) NULL INSERT INTO test.t1(a, b) VALUES(?, ?), (?, ?) YES NULL
39 6 REPLACE INTO test.t1(a, c) VALUES(?, ?) NULL REPLACE INTO test.t1(a, c) VALUES(?, 2) YES NULL
40 7 INSERT INTO test.t1 ( a, b ) SELECT ?, ? FROM test.t2 NULL INSERT INTO test.t1 ( a, c ) SELECT ?, ? FROM test.t2 YES NULL
41 INSERT INTO test.t1 ( a, b ) VALUES ( 2, 1 )
42 ON DUPLICATE KEY UPDATE c = 5, a = 5;
43 Warnings:
44 Note 1105 Query 'INSERT INTO test.t1 ( a, b ) VALUES ( 2, 1 )
45 ON DUPLICATE KEY UPDATE c = 5, a = 5' rewritten to 'INSERT INTO test.t1 ( b, a ) VALUES ( 2, 1 )
46 ON DUPLICATE KEY UPDATE c = 5, a = 25' by a query rewrite plugin
47 SELECT * FROM t1;
48 a b c
49 3 4 NULL
50 4 5 NULL
51 25 2 5
52 INSERT INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (11, 31);
53 Warnings:
54 Note 1105 Query 'INSERT INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (11, 31)' rewritten to 'INSERT INTO test.t1 ( a, b ) VALUES ( 11, 31 )' by a query rewrite plugin
55 SELECT * FROM t1;
56 a b c
57 3 4 NULL
58 4 5 NULL
59 11 31 NULL
60 25 2 5
61 INSERT INTO test.t1( a, b ) SELECT c + 2222, 2 FROM test.t2;
62 Warnings:
63 Note 1105 Query 'INSERT INTO test.t1( a, b ) SELECT c + 2222, 2 FROM test.t2' rewritten to 'INSERT /*+ QB_NAME(hi) */ INTO test.t1 ( a, b )
64 SELECT c + 2222, 2 FROM test.t2' by a query rewrite plugin
65 SELECT * FROM t1;
66 a b c
67 3 4 NULL
68 4 5 NULL
69 11 31 NULL
70 25 2 5
71 2232 2 NULL
72 REPLACE INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (100, 300);
73 Warnings:
74 Note 1105 Query 'REPLACE INTO test.t1 ( a, b ) SELECT c, d FROM test.t2 WHERE c IN (100, 300)' rewritten to 'REPLACE INTO test.t1 ( a, b ) VALUES ( 100, 300 )' by a query rewrite plugin
75 SELECT * FROM t1;
76 a b c
77 3 4 NULL
78 4 5 NULL
79 11 31 NULL
80 25 2 5
81 100 300 NULL
82 2232 2 NULL
83 INSERT INTO test.t1(a, c) VALUES(1001, 200), (1000, 2000);
84 Warnings:
85 Note 1105 Query 'INSERT INTO test.t1(a, c) VALUES(1001, 200), (1000, 2000)' rewritten to 'INSERT INTO test.t1(a, b) VALUES(1001, 200), (1000, 2000)' by a query rewrite plugin
86 REPLACE INTO test.t1(a, c) VALUES(1002, 200);
87 Warnings:
88 Note 1105 Query 'REPLACE INTO test.t1(a, c) VALUES(1002, 200)' rewritten to 'REPLACE INTO test.t1(a, c) VALUES(1002, 2)' by a query rewrite plugin
89 SELECT * FROM t1;
90 a b c
91 3 4 NULL
92 4 5 NULL
93 11 31 NULL
94 25 2 5
95 100 300 NULL
96 1000 2000 NULL
97 1001 200 NULL
98 1002 NULL 2
99 2232 2 NULL
100 INSERT INTO test.t1 ( a, b ) SELECT 22, 3 FROM test.t2;
101 Warnings:
102 Note 1105 Query 'INSERT INTO test.t1 ( a, b ) SELECT 22, 3 FROM test.t2' rewritten to 'INSERT INTO test.t1 ( a, c ) SELECT 22, 3 FROM test.t2' by a query rewrite plugin
103 INSERT INTO test.t1 ( a, b ) SELECT c, d FROM test.t2;
104 SELECT * FROM t1;
105 a b c
106 3 4 NULL
107 4 5 NULL
108 10 20 NULL
109 11 31 NULL
110 22 NULL 3
111 25 2 5
112 100 300 NULL
113 1000 2000 NULL
114 1001 200 NULL
115 1002 NULL 2
116 2232 2 NULL
117 DROP TABLE t1, t2;
118 Warnings:
119 Warning 1620 Plugin is busy and will be uninstalled on shutdown
120 # Query rewrite plugin was queued for uninstalling.