1--source include/have_rocksdb.inc
2
3#
4# UPDATE statements for tables with keys
5#
6
7#############################################
8# TODO:
9# The test doesn't work quite as expected,
10# apparently due to "can't see own changes"
11#############################################
12
13--disable_warnings
14DROP TABLE IF EXISTS t1;
15--enable_warnings
16
17CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY, INDEX(b)) ENGINE=rocksdb;
18INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'x'),(7,'y'),(8,'z');
19UPDATE t1 SET a=100, b='f' WHERE b IN ('b','c');
20UPDATE t1 SET b='m' WHERE b = 'f';
21UPDATE t1 SET b='z' WHERE a < 2;
22UPDATE t1 SET b='';
23--sorted_result
24SELECT a,b FROM t1;
25DROP TABLE t1;
26
27--echo # RocksDB: skip the test for secondary UNIQUE keys.
28--disable_parsing
29--error ER_GET_ERRMSG
30CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY, UNIQUE INDEX(a)) ENGINE=innodb;
31
32
33INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(0,'f'),(100,'a');
34UPDATE t1 SET a=a+200;
35UPDATE t1 SET a=0 WHERE a > 250;
36--error ER_DUP_ENTRY
37UPDATE t1 SET a=205 WHERE a=200;
38UPDATE t1 SET a=12345 ORDER BY a, b LIMIT 1;
39--sorted_result
40SELECT a,b FROM t1;
41
42--error ER_DUP_ENTRY
43UPDATE t1 SET a=80 WHERE a IN (202,203);
44--sorted_result
45SELECT a,b FROM t1;
46DROP TABLE t1;
47
48CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY, UNIQUE INDEX(a,b)) ENGINE=rocksdb;
49INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(100,'a'),(6,'f');
50UPDATE t1 SET a=6 WHERE a=3;
51--error ER_DUP_ENTRY
52UPDATE t1 SET a=100 WHERE a=1;
53--error ER_DUP_ENTRY
54UPDATE t1 SET a=4, b='d' WHERE b='f';
55UPDATE t1 SET a=a+1;
56--sorted_result
57SELECT a,b FROM t1;
58--error ER_DUP_ENTRY
59UPDATE t1 SET b='z';
60DROP TABLE t1;
61
62--enable_parsing
63
64CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb;
65INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(0,'f'),(100,'a');
66UPDATE t1 SET a=a+200;
67UPDATE t1 SET a=0 WHERE a > 250;
68--error ER_DUP_ENTRY
69UPDATE t1 SET a=205 WHERE a=200;
70UPDATE t1 SET a=12345 ORDER BY a DESC, b LIMIT 1;
71
72--sorted_result
73SELECT a,b FROM t1;
74
75--error ER_DUP_ENTRY
76UPDATE t1 SET a=80 WHERE a IN (202,203);
77DROP TABLE t1;
78
79