1--source include/have_rocksdb.inc
2
3#
4# Basic DELETE statements.
5# DELETE LOW_PRIORITY is covered in delete_low_prio test
6# DELETE QUICK is covered in delete_quick test (syntax only)
7# DELETE IGNORE is covered in delete_ignore test
8#
9
10--disable_warnings
11DROP TABLE IF EXISTS t1,t2;
12--enable_warnings
13
14CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb;
15INSERT INTO t1 (a,b) VALUES (10000,'foobar'),(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
16INSERT INTO t1 (a,b) SELECT a, b FROM t1;
17
18# Single-table DELETE
19
20DELETE FROM t1 WHERE b IN ('c');
21--sorted_result
22SELECT a,b FROM t1;
23
24DELETE FROM t1 WHERE a < 0 OR b = 'a';
25--sorted_result
26SELECT a,b FROM t1;
27
28# ORDER BY and LIMIT
29DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
30--sorted_result
31SELECT a,b FROM t1;
32
33# Multi-table DELETE
34
35CREATE TABLE t2 (c CHAR(8), d INT, pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb;
36INSERT INTO t2 (c,d) SELECT b, a FROM t1;
37--sorted_result
38SELECT c,d FROM t2;
39
40DELETE t2.* FROM t1, t2 WHERE c < b AND a + d != 1;
41--sorted_result
42SELECT a,b FROM t1;
43--sorted_result
44SELECT c,d FROM t2;
45
46DELETE FROM t2, t1.* USING t2, t1 WHERE c = 'foobar' and b = c;
47--sorted_result
48SELECT a,b FROM t1;
49--sorted_result
50SELECT c,d FROM t2;
51
52DELETE FROM t1;
53--sorted_result
54SELECT a,b FROM t1;
55
56DROP TABLE t1, t2;
57
58
59#
60# Transactional DELETE
61#
62
63CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb;
64INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(10000,'foobar');
65INSERT INTO t1 (a,b) SELECT a, b FROM t1;
66
67BEGIN;
68DELETE FROM t1 WHERE b IN ('c');
69--sorted_result
70SELECT a,b FROM t1;
71DELETE FROM t1 WHERE a < 0 OR b = 'a';
72COMMIT;
73--sorted_result
74SELECT a,b FROM t1;
75
76# Savepoints
77
78BEGIN;
79DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
80SAVEPOINT spt1;
81
82DELETE FROM t1;
83RELEASE SAVEPOINT spt1;
84ROLLBACK;
85--sorted_result
86SELECT a,b FROM t1;
87
88BEGIN;
89DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1;
90SAVEPOINT spt1;
91DELETE FROM t1;
92INSERT INTO t1 (a,b) VALUES (1,'a');
93--error ER_ROLLBACK_TO_SAVEPOINT
94ROLLBACK TO SAVEPOINT spt1;
95--error ER_ROLLBACK_ONLY
96COMMIT;
97--sorted_result
98SELECT a,b FROM t1;
99
100DROP TABLE t1;
101
102