From: Evgeny Potemkin
Date: October 7 2008 7:45pm
Subject: bzr commit into mysql-6.0 branch (epotemkin:2853) Bug#37899
List-Archive: http://lists.mysql.com/commits/55623
X-Bug: 37899
Message-Id: <20081007194520.8BADD5552D@moonbone.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///work/bzr_trees/37870-bug-6.0-bugteam/

 2853 Evgeny Potemkin	2008-10-07
      Bug#37899: Wrongly checked optimization prerequisite caused failed assertion.
      
      One of the prerequisites for subquery optimizations is that the subquery
      predicate should be a top-level item or under a top-level AND item in the
      WHERE expression. XOR function was wrongly allowed for top-level function for
      the subquery optimization thus causing an internal error and failed assertion
      later.
      
      The Item_cond::fix_fields function now allow only the AND function as a 
      top-level item for subquery optimization.
      Added an assertion at the end of the replace_where_subcondition function
      to catch such errors.
modified:
  mysql-test/r/subselect_sj.result
  mysql-test/t/subselect_sj.test
  sql/item_cmpfunc.cc
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect_sj.result
    Added a test case for the bug#37899.
  mysql-test/t/subselect_sj.test
    Added a test case for the bug#37899.
  sql/item_cmpfunc.cc
    Bug#37899: Wrongly checked optimization prerequisite caused failed assertion.
    The Item_cond::fix_fields function now allow only the AND function as a 
    top-level item for subquery optimization.
  sql/sql_select.cc
    Bug#37899: Wrongly checked optimization prerequisite caused failed assertion.
    Added an assertion at the end of the replace_where_subcondition function
    to catch such errors.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2008-06-11 23:16:53 +0000
+++ b/mysql-test/r/subselect_sj.result	2008-10-07 19:45:09 +0000
@@ -238,3 +238,26 @@ no_materialization,no_loosescan
 set optimizer_switch='';
 drop table t0, t1;
 drop table t10, t11, t12;
+
+Bug#37899: Wrongly checked optimization prerequisite caused failed
+assertion.
+
+CREATE TABLE t1 (
+`pk` int(11),
+`varchar_nokey` varchar(5)
+);
+INSERT INTO t1 VALUES
+(1,'qk'),(2,'j'),(3,'aew');
+SELECT *
+FROM t1
+WHERE varchar_nokey IN (
+SELECT
+varchar_nokey
+FROM
+t1
+) XOR pk = 30;
+pk	varchar_nokey
+1	qk
+2	j
+3	aew
+drop table t1;

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2008-06-11 23:16:53 +0000
+++ b/mysql-test/t/subselect_sj.test	2008-10-07 19:45:09 +0000
@@ -125,3 +125,25 @@ set optimizer_switch='';
 
 drop table t0, t1;
 drop table t10, t11, t12;
+
+--echo
+--echo Bug#37899: Wrongly checked optimization prerequisite caused failed
+--echo            assertion.
+--echo
+CREATE TABLE t1 (
+  `pk` int(11),
+  `varchar_nokey` varchar(5)
+);
+
+INSERT INTO t1 VALUES
+(1,'qk'),(2,'j'),(3,'aew');
+
+SELECT *
+FROM t1
+WHERE varchar_nokey IN (
+ SELECT
+ varchar_nokey
+ FROM
+ t1
+) XOR pk = 30;
+drop table t1;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2008-09-09 15:55:45 +0000
+++ b/sql/item_cmpfunc.cc	2008-10-07 19:45:09 +0000
@@ -3926,7 +3926,7 @@ Item_cond::fix_fields(THD *thd, Item **r
   not_null_tables_cache= used_tables_cache= 0;
   const_item_cache= 1;
 
-  if (functype() == COND_OR_FUNC)
+  if (functype() != COND_AND_FUNC)
     thd->thd_marker.emb_on_expr_nest= NULL;
   /*
     and_table_cache is the value that Item_cond_or() returns for

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-09-04 18:30:34 +0000
+++ b/sql/sql_select.cc	2008-10-07 19:45:09 +0000
@@ -15263,7 +15263,8 @@ static bool replace_where_subcondition(J
       }
     }
   }
-
+  // If we came here it means there were an error during prerequisites check.
+  DBUG_ASSERT(0);
   return TRUE;
 }