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