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