Fixed bug #19579: at range analysis optimizer did not take into

account predicates that become sargable after reading const tables.
In some cases this resulted in choosing non-optimal execution plans.
Now info of such potentially saragable predicates is saved in
an array and after reading const tables we check whether this
predicates has become saragable.

1 files changed, 49 insertions, 0 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 9c35e1963e6..19492d418ff 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3552,3 +3552,52 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using where
 1	SIMPLE	t2	ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where
 DROP TABLE t1,t2;
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+INSERT INTO t1 VALUES 
+(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+(6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+(21,210), (41,410), (82,820), (83,830), (84,840),
+(65,650), (51,510), (37,370), (94,940), (76,760),
+(22,220), (33,330), (40,400), (95,950), (38,380),
+(67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 
+t3.a=t2.a AND t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si	si	5	NULL	4	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee') ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN 
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si	si	5	NULL	2	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN 
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+DROP TABLE t1,t2,t3;