BUG#675118: Elimination of a table results in an invalid execution plan

- Fix for MySQL BUG#52357 added NESTED_JOIN::is_fully_covered() which would 
  not take into account that MariaDB's table elimination could eliminate tables
  from join plan (and so, from join nest).
  Fixed the check in the function to compare post-table-elimination numbers.

1 files changed, 31 insertions, 0 deletions

diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 395b0be7e34..6c713946b27 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -535,3 +535,34 @@ HAVING
 field4 != 6;
 field1	field2	field3	field4	field5	field6
 drop table t0,t1,t2,t3,t4,t5,t6;
+#
+# BUG#675118: Elimination of a table results in an invalid execution plan
+#
+CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;
+CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
+Warnings:
+Warning	1071	Specified key was too long; max key length is 1000 bytes
+INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'),
+('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'),
+('hczkfqjeggivdvac'),('e'),('okay'),('up');
+CREATE TABLE t3 (f4 varchar(1024), f1 int(11), f2 int(11)) ;
+INSERT IGNORE INTO t3 VALUES ('f','4','0'),('n','5','-996540416');
+CREATE TABLE t4 (f1 int(11), f3 varchar(10)) ;
+INSERT IGNORE INTO t4 VALUES ('8','n'),('9','nwzcerzsgx'),('10','c');
+CREATE TABLE t5 (f5 int(11), KEY (f5)) ;
+EXPLAIN 
+SELECT t3.f2
+FROM t2
+LEFT JOIN t3
+LEFT JOIN t4
+LEFT JOIN t1 ON t4.f1 = t1.f1
+JOIN t5 ON t4.f3 ON t3.f1 = t5.f5 ON t2.f4 = t3.f4
+WHERE t3.f2 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t5	ref	f5	f5	5	test.t3.f1	2	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	f4	NULL	NULL	NULL	11	Using where; Using join buffer
+# ^^ The above must not produce a QEP of t3,t5,t2,t4
+#    as that violates the "no interleaving of outer join nests" rule.
+DROP TABLE t1,t2,t3,t4,t5;