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# MyRocks does not support gap locks in REPEATABLE-READ mode, test does not 11# require RR ISO to complete, so lets alter the ISO to RC 12set session transaction_isolation='READ-COMMITTED'; 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# Transactional DELETE 60# 61 62CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=ROCKSDB; 63INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(10000,'foobar'); 64INSERT INTO t1 (a,b) SELECT a, b FROM t1; 65 66BEGIN; 67DELETE FROM t1 WHERE b IN ('c'); 68--sorted_result 69SELECT a,b FROM t1; 70DELETE FROM t1 WHERE a < 0 OR b = 'a'; 71COMMIT; 72--sorted_result 73SELECT a,b FROM t1; 74 75# Savepoints 76 77BEGIN; 78DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1; 79SAVEPOINT spt1; 80 81DELETE FROM t1; 82RELEASE SAVEPOINT spt1; 83ROLLBACK; 84--sorted_result 85SELECT a,b FROM t1; 86 87BEGIN; 88DELETE FROM t1 WHERE a <= 4 ORDER BY b DESC LIMIT 1; 89SAVEPOINT spt1; 90DELETE FROM t1; 91INSERT INTO t1 (a,b) VALUES (1,'a'); 92--error ER_ROLLBACK_TO_SAVEPOINT 93ROLLBACK TO SAVEPOINT spt1; 94--error ER_ROLLBACK_ONLY 95COMMIT; 96--sorted_result 97SELECT a,b FROM t1; 98 99DROP TABLE t1; 100