Merge branch '10.4' into 10.4-mdev16188

1 files changed, 45 insertions, 28 deletions

diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index e2996b963bc..50de308045b 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -1048,30 +1048,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b';
 a	hex(filler)
 a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 drop table t1,t2,t3;
-create table t1 (a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2 (a int, key(a));
-insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
-set @a="select * from t2 force index (a) where a NOT IN(0";
-select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
-count(*)
-1000
-set @a=concat(@a, ')');
-insert into t2 values (11),(13),(15);
-set @b= concat("explain ", @a);
-prepare stmt1 from @b;
-execute stmt1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	NULL	a	5	NULL	1003	Using where; Using index
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-prepare stmt1 from @a;
-execute stmt1;
-a
-11
-13
-15
-drop table t1, t2;
 CREATE TABLE t1 (
 id int NOT NULL DEFAULT '0',
 b int NOT NULL DEFAULT '0',
@@ -1125,7 +1101,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
 item	started	price
 Warnings:
-Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00'
+Warning	1292	Truncated incorrect datetime value: '2005-12-01 24:00:00'
 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
 item	started	price
 A1	2005-11-01 08:00:00	1000.000
@@ -1137,7 +1113,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
 item	started	price
 Warnings:
-Warning	1292	Incorrect datetime value: '2005-12-01 24:00:00'
+Warning	1292	Truncated incorrect datetime value: '2005-12-01 24:00:00'
 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
 item	started	price
 A1	2005-11-01 08:00:00	1000.000
@@ -1587,7 +1563,7 @@ str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
 str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'
 1
 Warnings:
-Warning	1292	Incorrect datetime value: ''
+Warning	1292	Truncated incorrect datetime value: ''
 SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 
 str_to_date('2007-20-00', '%Y-%m-%d') <= '';
 str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 
@@ -1602,7 +1578,7 @@ SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20';
 str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'
 1
 Warnings:
-Warning	1292	Incorrect datetime value: ''
+Warning	1292	Truncated incorrect datetime value: ''
 SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '';
 str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''
 NULL
@@ -3005,5 +2981,46 @@ deallocate prepare stmt;
 set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #
+# MDEV-16934: using system variable eq_range_index_dive_limit
+#             to reduce the number of index dives
+#
+create table t1 (a int, b varchar(31), index idx(a));
+insert into t1 values
+(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'),
+(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff');
+insert into t1 select a+10, concat(b,'zz') from t1;
+insert into t1 select a+15, concat(b,'yy') from t1;
+insert into t1 select a+100, concat(b,'xx') from t1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1;
+rec_per_key
+2
+set eq_range_index_dive_limit=0;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	7	Using index condition
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+1	cc
+9	zzzzz
+15	ffffzz
+set eq_range_index_dive_limit=2;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	10	Using index condition
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+1	cc
+9	zzzzz
+15	ffffzz
+set eq_range_index_dive_limit=default;
+drop table t1;
+#
 # End of 10.2 tests
 #