1# 2# Range optimizer (and related) tests that need InnoDB. 3# 4drop table if exists t0, t1, t2; 5# 6# MDEV-6735: Range checked for each record used with key 7# 8create table t0(a int); 9insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 10create table t1(a int); 11insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000 12from t0 A, t0 B, t0 C, t0 D; 13create table t2 ( 14a int, 15b int, 16filler1 char(100), 17filler2 char(100), 18filler3 char(100), 19filler4 char(100), 20key(a), 21key(b) 22) engine=innodb; 23insert into t2 24select 25a,a, 26repeat('0123456789', 10), 27repeat('0123456789', 10), 28repeat('0123456789', 10), 29repeat('0123456789', 10) 30from t1; 31analyze table t2; 32Table Op Msg_type Msg_text 33test.t2 analyze status Engine-independent statistics collected 34test.t2 analyze status OK 35# The following must not use "Range checked for each record": 36explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250; 37id select_type table type possible_keys key key_len ref rows Extra 381 SIMPLE t0 ALL NULL NULL NULL NULL 10 391 SIMPLE t2 range a,b b 5 NULL 201 Using where; Using join buffer (flat, BNL join) 40drop table t0,t1,t2; 41# 42# MDEV-10466: constructing an invalid SEL_ARG 43# 44create table t1 ( 45pk int, a int, b int, 46primary key (pk), index idx1(b), index idx2(b) 47) engine=innodb; 48Warnings: 49Note 1831 Duplicate index `idx2`. This is deprecated and will be disallowed in a future release 50insert into t1 values (1,6,0),(2,1,0),(3,5,2),(4,8,0); 51create table t2 (c int) engine=innodb; 52insert into t2 values (1),(2); 53create table t3 (d int) engine=innodb; 54insert into t3 values (3),(-1),(4); 55set @save_optimizer_switch=@@optimizer_switch; 56set optimizer_switch='extended_keys=on'; 57explain 58select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; 59id select_type table type possible_keys key key_len ref rows Extra 601 SIMPLE t2 ALL NULL NULL NULL NULL 2 611 SIMPLE t1 ref PRIMARY,idx1,idx2 idx1 5 const 3 Using index condition 621 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 63select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; 64pk a b 651 6 0 66set optimizer_switch=@save_optimizer_switch; 67drop table t1,t2,t3; 68CREATE TABLE t1 ( 69pk INT PRIMARY KEY, f1 INT, f2 CHAR(1), f3 CHAR(1), 70KEY(f1), KEY(f2) 71) ENGINE=InnoDB; 72INSERT INTO t1 VALUES 73(1,4,'v',NULL),(2,6,'v',NULL),(3,7,'c',NULL),(4,1,'e',NULL),(5,0,'x',NULL), 74(6,7,'i',NULL),(7,7,'e',NULL),(8,1,'p',NULL),(9,7,'s',NULL),(10,1,'j',NULL), 75(11,5,'z',NULL),(12,2,'c',NULL),(13,0,'a',NULL),(14,1,'q',NULL),(15,8,'y',NULL), 76(16,1,'m',NULL),(17,1,'r',NULL),(18,9,'v',NULL),(19,1,'n',NULL); 77CREATE TABLE t2 (f4 INT, f5 CHAR(1)) ENGINE=InnoDB; 78INSERT INTO t2 VALUES (4,'q'),(NULL,'j'); 79SELECT * FROM t1 AS t1_1, t1 AS t1_2, t2 80WHERE f5 = t1_2.f2 AND ( t1_1.f1 = 103 AND t1_1.f2 = 'o' OR t1_1.pk < f4 ); 81pk f1 f2 f3 pk f1 f2 f3 f4 f5 821 4 v NULL 14 1 q NULL 4 q 832 6 v NULL 14 1 q NULL 4 q 843 7 c NULL 14 1 q NULL 4 q 85drop table t1,t2; 86# 87# MDEV-14440: Server crash in in handler::ha_external_lock or Assertion `inited==RND' 88# failed in handler::ha_rnd_end upon SELECT from partitioned table 89# 90set @optimizer_switch_save= @@optimizer_switch; 91set optimizer_switch='index_merge_sort_intersection=off'; 92create table t0 (a int)engine=innodb; 93insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 94create table t1 ( 95a int, b int, c int, 96key(a),key(b),key(c) 97)engine=innodb; 98insert into t1 99select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a 100from t0 A, t0 B, t0 C, t0 D where D.a<5; 101SET @saved_dbug = @@GLOBAL.debug_dbug; 102set @@global.debug_dbug="+d,ha_index_init_fail"; 103explain select * from t1 where a=10 and b=10; 104id select_type table type possible_keys key key_len ref rows Extra 1051 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 106select * from t1 where a=10 and b=10; 107ERROR HY000: Table definition has changed, please retry transaction 108DROP TABLE t0,t1; 109SET @@GLOBAL.debug_dbug = @saved_dbug; 110set @@optimizer_switch= @optimizer_switch_save; 111# End of 10.1 tests 112# 113# MDEV-27262: Index intersection with full scan over an index 114# 115CREATE TABLE t1 ( 116id int(10) unsigned NOT NULL AUTO_INCREMENT, 117p char(32) DEFAULT NULL, 118es tinyint(3) unsigned NOT NULL DEFAULT 0, 119er tinyint(3) unsigned NOT NULL DEFAULT 0, 120x mediumint(8) unsigned NOT NULL DEFAULT 0, 121PRIMARY KEY (id), 122INDEX es (es), 123INDEX x (x), 124INDEX er (er,x), 125INDEX p (p) 126) ENGINE=InnoDB DEFAULT CHARSET=latin1; 127insert into t1(es,er) select 0, 1 from seq_1_to_45; 128insert into t1(es,er) select 0, 2 from seq_1_to_49; 129insert into t1(es,er) select 0, 3 from seq_1_to_951; 130insert into t1(es,er) select 0, 3 from seq_1_to_1054; 131insert into t1(es,er) select 0, 6 from seq_1_to_25; 132insert into t1(es,er) select 0, 11 from seq_1_to_1; 133insert into t1(es,er) select 1, 1 from seq_1_to_45; 134insert into t1(es,er) select 1, 2 from seq_1_to_16; 135insert into t1(es,er) select 1, 3 from seq_1_to_511; 136insert into t1(es,er) select 1, 4 from seq_1_to_687; 137insert into t1(es,er) select 1, 6 from seq_1_to_50; 138insert into t1(es,er) select 1, 7 from seq_1_to_4; 139insert into t1(es,er) select 1, 11 from seq_1_to_1; 140insert into t1(es,er) select 2, 1 from seq_1_to_82; 141insert into t1(es,er) select 2, 2 from seq_1_to_82; 142insert into t1(es,er) select 2, 3 from seq_1_to_1626; 143insert into t1(es,er) select 2, 4 from seq_1_to_977; 144insert into t1(es,er) select 2, 6 from seq_1_to_33; 145insert into t1(es,er) select 2, 11 from seq_1_to_1; 146insert into t1(es,er) select 3, 1 from seq_1_to_245; 147insert into t1(es,er) select 3, 2 from seq_1_to_81; 148insert into t1(es,er) select 3, 3 from seq_1_to_852; 149insert into t1(es,er) select 3, 4 from seq_1_to_2243; 150insert into t1(es,er) select 3, 6 from seq_1_to_44; 151insert into t1(es,er) select 3, 11 from seq_1_to_1; 152insert into t1(es,er) select 4, 1 from seq_1_to_91; 153insert into t1(es,er) select 4, 2 from seq_1_to_83; 154insert into t1(es,er) select 4, 3 from seq_1_to_297; 155insert into t1(es,er) select 4, 4 from seq_1_to_2456; 156insert into t1(es,er) select 4, 6 from seq_1_to_19; 157insert into t1(es,er) select 4, 11 from seq_1_to_1; 158update t1 set p='foobar'; 159update t1 set x=0; 160set @save_isp=@@innodb_stats_persistent; 161set global innodb_stats_persistent= 1; 162analyze table t1; 163Table Op Msg_type Msg_text 164test.t1 analyze status Engine-independent statistics collected 165test.t1 analyze status OK 166set optimizer_switch='index_merge_sort_intersection=on'; 167SELECT * FROM t1 168WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; 169id p es er x 17014645 foobar 4 4 0 17114646 foobar 4 4 0 172EXPLAIN EXTENDED SELECT * FROM t1 173WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; 174id select_type table type possible_keys key key_len ref rows filtered Extra 1751 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where 176Warnings: 177Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 178set optimizer_switch='index_merge_sort_intersection=off'; 179SELECT * FROM t1 180WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; 181id p es er x 18214645 foobar 4 4 0 18314646 foobar 4 4 0 184EXPLAIN EXTENDED SELECT * FROM t1 185WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; 186id select_type table type possible_keys key key_len ref rows filtered Extra 1871 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where 188Warnings: 189Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 190set optimizer_switch='index_merge_sort_intersection=on'; 191SELECT * FROM t1 192WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; 193id p es er x 19414007 foobar 4 2 0 19514008 foobar 4 2 0 196EXPLAIN EXTENDED SELECT * FROM t1 197WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; 198id select_type table type possible_keys key key_len ref rows filtered Extra 1991 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where 200Warnings: 201Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 202set optimizer_switch='index_merge_sort_intersection=off'; 203SELECT * FROM t1 204WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; 205id p es er x 20614007 foobar 4 2 0 20714008 foobar 4 2 0 208EXPLAIN EXTENDED SELECT * FROM t1 209WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; 210id select_type table type possible_keys key key_len ref rows filtered Extra 2111 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where 212Warnings: 213Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 214set optimizer_switch='index_merge_sort_intersection=default'; 215set global innodb_stats_persistent= @save_isp; 216DROP TABLE t1; 217# End of 10.2 tests 218# 219# MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, 220# [Warning] InnoDB: Using a partial-field key prefix in search 221# 222CREATE TABLE t1 ( 223pk INT, 224a VARCHAR(1), 225b INT, 226PRIMARY KEY (pk), 227KEY (a,b) 228) ENGINE=InnoDB; 229INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); 230explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t1 range PRIMARY,a PRIMARY 4 NULL 1 Using where 233SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; 234a 235drop table t1; 236# End of 10.4 tests 237