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