1--source include/have_rocksdb.inc
2
3# Fixing issue#230 -- Prefix bloom filter + reverse column family misses some rows
4# This test inserts 20,000 rows into t1, then selecting one by one from stored procedure.
5# If the select does not return any row, it is wrong.
6
7CREATE TABLE t1 (
8  `id1` int unsigned NOT NULL DEFAULT '0',
9  `id2` int unsigned NOT NULL DEFAULT '0',
10  `link_type` int unsigned NOT NULL DEFAULT '0',
11  `visibility` tinyint NOT NULL DEFAULT '0',
12  `data` varchar(255) NOT NULL DEFAULT '',
13  `time` int unsigned NOT NULL DEFAULT '0',
14  `version` int unsigned NOT NULL DEFAULT '0',
15  PRIMARY KEY (id1, link_type, visibility, id2) COMMENT 'rev:cf_link_pk'
16) ENGINE=RocksDB DEFAULT COLLATE=latin1_bin;
17
18DELIMITER //;
19CREATE PROCEDURE select_test()
20BEGIN
21 DECLARE id1_cond INT;
22 SET id1_cond = 1;
23 WHILE id1_cond <= 20000 DO
24   SELECT count(*) AS cnt FROM (SELECT id1 FROM t1 FORCE INDEX (PRIMARY) WHERE id1 = id1_cond AND link_type = 1 AND visibility = 1 ORDER BY id2 DESC) AS t INTO @cnt;
25   IF @cnt < 1 THEN
26     SELECT id1_cond, @cnt;
27   END IF;
28   SET id1_cond = id1_cond + 1;
29 END WHILE;
30END//
31DELIMITER ;//
32
33--disable_query_log
34let $i = 1;
35while ($i <= 20000) {
36  let $insert = INSERT INTO t1 VALUES($i, $i, 1, 1, $i, $i, $i);
37  eval $insert;
38  inc $i;
39}
40--enable_query_log
41
42--echo "Skipping bloom filter"
43SET session rocksdb_skip_bloom_filter_on_read=1;
44CALL select_test();
45
46--echo "Using bloom filter"
47SET session rocksdb_skip_bloom_filter_on_read=0;
48CALL select_test();
49
50DROP PROCEDURE select_test;
51drop table t1;
52
53