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