Fixed bug #21390: wrong estimate of rows after elimination of

const tables. This resulted in choosing extremely inefficient
execution plans in same cases when distribution of data in
joined were skewed (see the customer test case for the bug).

2 files changed, 81 insertions, 2 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index b5d059be4c5..0c62d3f570f 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2328,9 +2328,9 @@ explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t4	const	id4	NULL	NULL	NULL	1	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t4	ALL	id4	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id1	id2	id3	id4	id44
@@ -3479,3 +3479,41 @@ Warning	1466	Leading spaces are removed from name ' a '
 execute stmt;
 a 
 1
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+(1), (1), (1), (1), (1), (1), (1), (1),
+(2), (2), (2), (2),
+(3), (3),
+(4);
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+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	ref	idx	idx	4	const	7	Using index
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+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	ref	idx	idx	4	const	1	Using index
+DROP TABLE t1, t2;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+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	const	idx1	NULL	NULL	NULL	1	
+1	SIMPLE	t3	ref	idx1	idx1	5	const	3	Using where
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+id	a	b	c	d	e
+2	NULL	NULL	NULL	2	10
+2	NULL	NULL	NULL	2	20
+2	NULL	NULL	NULL	2	40
+2	NULL	NULL	NULL	2	50
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 197d89d02d5..36b3749b4d7 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2957,3 +2957,44 @@ SELECT 0.9888889889 * 1.011111411911;
 #
 prepare stmt from 'select 1 as " a "';
 execute stmt;
+
+#
+# Bug #21390: wrong estimate of rows after elimination of const tables
+#
+
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+  (1), (1), (1), (1), (1), (1), (1), (1),
+  (2), (2), (2), (2),
+  (3), (3),
+  (4);
+
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+
+DROP TABLE t1, t2;
+
+#
+# No matches for a join after substitution of a const table
+#
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+
+
+DROP TABLE t1,t2,t3;