1--source include/have_rocksdb.inc 2 3# 4# INT column types 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 i INT PRIMARY KEY, 16 t TINYINT, 17 s SMALLINT, 18 m MEDIUMINT, 19 b BIGINT 20) ENGINE=rocksdb; 21 22INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); 23 24--replace_column 9 # 25EXPLAIN SELECT i FROM t1 ORDER BY i; 26SELECT i FROM t1 ORDER BY i; 27 28DROP TABLE t1; 29 30CREATE TABLE t1 ( 31 i INT, 32 t TINYINT, 33 s SMALLINT, 34 m MEDIUMINT, 35 b BIGINT, 36 pk SMALLINT AUTO_INCREMENT PRIMARY KEY, 37 INDEX s_m (s,m) 38) ENGINE=rocksdb; 39 40INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); 41 42--replace_column 9 # 43EXPLAIN SELECT s, m FROM t1 WHERE s != 10 AND m != 1; 44--sorted_result 45SELECT s, m FROM t1 WHERE s != 10 AND m != 1; 46 47DROP TABLE t1; 48 49--echo # RocksDB: unique indexes allowed 50#--error ER_GET_ERRMSG 51CREATE TABLE t1 ( 52 i INT, 53 t TINYINT, 54 s SMALLINT, 55 m MEDIUMINT, 56 b BIGINT, 57 pk MEDIUMINT AUTO_INCREMENT PRIMARY KEY, 58 UNIQUE KEY b_t (b,t) 59) ENGINE=rocksdb; 60 61##--disable_parsing 62 63INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16); 64 65# This query should use the index b_t, we just don't want to run EXPLAIN 66# (to avoid mismatches due to different subquery-related plans) 67SELECT b+t FROM t1 WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t; 68SELECT b+t FROM t1 FORCE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t; 69SELECT b+t FROM t1 IGNORE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t; 70 71DROP TABLE t1; 72 73##--enable_parsing 74 75SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK; 76