1# Verify that index and range scans are not slow
2# on tables during create select statements
3# due to tokudb bulk fetch not being used
4
5source include/have_tokudb.inc;
6source include/have_partition.inc;
7source include/big_test.inc;
8set default_storage_engine='tokudb';
9disable_warnings;
10drop table if exists t1,t2;
11enable_warnings;
12
13let $debug = 0;
14
15# Create source hash partitioned table from source table t
16CREATE TABLE `t1` (
17  `num` int(10) unsigned auto_increment NOT NULL,
18  `val` varchar(32) DEFAULT NULL,
19  PRIMARY KEY (`num`)
20) PARTITION BY HASH (num) PARTITIONS 8;
21
22# put 1M rows into t1
23INSERT INTO t1 values (null,null);
24INSERT INTO t1 SELECT null,val FROM t1;
25INSERT INTO t1 SELECT null,val FROM t1;
26INSERT INTO t1 SELECT null,val FROM t1;
27INSERT INTO t1 SELECT null,val FROM t1;
28INSERT INTO t1 SELECT null,val FROM t1;
29INSERT INTO t1 SELECT null,val FROM t1;
30INSERT INTO t1 SELECT null,val FROM t1;
31INSERT INTO t1 SELECT null,val FROM t1;
32INSERT INTO t1 SELECT null,val FROM t1;
33INSERT INTO t1 SELECT null,val FROM t1;
34INSERT INTO t1 SELECT null,val FROM t1;
35INSERT INTO t1 SELECT null,val FROM t1;
36INSERT INTO t1 SELECT null,val FROM t1;
37INSERT INTO t1 SELECT null,val FROM t1;
38INSERT INTO t1 SELECT null,val FROM t1;
39INSERT INTO t1 SELECT null,val FROM t1;
40INSERT INTO t1 SELECT null,val FROM t1;
41INSERT INTO t1 SELECT null,val FROM t1;
42INSERT INTO t1 SELECT null,val FROM t1;
43INSERT INTO t1 SELECT null,val FROM t1;
44SELECT count(*) FROM t1;
45
46let $maxq = 20;
47
48set tokudb_bulk_fetch=ON;
49let $s = `select unix_timestamp()`;
50let $i = 0;
51while ($i < $maxq) {
52    CREATE TABLE t2 AS SELECT count(*) from t1;
53    DROP TABLE t2;
54    inc $i;
55}
56let $time_elapsed_on = `select unix_timestamp() - $s`;
57
58set tokudb_bulk_fetch=OFF;
59let $s = `select unix_timestamp()`;
60let $i = 0;
61while ($i < $maxq) {
62    CREATE TABLE t2 AS SELECT count(*) from t1;
63    DROP TABLE t2;
64    inc $i;
65}
66let $time_elapsed_off = `select unix_timestamp() - $s`;
67
68# check that bulk fetch on is greater than bulk fetch off
69let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`;
70echo $verdict;
71if ($debug) { echo index $verdict $time_elapsed_off $time_elapsed_on; }
72if (!$verdict) { echo index $time_elapsed_off $time_elapsed_on; }
73
74let $maxq = 20;
75
76set tokudb_bulk_fetch=ON;
77let $s = `select unix_timestamp()`;
78let $i = 0;
79while ($i < $maxq) {
80    CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000;
81    DROP TABLE t2;
82    inc $i;
83}
84let $time_elapsed_on = `select unix_timestamp() - $s`;
85
86set tokudb_bulk_fetch=OFF;
87let $s = `select unix_timestamp()`;
88let $i = 0;
89while ($i < $maxq) {
90    CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000;
91    DROP TABLE t2;
92    inc $i;
93}
94let $time_elapsed_off = `select unix_timestamp() - $s`;
95
96# check that bulk fetch on is greater than bulk fetch off
97let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`;
98echo $verdict;
99if ($debug) { echo range $verdict $time_elapsed_off $time_elapsed_on; }
100if (!$verdict) { echo range $time_elapsed_off $time_elapsed_on; }
101
102drop table t1;
103