1--source include/have_rocksdb.inc
2
3--disable_warnings
4DROP TABLE IF EXISTS t1;
5--enable_warnings
6
7# Create the table and insert some keys
8CREATE TABLE t1 (
9       i INT,
10       a INT,
11       b INT,
12       PRIMARY KEY (i),
13       KEY ka(a),
14       KEY kb(b) comment 'rev:cf1'
15) ENGINE = rocksdb;
16
17--disable_query_log
18let $max = 20000;
19let $i = 1;
20while ($i <= $max) {
21  let $insert = INSERT INTO t1 VALUES ($i, $i, $i);
22  inc $i;
23  eval $insert;
24}
25--enable_query_log
26
27# get results for records_in_range prior to memtable flush
28# normal CF
29explain select * from t1 where a> 500 and a< 750;
30explain select * from t1 where a< 750;
31explain select * from t1 where a> 500;
32explain select * from t1 where a>=0 and a<=1000;
33
34#reverse CF
35explain select * from t1 where b> 500 and b< 750;
36explain select * from t1 where b< 750;
37explain select * from t1 where b> 500;
38explain select * from t1 where b>=0 and b<=1000;
39
40## cost calculation differences between covering vs non-covering (#298)
41set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range;
42set rocksdb_records_in_range = 15000;
43# covering, range
44explain select a from t1 where a < 750;
45# non-covering, full
46explain select a, b from t1 where a < 750;
47# covering, ref
48explain select a from t1 where a = 700;
49# non-covering, ref
50explain select a,b from t1 where a = 700;
51# covering, full index
52explain select a from t1 where a in (700, 800);
53# non-covering, full
54explain select a,b from t1 where a in (700, 800);
55set rocksdb_records_in_range=8000;
56# covering, range
57explain select a from t1 where a in (700, 800);
58# non-covering, full
59explain select a,b from t1 where a in (700, 800);
60set rocksdb_records_in_range = @save_rocksdb_records_in_range;
61
62# flush memtable and repeat
63set global rocksdb_force_flush_memtable_now = true;
64# normal CF
65explain select * from t1 where a> 500 and a< 750;
66explain select * from t1 where a< 750;
67explain select * from t1 where a> 500;
68explain select * from t1 where a>=0 and a<=1000;
69
70#reverse CF
71explain select * from t1 where b> 500 and b< 750;
72explain select * from t1 where b< 750;
73explain select * from t1 where b> 500;
74explain select * from t1 where b>=0 and b<=1000;
75
76# a set of 1
77explain select * from t1 where a>= 500 and a<= 500;
78explain select * from t1 where b>= 500 and b<= 500;
79
80# two indexes
81explain select * from t1 where a< 750 and b> 500 and b< 750;
82
83# composite index
84drop index ka on t1;
85drop index kb on t1;
86create index kab on t1(a,b);
87set global rocksdb_force_flush_memtable_now = true;
88explain select * from t1 where a< 750 and b> 500 and b< 750;
89
90# override records in range
91set rocksdb_records_in_range=444;
92explain select * from t1 where a< 750 and b> 500 and b< 750;
93set rocksdb_records_in_range=0;
94
95# issue 82
96## forward cf
97CREATE TABLE `linktable` (
98  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
99  `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
100  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
101  `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
102  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
103  `visibility` tinyint(3) NOT NULL DEFAULT '0',
104  `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
105  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
106  `version` int(11) unsigned NOT NULL DEFAULT '0',
107  PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk',
108  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'cf_link_id1_type'
109) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
110
111insert into linktable values (1,1,1,1,1,1,1,1,1);
112insert into linktable values (1,1,2,1,1,1,1,1,1);
113insert into linktable values (1,1,3,1,1,1,1,1,1);
114insert into linktable values (1,1,4,1,1,1,1,1,1);
115set global rocksdb_force_flush_memtable_now = true;
116explain select id1, id2, link_type, visibility, data, time,  version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2);
117drop table linktable;
118
119## rev cf
120CREATE TABLE `linktable` (
121  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
122  `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
123  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
124  `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
125  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
126  `visibility` tinyint(3) NOT NULL DEFAULT '0',
127  `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
128  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
129  `version` int(11) unsigned NOT NULL DEFAULT '0',
130  PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk',
131  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type'
132) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
133
134insert into linktable values (1,1,1,1,1,1,1,1,1);
135insert into linktable values (1,1,2,1,1,1,1,1,1);
136insert into linktable values (1,1,3,1,1,1,1,1,1);
137insert into linktable values (1,1,4,1,1,1,1,1,1);
138set global rocksdb_force_flush_memtable_now = true;
139explain select id1, id2, link_type, visibility, data, time,  version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2);
140drop table linktable;
141
142#cleanup
143DROP TABLE t1;
144