BUG#919878: Assertion `!eliminated_tables...

- In MySQL 5.5, print_join() was re-worked to print "FROM dual" when all
  tables are constant. This change didn't work together with table 
  elimination.

3 files changed, 62 insertions, 2 deletions

diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 9b2656c17e4..69cd9792299 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -588,4 +588,23 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using index
 drop view v1;
 DROP TABLE t1,t2,t3;
+#
+# BUG#919878: Assertion `!eliminated_tables...
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2
+( b INT, UNIQUE INDEX(b) );
+INSERT INTO t2 VALUES (1),(2);
+EXPLAIN EXTENDED
+SELECT * FROM t2
+WHERE b IN (
+SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	index	NULL	b	5	NULL	2	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual where 1 having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index dc32618eb8c..26b98c9023b 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -521,4 +521,22 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 drop view v1;
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # BUG#919878: Assertion `!eliminated_tables...
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2
+  ( b INT, UNIQUE INDEX(b) );
+INSERT INTO t2 VALUES (1),(2);
+
+EXPLAIN EXTENDED
+  SELECT * FROM t2
+  WHERE b IN (
+    SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
+  );
+
+DROP TABLE t1,t2;
+
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5e483c2d359..9a085ec6314 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21442,6 +21442,8 @@ static void print_table_array(THD *thd,
          (curr->nested_join && !(curr->nested_join->used_tables &
                                 ~eliminated_tables))))
     {
+      /* as of 5.5, print_join doesnt put eliminated elements into array */
+      DBUG_ASSERT(0); 
       continue;
     }
 
@@ -21467,6 +21469,21 @@ static void print_table_array(THD *thd,
 }
 
 
+/*
+  Check if the passed table is 
+   - a base table which was eliminated, or
+   - a join nest which only contained eliminated tables (and so was eliminated,
+     too)
+*/
+
+static bool is_eliminated_table(table_map eliminated_tables, TABLE_LIST *tbl)
+{
+  return eliminated_tables &&
+    ((tbl->table && (tbl->table->map & eliminated_tables)) ||
+     (tbl->nested_join && !(tbl->nested_join->used_tables &
+                            ~eliminated_tables)));
+}
+
 /**
   Print joins from the FROM clause.
 
@@ -21488,8 +21505,14 @@ static void print_join(THD *thd,
   uint non_const_tables= 0;
 
   for (TABLE_LIST *t= ti++; t ; t= ti++)
-    if (!t->optimized_away)
+  {
+    /* 
+      See comment in print_table_array() about the second part of the
+      condition 
+    */
+    if (!t->optimized_away && !is_eliminated_table(eliminated_tables, t))
       non_const_tables++;
+  }
   if (!non_const_tables)
   {
     str->append(STRING_WITH_LEN("dual"));
@@ -21504,7 +21527,7 @@ static void print_join(THD *thd,
   TABLE_LIST *tmp, **t= table + (non_const_tables - 1);
   while ((tmp= ti++))
   {
-    if (tmp->optimized_away)
+    if (tmp->optimized_away || is_eliminated_table(eliminated_tables, tmp))
       continue;
     *t--= tmp;
   }