Debian Codegraph @

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

raw
1 --echo #
2 --echo # Test of rewrites of delete statements.
3 --echo #
4
5 --source suite/query_rewrite_plugins/include/have_plugin_rewriter.inc
6 use test;
7 CREATE TABLE t1 (
8 a INT,
9 b INT )
10 PARTITION BY LIST(a) (
11 PARTITION p0 VALUES IN (1, 3),
12 PARTITION p1 VALUES IN (5, 7)
13 );
14 CREATE TABLE t2 ( a INT, b INT );
15 INSERT INTO t1 VALUES (1, 2), (3, 4), (5, 2), (7, 4);
16 INSERT INTO t2 VALUES (10, 20), (30, 40), (50, 60);
17 CREATE VIEW v1 AS SELECT t1.* FROM t1 JOIN t2 ON t1.b=t2.b;
18 CREATE VIEW v2 AS SELECT b, a FROM t1 WHERE b > 20;
19
20 --source suite/query_rewrite_plugins/include/install_rewriter.inc
21
22 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
23 VALUES ( 'DELETE FROM test.t1 PARTITION (p0) WHERE a = ? ORDER BY b LIMIT 2',
24 'DELETE FROM test.t1 PARTITION (p0) WHERE b = ? ORDER BY b LIMIT 2' ),
25
26 ( 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a = ?',
27 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a IN (?,3,5)' ),
28
29 ( 'DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a = ?',
30 'DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a IN (?,3,5)' ),
31
32 ( 'DELETE test.v2 FROM test.v2 WHERE test.v2.a = ?',
33 'DELETE test.v2 FROM test.v2 WHERE test.v2.a IN (?,3,5)' ),
34
35 ( 'DELETE FROM test.v1',
36 'DELETE FROM test.t2' );
37
38 CALL query_rewrite.flush_rewrite_rules();
39
40 DELETE FROM test.t1 PARTITION (p0) WHERE a = 4 ORDER BY b LIMIT 2;
41 SELECT * FROM t1;
42
43 DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a = 1;
44 SELECT * FROM t1;
45 SELECT * FROM t2;
46
47 --error ER_VIEW_DELETE_MERGE_VIEW
48 DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a = 1;
49
50 # Unlike in EXPLAIN, v2 isn't resolved to t1.
51 DELETE test.v2 FROM test.v2 WHERE test.v2.a = 1;
52
53 # Replace view with a table.
54 DELETE FROM test.v1;
55
56 DROP VIEW v1, v2;
57 DROP TABLE t1, t2;
58
59 --source suite/query_rewrite_plugins/include/uninstall_rewriter.inc