# # Range optimizer (and related) tests that need InnoDB. # # # MDEV-6735: Range checked for each record used with key # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 ( a int, b int, filler1 char(100), filler2 char(100), filler3 char(100), filler4 char(100), key(a), key(b) ) engine=innodb; insert into t2 select seq,seq, repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10) from seq_0_to_9999; analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK # The following must not use "Range checked for each record": explain select * from t0 left join t2 on t2.a = d and pk < c and b = '0'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY,idx1,idx2 idx1 5 const # 1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (incremental, BNL join) select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0'; pk a b 1 6 0 set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; CREATE TABLE t1 ( pk INT PRIMARY KEY, f1 INT, f2 CHAR(1), f3 CHAR(1), KEY(f1), KEY(f2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,4,'v',NULL),(2,6,'v',NULL),(3,7,'c',NULL),(4,1,'e',NULL),(5,0,'x',NULL), (6,7,'i',NULL),(7,7,'e',NULL),(8,1,'p',NULL),(9,7,'s',NULL),(10,1,'j',NULL), (11,5,'z',NULL),(12,2,'c',NULL),(13,0,'a',NULL),(14,1,'q',NULL),(15,8,'y',NULL), (16,1,'m',NULL),(17,1,'r',NULL),(18,9,'v',NULL),(19,1,'n',NULL); CREATE TABLE t2 (f4 INT, f5 CHAR(1)) ENGINE=InnoDB; INSERT INTO t2 VALUES (4,'q'),(NULL,'j'); SELECT * FROM t1 AS t1_1, t1 AS t1_2, t2 WHERE f5 = t1_2.f2 AND ( t1_1.f1 = 103 AND t1_1.f2 = 'o' OR t1_1.pk < f4 ); pk f1 f2 f3 pk f1 f2 f3 f4 f5 1 4 v NULL 14 1 q NULL 4 q 2 6 v NULL 14 1 q NULL 4 q 3 7 c NULL 14 1 q NULL 4 q drop table t1,t2; # # MDEV-14440: Server crash in in handler::ha_external_lock or Assertion `inited==RND' # failed in handler::ha_rnd_end upon SELECT from partitioned table # call mtr.add_suppression("Got error .* when reading table"); set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( a int, b int, c int, key(a),key(b),key(c) )engine=innodb; insert into t1 select a.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b; SET @saved_dbug = @@GLOBAL.debug_dbug; set @@global.debug_dbug="+d,ha_index_init_fail"; explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref|filter a,b a|b 5|5 const 50 (2%) Using where; Using rowid filter select * from t1 where a=10 and b=10; ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t0,t1; SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; # End of 10.1 tests # # MDEV-27262: Index intersection with full scan over an index # CREATE TABLE t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, p char(32) DEFAULT NULL, es tinyint(3) unsigned NOT NULL DEFAULT 0, er tinyint(3) unsigned NOT NULL DEFAULT 0, x mediumint(8) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX es (es), INDEX x (x), INDEX er (er,x), INDEX p (p) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t1(es,er) select 0, 1 from seq_1_to_45; insert into t1(es,er) select 0, 2 from seq_1_to_49; insert into t1(es,er) select 0, 3 from seq_1_to_951; insert into t1(es,er) select 0, 3 from seq_1_to_1054; insert into t1(es,er) select 0, 6 from seq_1_to_25; insert into t1(es,er) select 0, 11 from seq_1_to_1; insert into t1(es,er) select 1, 1 from seq_1_to_45; insert into t1(es,er) select 1, 2 from seq_1_to_16; insert into t1(es,er) select 1, 3 from seq_1_to_511; insert into t1(es,er) select 1, 4 from seq_1_to_687; insert into t1(es,er) select 1, 6 from seq_1_to_50; insert into t1(es,er) select 1, 7 from seq_1_to_4; insert into t1(es,er) select 1, 11 from seq_1_to_1; insert into t1(es,er) select 2, 1 from seq_1_to_82; insert into t1(es,er) select 2, 2 from seq_1_to_82; insert into t1(es,er) select 2, 3 from seq_1_to_1626; insert into t1(es,er) select 2, 4 from seq_1_to_977; insert into t1(es,er) select 2, 6 from seq_1_to_33; insert into t1(es,er) select 2, 11 from seq_1_to_1; insert into t1(es,er) select 3, 1 from seq_1_to_245; insert into t1(es,er) select 3, 2 from seq_1_to_81; insert into t1(es,er) select 3, 3 from seq_1_to_852; insert into t1(es,er) select 3, 4 from seq_1_to_2243; insert into t1(es,er) select 3, 6 from seq_1_to_44; insert into t1(es,er) select 3, 11 from seq_1_to_1; insert into t1(es,er) select 4, 1 from seq_1_to_91; insert into t1(es,er) select 4, 2 from seq_1_to_83; insert into t1(es,er) select 4, 3 from seq_1_to_297; insert into t1(es,er) select 4, 4 from seq_1_to_2456; insert into t1(es,er) select 4, 6 from seq_1_to_19; insert into t1(es,er) select 4, 11 from seq_1_to_1; update t1 set p='foobar'; update t1 set x=0; set @save_isp=@@innodb_stats_persistent; set global innodb_stats_persistent= 1; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK set optimizer_switch='index_merge_sort_intersection=on'; SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; id p es er x 14645 foobar 4 4 0 14646 foobar 4 4 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where Warnings: Note 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 set optimizer_switch='index_merge_sort_intersection=off'; SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; id p es er x 14645 foobar 4 4 0 14646 foobar 4 4 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where Warnings: Note 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 set optimizer_switch='index_merge_sort_intersection=on'; SELECT * FROM t1 WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; id p es er x 14007 foobar 4 2 0 14008 foobar 4 2 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where Warnings: Note 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 set optimizer_switch='index_merge_sort_intersection=off'; SELECT * FROM t1 WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; id p es er x 14007 foobar 4 2 0 14008 foobar 4 2 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where Warnings: Note 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 set optimizer_switch='index_merge_sort_intersection=default'; set global innodb_stats_persistent= @save_isp; DROP TABLE t1; # End of 10.2 tests # # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, # [Warning] InnoDB: Using a partial-field key prefix in search # CREATE TABLE t1 ( pk INT, a VARCHAR(1), b INT, PRIMARY KEY (pk), KEY (a,b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,a PRIMARY 4 NULL 1 Using where SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; a drop table t1; # End of 10.4 tests