EVOLUTION-MANAGER

Edit File: records_in_range.test

--source include/have_rocksdb.inc

--source include/restart_mysqld.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

# Create the table and insert some keys
CREATE TABLE t1 (
       i INT,
       a INT,
       b INT,
       PRIMARY KEY (i),
       KEY ka(a),
       KEY kb(b) comment 'rev:cf1'
) ENGINE = rocksdb;

--disable_query_log
let $max = 20000;
let $i = 1;
while ($i <= $max) {
  let $insert = INSERT INTO t1 VALUES ($i, $i, $i);
  inc $i;
  eval $insert;
}
--enable_query_log

# get results for records_in_range prior to memtable flush
# normal CF
explain extended select * from t1 where a> 500 and a< 750;
explain extended select * from t1 where a< 750;
explain extended select * from t1 where a> 500;
explain extended select * from t1 where a>=0 and a<=1000;

#reverse CF
explain extended select * from t1 where b> 500 and b< 750;
explain extended select * from t1 where b< 750;
explain extended select * from t1 where b> 500;
explain extended select * from t1 where b>=0 and b<=1000;

## cost calculation differences between covering vs non-covering (#298)
set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range;
set rocksdb_records_in_range = 15000;
# covering, range
explain extended select a from t1 where a < 750;
# non-covering, full
explain extended select a, b from t1 where a < 750;
# covering, ref
explain extended select a from t1 where a = 700;
# non-covering, ref
explain extended select a,b from t1 where a = 700;
# covering, full index
explain extended select a from t1 where a in (700, 800);
# non-covering, full
explain extended select a,b from t1 where a in (700, 800);
set rocksdb_records_in_range=8000;
# covering, range
explain extended select a from t1 where a in (700, 800);
# non-covering, full
explain extended select a,b from t1 where a in (700, 800);
set rocksdb_records_in_range = @save_rocksdb_records_in_range;

# flush memtable and repeat
set global rocksdb_force_flush_memtable_now = true;
# normal CF
explain extended select * from t1 where a> 500 and a< 750;
explain extended select * from t1 where a< 750;
explain extended select * from t1 where a> 500;
explain extended select * from t1 where a>=0 and a<=1000;

# a set of 1
explain extended select * from t1 where a>= 500 and a<= 500;
explain extended select * from t1 where b>= 500 and b<= 500;

# two indexes
explain extended select * from t1 where a< 750 and b> 500 and b< 750;

# composite index
drop index ka on t1;
drop index kb on t1;
create index kab on t1(a,b);
set global rocksdb_force_flush_memtable_now = true;
explain extended select * from t1 where a< 750 and b> 500 and b< 750;

# override records in range
set rocksdb_records_in_range=444;
explain extended select * from t1 where a< 750 and b> 500 and b< 750;
set rocksdb_records_in_range=0;

# issue 82
## forward cf
CREATE TABLE `linktable` (
  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `visibility` tinyint(3) NOT NULL DEFAULT '0',
  `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk',
  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'cf_link_id1_type'
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

insert into linktable values (1,1,1,1,1,1,1,1,1);
insert into linktable values (1,1,2,1,1,1,1,1,1);
insert into linktable values (1,1,3,1,1,1,1,1,1);
insert into linktable values (1,1,4,1,1,1,1,1,1);
set global rocksdb_force_flush_memtable_now = true;
explain select id1, id2, link_type, visibility, data, time,  version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2);
drop table linktable;

## rev cf
CREATE TABLE `linktable` (
  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `visibility` tinyint(3) NOT NULL DEFAULT '0',
  `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk',
  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type'
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

#cleanup
DROP TABLE t1;