bzr commit into mysql-6.0-opt branch (timour:2694) Bug#37896

[Timour Katchaounov]

#At file:///home/tkatchaounov/mysql/bzr/6.0-b37896/

 2694 Timour Katchaounov	2008-10-15
      BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
      
      Problem:
      The assertion failure is due to a shortcut in JOIN::optimize where if opt_sum_query
      detects that:
      a) a MIN/MAX function can be computed and substituted by an index lookup, and
      b) the WHERE clause doesn't reference any table in the FROM clause,
      then it does constant substitution, sets JOIN::tables_list = 0, and returns.
      As a result JOIN::optimize never calls setup_subquery_materialization, and never
      creates the correct subselect_engine. JOIN::exec still needs to execute the
      table-independent WHERE clause to check whether there is any result at all.
      This is when the assertion detects this inconsistent state.
      
      
      Solution:
      Call setup_subquery_materialization after the call to opt_sum_query detected that
      (a) and (b) above hold true.
      
      Note:
      This fix makes the behavior the same as that of subquery execution via the
IN=>EXISTS
      transformation, thus the same as that of 5.x. However, as BUG#40037 explains, this
      behavior is incorrect because MIN/MAX should return NULL, if the query result is
      empty. This problem is filed as a separate bug since it is not related to
materialization
      and is present in older versions.
modified:
  sql/item_subselect.cc
  sql/sql_select.cc

per-file messages:
  sql/item_subselect.cc
    The fact that a query has no tables, or all tables were optimized away is expressed
    in one more way, so we add this extra condition.
  sql/sql_select.cc
    Call setup_subquery_materialization() before RETURN-ing prematurely, because if there
    are IN predicates, they still might be executed and need to be setup.
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2008-07-24 10:00:56 +0000
+++ b/sql/item_subselect.cc	2008-10-15 13:49:45 +0000
@@ -1896,7 +1896,11 @@ bool Item_in_subselect::init_left_expr_c
   bool use_result_field= FALSE;
 
   outer_join= unit->outer_select()->join;
-  if (!outer_join || !outer_join->tables)
+  /*
+    An IN predicate might be evaluated in a query for which all tables have
+    been optimzied away.
+  */ 
+  if (!outer_join || !outer_join->tables || !outer_join->tables_list)
     return TRUE;
   /*
     If we use end_[send | write]_group to handle complete rows of the outer

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-09-13 14:16:20 +0000
+++ b/sql/sql_select.cc	2008-10-15 13:49:45 +0000
@@ -1555,6 +1555,9 @@ JOIN::optimize()
                                  QT_ORDINARY););
         conds= table_independent_conds;
       }
+      /* Create all structures needed for materialized subquery execution. */
+      if (setup_subquery_materialization())
+        DBUG_RETURN(1);
     }
   }
   if (!tables_list)