Debian Codegraph @

mysql-test/suite/query_rewrite_plugins/t/update.test

raw
1 --echo #
2 --echo # Test of rewrites of update statements.
3 --echo #
4
5 --source suite/query_rewrite_plugins/include/have_plugin_rewriter.inc
6 use test;
7 CREATE TABLE t1 (a INT NOT NULL, b int, PRIMARY KEY (a));
8 CREATE TABLE t2 (a int);
9 INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 30);
10 INSERT INTO t2 SELECT a FROM t1;
11
12 --source suite/query_rewrite_plugins/include/install_rewriter.inc
13
14 INSERT INTO query_rewrite.rewrite_rules ( pattern, pattern_database, replacement )
15 VALUES ( 'UPDATE test.t1 SET a = ? WHERE b = ? ORDER BY ? LIMIT ?', 'test',
16 'UPDATE test.t1 SET b = ? WHERE b = ? ORDER BY a LIMIT 4'),
17 ( 'UPDATE test.t1, test.t2 SET t1.a = ?, t2.a = ? WHERE t1.b = ?', 'test',
18 'UPDATE test.t1, test.t2 SET t1.a = ?, t1.b = ? WHERE t1.a = ?' ),
19 ('UPDATE t1 LEFT JOIN t2 ON t1.a = ? SET t1.b = 20 WHERE t1.b > 25', 'test',
20 'UPDATE t1 LEFT JOIN t2 ON t2.a = ? SET t1.b = 20 WHERE t1.b > 25') ;
21
22 CALL query_rewrite.flush_rewrite_rules();
23 SELECT * FROM query_rewrite.rewrite_rules;
24
25 # Tests the following in accept_update for single update: walk_item(value,
26 # visitor), ORDER BY and LIMIT
27 UPDATE test.t1 SET a = 0 WHERE b = 2 ORDER BY 3 LIMIT 2;
28
29 # This isn't re-written.
30 UPDATE test.t1 SET a = 0 WHERE b = 2 ORDER BY a LIMIT 2;
31 SELECT * FROM t1;
32
33 # Tests the following in accept_update for multi update: walk_item(value,
34 # visitor)
35 UPDATE test.t1, test.t2 SET t1.a = 2, t2.a = 25 WHERE t1.b = 21;
36 SELECT * FROM t1;
37 SELECT * FROM t2;
38
39 # Tests the following in accept_update for multi update: accept_join
40 UPDATE t1 LEFT JOIN t2 ON t1.a = 25 SET t1.b = 20 WHERE t1.b > 25;
41
42 DROP TABLE t1, t2;
43
44 --source suite/query_rewrite_plugins/include/uninstall_rewriter.inc