[Commits] Rev 3168: BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING in file:///home/psergey/dev2/5.3-push18/

Sergey Petrunya psergey at askmonty.org
Mon Aug 29 18:57:43 EEST 2011


At file:///home/psergey/dev2/5.3-push18/

------------------------------------------------------------
revno: 3168
revision-id: psergey at askmonty.org-20110829155741-lt1xkq1wunwokczb
parent: timour at askmonty.org-20110826214029-vh6gj92pa6km7fr1
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-push18
timestamp: Mon 2011-08-29 19:57:41 +0400
message:
  BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING
  - The problem was that the code that made the check whether the subquery is an AND-part of the WHERE 
    clause didn't work correctly for nested subqueries. In particular, grand-child subquery in HAVING was 
    treated as if it was in the WHERE, which eventually caused an assert when replace_where_subcondition
    looked for the subquery predicate in the WHERE and couldn't find it there.
  
  - The fix: Removed implementation of "thd_marker approach". thd->thd_marker was used to determine the 
    location of subquery predicate: setup_conds() would set accordingly it when making the 
  
      {where|on_expr}->fix_fields(...)
  
    call so that AND-parts of the WHERE/ON clauses can determine they are the AND-parts. 
    Item_cond_or::fix_fields(), Item_func::fix_fields(), Item_subselect::fix_fields (this one was missed),
    and all other items-that-contain-items had to reset thd->thd_marker before calling fix_fields() for 
    their children items, so that the children can see they are not AND-parts of WHERE/ON.
  - The "thd_marker approach" required that a lot of code in different locations maintains correct value of
    thd->thd_marker, so it was replaced with:
  - The new approach with mark_as_condition_AND_part does not keep context in thd->thd_marker. Instead, 
    setup_conds() now calls
  
      {where|on_expr}->mark_as_condition_AND_part()
  
    and implementations of that function make sure that: 
     - parts of AND-expressions get the mark_as_condition_AND_part() call
     - Item_in_subselect objects record that they are AND-parts of WHERE/ON
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2011-08-16 17:42:25 +0000
+++ b/mysql-test/r/subselect_sj.result	2011-08-29 15:57:41 +0000
@@ -1729,4 +1729,24 @@
 2	7
 5	6
 DROP TABLE t1, t2, t3;
+#
+# BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING
+#
+CREATE TABLE t1 ( d int );
+INSERT INTO t1 VALUES (2),(2),(0),(2),(2);
+CREATE TABLE t2 ( b int );
+INSERT INTO t2 VALUES (4),(3),(3);
+CREATE TABLE t3 ( a int );
+SELECT *
+FROM t3
+WHERE (t3.a) IN (
+SELECT t1.d
+FROM t1
+HAVING ( 4 ) IN (
+SELECT t2.b
+FROM t2
+)
+);
+a
+drop table t1, t2,t3;
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2011-08-16 17:42:25 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2011-08-29 15:57:41 +0000
@@ -1740,6 +1740,26 @@
 2	7
 5	6
 DROP TABLE t1, t2, t3;
+#
+# BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING
+#
+CREATE TABLE t1 ( d int );
+INSERT INTO t1 VALUES (2),(2),(0),(2),(2);
+CREATE TABLE t2 ( b int );
+INSERT INTO t2 VALUES (4),(3),(3);
+CREATE TABLE t3 ( a int );
+SELECT *
+FROM t3
+WHERE (t3.a) IN (
+SELECT t1.d
+FROM t1
+HAVING ( 4 ) IN (
+SELECT t2.b
+FROM t2
+)
+);
+a
+drop table t1, t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2011-08-16 17:42:25 +0000
+++ b/mysql-test/t/subselect_sj.test	2011-08-29 15:57:41 +0000
@@ -1571,5 +1571,28 @@
 
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # BUG#834534: Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING
+--echo #
+CREATE TABLE t1 ( d int );
+INSERT INTO t1 VALUES (2),(2),(0),(2),(2);
+
+CREATE TABLE t2 ( b int );
+INSERT INTO t2 VALUES (4),(3),(3);
+
+CREATE TABLE t3 ( a int );
+
+SELECT *
+FROM t3
+WHERE (t3.a) IN (
+        SELECT t1.d
+        FROM t1
+        HAVING ( 4 ) IN (
+                SELECT t2.b
+                FROM t2
+        )
+);
+drop table t1, t2,t3; 
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/item.h'
--- a/sql/item.h	2011-08-12 03:24:32 +0000
+++ b/sql/item.h	2011-08-29 15:57:41 +0000
@@ -1292,6 +1292,8 @@
     be defined for Item_func.
   */
   virtual void get_cache_parameters(List<Item> &parameters) { };
+
+  virtual void mark_as_condition_AND_part(TABLE_LIST *embedding) {};
 };
 
 

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-08-23 12:39:15 +0000
+++ b/sql/item_cmpfunc.cc	2011-08-29 15:57:41 +0000
@@ -4076,15 +4076,12 @@
   DBUG_ASSERT(fixed == 0);
   List_iterator<Item> li(list);
   Item *item;
-  TABLE_LIST *save_emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
 #ifndef EMBEDDED_LIBRARY
   uchar buff[sizeof(char*)];			// Max local vars in function
 #endif
   not_null_tables_cache= used_tables_cache= 0;
   const_item_cache= 1;
 
-  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
     not_null_tables()
@@ -4144,7 +4141,6 @@
       maybe_null=1;
   }
   thd->lex->current_select->cond_count+= list.elements;
-  thd->thd_marker.emb_on_expr_nest= save_emb_on_expr_nest;
   fix_length_and_dec();
   fixed= 1;
   return FALSE;
@@ -4414,6 +4410,17 @@
 }
 
 
+void Item_cond_and::mark_as_condition_AND_part(TABLE_LIST *embedding)
+{
+  List_iterator<Item> li(list);
+  Item *item;
+  while ((item=li++))
+  {
+    item->mark_as_condition_AND_part(embedding);
+  }
+}
+
+
 /**
   Evaluation of AND(expr, expr, expr ...).
 

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2011-08-17 11:10:32 +0000
+++ b/sql/item_cmpfunc.h	2011-08-29 15:57:41 +0000
@@ -1816,6 +1816,7 @@
     return item;
   }
   Item *neg_transformer(THD *thd);
+  void mark_as_condition_AND_part(TABLE_LIST *embedding);
 };
 
 inline bool is_cond_and(Item *item)

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2011-07-11 00:19:45 +0000
+++ b/sql/item_func.cc	2011-08-29 15:57:41 +0000
@@ -152,11 +152,9 @@
 {
   DBUG_ASSERT(fixed == 0);
   Item **arg,**arg_end;
-  TABLE_LIST *save_emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
 #ifndef EMBEDDED_LIBRARY			// Avoid compiler warning
   uchar buff[STACK_BUFF_ALLOC];			// Max argument in function
 #endif
-  thd->thd_marker.emb_on_expr_nest= NULL;
 
   used_tables_cache= not_null_tables_cache= 0;
   const_item_cache=1;
@@ -210,7 +208,6 @@
   if (thd->is_error()) // An error inside fix_length_and_dec occured
     return TRUE;
   fixed= 1;
-  thd->thd_marker.emb_on_expr_nest= save_emb_on_expr_nest;
   return FALSE;
 }
 

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-08-22 21:00:13 +0000
+++ b/sql/item_subselect.cc	2011-08-29 15:57:41 +0000
@@ -229,7 +229,7 @@
   if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
     return TRUE;
   
-
+  
   if (!(res= engine->prepare()))
   {
     // all transformation is done (used by prepared statements)
@@ -1165,8 +1165,9 @@
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
-  optimizer(0), pushed_cond_guards(NULL), in_strategy(0),
+  Item_exists_subselect(), 
+  left_expr_cache(0), first_execution(TRUE),
+  optimizer(0), pushed_cond_guards(NULL), emb_on_expr_nest(NULL), in_strategy(0),
   is_jtbm_merged(FALSE), is_flattenable_semijoin(FALSE),
   is_registered_semijoin(FALSE), 
   upper_item(0)
@@ -2395,6 +2396,7 @@
   uint outer_cols_num;
   List<Item> *inner_cols;
 
+
   if (in_strategy & SUBS_SEMI_JOIN)
     return !( (*ref)= new Item_int(1));
 
@@ -2449,7 +2451,6 @@
     return TRUE;
   if (Item_subselect::fix_fields(thd_arg, ref))
     return TRUE;
-
   fixed= TRUE;
   return FALSE;
 }

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2011-08-22 21:00:13 +0000
+++ b/sql/item_subselect.h	2011-08-29 15:57:41 +0000
@@ -428,7 +428,6 @@
       join nest pointer - the predicate is an AND-part of ON expression
                           of a join nest   
       NULL              - for all other locations
-    See also THD::emb_on_expr_nest.
   */
   TABLE_LIST *emb_on_expr_nest;
   /*
@@ -447,7 +446,7 @@
 
   /* A bitmap of possible execution strategies for an IN predicate. */
   uchar in_strategy;
-
+   
   bool is_jtbm_merged;
 
   /*
@@ -459,7 +458,7 @@
     TRUE<=>registered in the list of semijoins in outer select
   */
   bool is_registered_semijoin;
-
+  
   /*
     Used to determine how this subselect item is represented in the item tree,
     in case there is a need to locate it there and replace with something else.
@@ -489,7 +488,8 @@
   Item_in_subselect()
     :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
      abort_on_null(0), optimizer(0),
-    pushed_cond_guards(NULL), func(NULL), in_strategy(SUBS_NOT_TRANSFORMED),
+    pushed_cond_guards(NULL), func(NULL), emb_on_expr_nest(NULL), 
+    in_strategy(SUBS_NOT_TRANSFORMED),
     is_jtbm_merged(FALSE),
     upper_item(0)
     {}
@@ -533,6 +533,12 @@
     user.
   */
   int get_identifier();
+
+  void mark_as_condition_AND_part(TABLE_LIST *embedding)
+  {
+    emb_on_expr_nest= embedding;
+  }
+
   friend class Item_ref_null_helper;
   friend class Item_is_not_null_test;
   friend class Item_in_optimizer;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2011-07-21 15:14:34 +0000
+++ b/sql/opt_subselect.cc	2011-08-29 15:57:41 +0000
@@ -340,7 +340,7 @@
         !select_lex->is_part_of_union() &&                            // 2
         !select_lex->group_list.elements && !join->order &&           // 3
         !join->having && !select_lex->with_sum_func &&                // 4
-        thd->thd_marker.emb_on_expr_nest &&                           // 5
+        in_subs->emb_on_expr_nest &&                                  // 5
         select_lex->outer_select()->join &&                           // 6
         parent_unit->first_select()->leaf_tables.elements &&          // 7
         !in_subs->in_strategy &&                                      // 8
@@ -353,7 +353,6 @@
 
       (void)subquery_types_allow_materialization(in_subs);
 
-      in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
       in_subs->is_flattenable_semijoin= TRUE;
 
       /* Register the subquery for further processing in flatten_subqueries() */
@@ -434,10 +433,9 @@
             If the subquery is an AND-part of WHERE register for being processed
             with jtbm strategy
           */
-          if (thd->thd_marker.emb_on_expr_nest == NO_JOIN_NEST &&
+          if (in_subs->emb_on_expr_nest == NO_JOIN_NEST &&
               optimizer_flag(thd, OPTIMIZER_SWITCH_SEMIJOIN))
           {
-            in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
             in_subs->is_flattenable_semijoin= FALSE;
             if (!in_subs->is_registered_semijoin)
 	    {

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2011-08-17 05:48:35 +0000
+++ b/sql/sql_base.cc	2011-08-29 15:57:41 +0000
@@ -8276,7 +8276,6 @@
   SELECT_LEX *select_lex= thd->lex->current_select;
   Query_arena *arena= thd->stmt_arena, backup;
   TABLE_LIST *table= NULL;	// For HP compilers
-  TABLE_LIST *save_emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
   List_iterator<TABLE_LIST> ti(leaves);
   /*
     it_is_update set to TRUE when tables of primary SELECT_LEX (SELECT_LEX
@@ -8317,7 +8316,6 @@
       goto err_no_arena;
   }
 
-  thd->thd_marker.emb_on_expr_nest= NO_JOIN_NEST;
   if (*conds)
   {
     thd->where="where clause";
@@ -8331,11 +8329,11 @@
     */
     if ((*conds)->type() == Item::FIELD_ITEM && !derived)
       wrap_ident(thd, conds);
+    (*conds)->mark_as_condition_AND_part(NO_JOIN_NEST);
     if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) ||
 	(*conds)->check_cols(1))
       goto err_no_arena;
   }
-  thd->thd_marker.emb_on_expr_nest= save_emb_on_expr_nest;
 
   /*
     Apply fix_fields() to all ON clauses at all levels of nesting,
@@ -8351,8 +8349,8 @@
       if (embedded->on_expr)
       {
         /* Make a join an a expression */
-        thd->thd_marker.emb_on_expr_nest= embedded;
         thd->where="on clause";
+        embedded->on_expr->mark_as_condition_AND_part(embedded);
         if ((!embedded->on_expr->fixed &&
              embedded->on_expr->fix_fields(thd, &embedded->on_expr)) ||
 	    embedded->on_expr->check_cols(1))
@@ -8376,7 +8374,6 @@
       }
     }
   }
-  thd->thd_marker.emb_on_expr_nest= save_emb_on_expr_nest;
 
   if (!thd->stmt_arena->is_conventional())
   {

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-07-19 20:19:10 +0000
+++ b/sql/sql_class.h	2011-08-29 15:57:41 +0000
@@ -1600,15 +1600,6 @@
   /* container for handler's private per-connection data */
   Ha_data ha_data[MAX_HA];
 
-  /* Place to store various things */
-  union 
-  { 
-    /*
-      Used by subquery optimizations, see Item_in_subselect::emb_on_expr_nest.
-    */
-    TABLE_LIST *emb_on_expr_nest;
-  } thd_marker;
-
   bool prepare_derived_at_open;
 
   /* 

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-08-16 12:51:40 +0000
+++ b/sql/sql_parse.cc	2011-08-29 15:57:41 +0000
@@ -5052,7 +5052,6 @@
       param->select_limit=
         new Item_int((ulonglong) thd->variables.select_limit);
   }
-  thd->thd_marker.emb_on_expr_nest= NULL;
   if (!(res= open_and_lock_tables(thd, all_tables)))
   {
     if (lex->describe)
@@ -5826,7 +5825,6 @@
 
   thd->query_plan_flags= QPLAN_INIT;
   thd->query_plan_fsort_passes= 0;
-  thd->thd_marker.emb_on_expr_nest= NULL;
 
   /*
     Because we come here only for start of top-statements, binlog format is

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2011-06-14 02:03:03 +0000
+++ b/sql/sql_prepare.cc	2011-08-29 15:57:41 +0000
@@ -1413,7 +1413,6 @@
     goto error;
 
   thd->used_tables= 0;                        // Updated by setup_fields
-  thd->thd_marker.emb_on_expr_nest= 0;
 
   /*
     JOIN::prepare calls



More information about the commits mailing list