Debian Codegraph @

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

raw
1 #
2 # Test of rewrites of delete statements.
3 #
4 use test;
5 CREATE TABLE t1 (
6 a INT,
7 b INT )
8 PARTITION BY LIST(a) (
9 PARTITION p0 VALUES IN (1, 3),
10 PARTITION p1 VALUES IN (5, 7)
11 );
12 CREATE TABLE t2 ( a INT, b INT );
13 INSERT INTO t1 VALUES (1, 2), (3, 4), (5, 2), (7, 4);
14 INSERT INTO t2 VALUES (10, 20), (30, 40), (50, 60);
15 CREATE VIEW v1 AS SELECT t1.* FROM t1 JOIN t2 ON t1.b=t2.b;
16 CREATE VIEW v2 AS SELECT b, a FROM t1 WHERE b > 20;
17 # Query rewrite plugin was installed.
18 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
19 VALUES ( 'DELETE FROM test.t1 PARTITION (p0) WHERE a = ? ORDER BY b LIMIT 2',
20 'DELETE FROM test.t1 PARTITION (p0) WHERE b = ? ORDER BY b LIMIT 2' ),
21 ( 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a = ?',
22 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a IN (?,3,5)' ),
23 ( 'DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a = ?',
24 'DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a IN (?,3,5)' ),
25 ( 'DELETE test.v2 FROM test.v2 WHERE test.v2.a = ?',
26 'DELETE test.v2 FROM test.v2 WHERE test.v2.a IN (?,3,5)' ),
27 ( 'DELETE FROM test.v1',
28 'DELETE FROM test.t2' );
29 CALL query_rewrite.flush_rewrite_rules();
30 DELETE FROM test.t1 PARTITION (p0) WHERE a = 4 ORDER BY b LIMIT 2;
31 Warnings:
32 Note 1105 Query 'DELETE FROM test.t1 PARTITION (p0) WHERE a = 4 ORDER BY b LIMIT 2' rewritten to 'DELETE FROM test.t1 PARTITION (p0) WHERE b = 4 ORDER BY b LIMIT 2' by a query rewrite plugin
33 SELECT * FROM t1;
34 a b
35 1 2
36 5 2
37 7 4
38 DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a = 1;
39 Warnings:
40 Note 1105 Query 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a = 1' rewritten to 'DELETE test.t1 FROM test.t1 join test.t2 WHERE test.t1.a IN (1,3,5)' by a query rewrite plugin
41 SELECT * FROM t1;
42 a b
43 7 4
44 SELECT * FROM t2;
45 a b
46 10 20
47 30 40
48 50 60
49 DELETE test.v1 FROM test.v1 join test.t2 WHERE test.v1.a = 1;
50 ERROR HY000: Can not delete from join view 'test.v1'
51 DELETE test.v2 FROM test.v2 WHERE test.v2.a = 1;
52 Warnings:
53 Note 1105 Query 'DELETE test.v2 FROM test.v2 WHERE test.v2.a = 1' rewritten to 'DELETE test.v2 FROM test.v2 WHERE test.v2.a IN (1,3,5)' by a query rewrite plugin
54 DELETE FROM test.v1;
55 Warnings:
56 Note 1105 Query 'DELETE FROM test.v1' rewritten to 'DELETE FROM test.t2' by a query rewrite plugin
57 DROP VIEW v1, v2;
58 DROP TABLE t1, t2;
59 Warnings:
60 Warning 1620 Plugin is busy and will be uninstalled on shutdown
61 # Query rewrite plugin was queued for uninstalling.