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