1--source include/have_rocksdb.inc 2 3# 4# Basic UPDATE statements. 5# UPDATE LOW_PRIORITY is covered in update_low_prio test 6# UPDATE IGNORE is covered in update_ignore test 7# Multi-table update is covered in update_multi test 8# 9 10######################################## 11# TODO: 12# The results of the transactional part 13# are unusual due to "can't-see-own-changes" 14######################################## 15 16--disable_warnings 17DROP TABLE IF EXISTS t1; 18--enable_warnings 19 20CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb; 21INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(10000,'foobar'); 22INSERT INTO t1 (a,b) SELECT a, b FROM t1; 23 24UPDATE t1 SET a=a+100; 25--sorted_result 26SELECT a,b FROM t1; 27 28UPDATE t1 SET a=a-100, b=DEFAULT WHERE a>100; 29--sorted_result 30SELECT a,b FROM t1; 31 32# ORDER BY and LIMIT 33UPDATE t1 SET b = 'update' WHERE a <= 4 ORDER BY b DESC, a ASC LIMIT 1; 34--sorted_result 35SELECT a,b FROM t1; 36 37DROP TABLE t1; 38 39# 40# Transactional UPDATE 41# 42 43CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb; 44INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(10000,'foobar'); 45INSERT INTO t1 (a,b) SELECT a, b FROM t1; 46 47BEGIN; 48UPDATE t1 SET a=a+100; 49UPDATE t1 SET a=a-50, b=DEFAULT WHERE a>100; 50COMMIT; 51--sorted_result 52SELECT * FROM t1 ORDER BY pk; 53 54BEGIN; 55UPDATE t1 SET b = 'update' WHERE a <= 4 ORDER BY a DESC, b ASC LIMIT 3; 56UPDATE t1 SET b = ''; 57ROLLBACK; 58SELECT * FROM t1 ORDER BY pk; 59 60BEGIN; 61UPDATE t1 SET b = 'update2' WHERE a <= 100; 62SAVEPOINT spt1; 63UPDATE t1 SET b = ''; 64--error ER_ROLLBACK_TO_SAVEPOINT 65ROLLBACK TO SAVEPOINT spt1; 66UPDATE t1 SET b = 'upd' WHERE a = 10050; 67--error ER_ROLLBACK_ONLY 68COMMIT; 69SELECT * FROM t1 ORDER BY pk; 70 71DROP TABLE t1; 72 73 74# 75# Issue #830 UPDATE with unique constraint does not work 76# 77 78CREATE TABLE t1 (a INT, b CHAR(8), UNIQUE INDEX(a)) ENGINE=RocksDB; 79INSERT INTO t1 (a,b) VALUES (1,'foo'),(2,'bar'); 80UPDATE t1 SET a=a+100; 81SELECT * FROM t1; 82DROP TABLE t1; 83