1--source include/have_rocksdb.inc
2
3#
4# SET columns with indexes
5#
6
7SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
8SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
9
10--disable_warnings
11DROP TABLE IF EXISTS t1;
12--enable_warnings
13
14CREATE TABLE t1 (
15  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
16  b SET('test1','test2','test3','test4','test5'),
17  c SET('01','22','23','33','34','39','40','44','50','63','64') PRIMARY KEY
18) ENGINE=rocksdb;
19
20SHOW INDEX IN t1;
21
22INSERT INTO t1 (a,b,c) VALUES
23('','test2,test3','01,34,44,23'),
24('',5,2),
25('N.America,Asia','test4,test2',''),
26('Africa,Europe,Asia','test2,test3','01'),
27('Antarctica','test3','34,44'),
28('Asia','test5','50'),
29('Europe,S.America','test1,','39');
30
31--replace_column 9 #
32EXPLAIN SELECT c FROM t1 ORDER BY c;
33SELECT c FROM t1 ORDER BY c;
34
35--replace_column 9 #
36EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
37SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
38
39--error ER_DUP_ENTRY
40INSERT INTO t1 (a,b,c) VALUES ('Antarctica','test3','02');
41
42--error ER_DUP_ENTRY
43INSERT INTO t1 (a,b,c) VALUES ('','test1','34,44');
44
45DROP TABLE t1;
46
47CREATE TABLE t1 (
48  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
49  b SET('test1','test2','test3','test4','test5'),
50  c SET('01','22','23','33','34','39','40','44','50','63','64'),
51  pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
52  INDEX(a)
53) ENGINE=rocksdb;
54
55SHOW INDEX IN t1;
56
57INSERT INTO t1 (a,b,c,pk) VALUES
58('','test2,test3','01,34,44,23',1),
59('',5,2,2),
60('N.America,Asia','test4,test2','',3),
61('Africa,Europe,Asia','test2,test3','01',4),
62('Antarctica','test3','34,44',5),
63('Asia','test5','50',6),
64('Europe,S.America','test1,','39',7);
65
66--replace_column 9 #
67EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
68--sorted_result
69SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
70
71DROP TABLE t1;
72
73CREATE TABLE t1 (
74  a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
75  b SET('test1','test2','test3','test4','test5'),
76  c SET('01','22','23','33','34','39','40','44','50','63','64'),
77  pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
78  UNIQUE INDEX b_a (b,a)
79) ENGINE=rocksdb;
80
81
82SHOW INDEX IN t1;
83
84INSERT INTO t1 (a,b,c,pk) VALUES
85('','test2,test3','01,34,44,23',1),
86('',5,2,2),
87('N.America,Asia','test4,test2','',3),
88('Africa,Europe,Asia','test2,test3','01',4),
89('Antarctica','test3','34,44',5),
90('Asia','test5','50',6),
91('Europe,S.America','test1,','39',7);
92
93--replace_column 9 #
94EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
95--sorted_result
96SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
97
98DROP TABLE t1;
99
100SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;
101