List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:November 18 2010 9:47am
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:tor.didriksen@stripped

 3287 Roy Lyseng	2010-11-18
      Bug#57525: Semijoin transformed subquery with inner grouped subquery
                 gives empty result.
      
      This is a followup to bug#31480, which attempted to fix resolved
      information in subqueries as part of semijoin transformation.
      
      The specific problem here is that the inner subquery (which is not
      transformed) contains an outer reference to the outer-most block in
      its HAVING clause. The HAVING clause is represented by Item_ref
      objects, and these are not re-resolved properly through
      fix_after_pullout().
      The solution lies in realizing that Item_ref objects contain a
      complete set of resolution data structures (ie depended_from,
      name resolution context), and that it may point to an Item object
      with it's own set of resolution data structures.
      Hence, we implement fix_after_pullout() for Item_ref by first
      calling fix_after_pullout() on the referenced object and then for
      itself.
      
      The above fix revealed another problem: The offending query failed
      in prepared statement mode. The reason is that the depended_from
      field in Item_ref is reset in cleanup() and not restored at the
      next fix_fields() call. A hack that persists the depended_from
      field across muliple fix_fields() solves this problem, and I think
      it is safe in the general case.
      
      There is also another followup to bug#31480:
      
      A - t1
       \
        B - t2
         \
          C - t3
           \
            D - t4
              where t1.x=t4.y
      
      The above figure describes an outer query expression (select_lex A)
      with 3 nested subqueries represented by select_lex B, C and D.
      The innermost subquery (D) contains a reference to a table t1
      in the outermost block (A).
      The original resolver marked the subquery containing query expression  
      B as using table t1, and the subqueries containing query expressions
      C and D with OUTER_REF_TABLE_BIT.
      The code in bug#31480 failed to preserve all outer references
      when calling fix_after_pullout().
      
      mysql-test/r/optimizer_switch.result
        Updated with correct result and plan for query that exposed bug.
      
      mysql-test/t/optimizer_switch.test
        Warnings about wrong results removed.
      
      sql/item.cc
        A common implementation of fix_after_pullout() for all Item_ref
        classes has been made. The exception is Item_aggregate_ref which
        must have its own implementation and Item_outer_ref which we still
        have no test case for.
        Item_ref::fix_after_pullout() now calls fix_after_pullout() on the
        referenced item, and then it calls Item_ident::fix_after_pullout()
        on itself.
        Item_field::fix_after_pullout() is also moved to class Item_ident,
        which is parent class for both Item_field and Item_ref.
        A new function Item_ref::resolved_used_tables() was needed.
      
      sql/item.h
        Some adjustments to function prototypes, see sql/item.cc.
      
      sql/item_subselect.h
        One changed friend declaration.

    modified:
      mysql-test/r/optimizer_switch.result
      mysql-test/t/optimizer_switch.test
      sql/item.cc
      sql/item.h
      sql/item_subselect.h
=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result	2010-11-08 14:51:09 +0000
+++ b/mysql-test/r/optimizer_switch.result	2010-11-18 09:47:04 +0000
@@ -293,14 +293,13 @@ a	b
 3	20
 2	30
 set @@optimizer_switch='materialization=off,semijoin=on';
-# The query result with semijoin is WRONG
 EXPLAIN SELECT * FROM t1 AS ta
 WHERE ta.a IN (SELECT c FROM t2 AS tb
 WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
 GROUP BY f
 HAVING ta.a=tc.f));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
 1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
 3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
 SELECT * FROM t1 AS ta
@@ -309,6 +308,10 @@ WHERE tb.d >= SOME(SELECT SUM(g) FROM t4
 GROUP BY f
 HAVING ta.a=tc.f));
 a	b
+2	10
+2	20
+3	20
+2	30
 # Subquery with ORDER BY and LIMIT
 set @@optimizer_switch='materialization=off,semijoin=off';
 # NOTE: The ordered subquery should have a LIMIT clause to make sense
@@ -332,7 +335,6 @@ a	b
 2	30
 4	40
 set @@optimizer_switch='materialization=off,semijoin=on';
-# The query result with semijoin is WRONG
 EXPLAIN SELECT * FROM t1 AS ta
 WHERE ta.a IN (SELECT c FROM t2 AS tb
 WHERE tb.d IN (SELECT g FROM t4 as tc

=== modified file 'mysql-test/t/optimizer_switch.test'
--- a/mysql-test/t/optimizer_switch.test	2010-10-15 10:32:50 +0000
+++ b/mysql-test/t/optimizer_switch.test	2010-11-18 09:47:04 +0000
@@ -280,8 +280,6 @@ eval $query;
 
 set @@optimizer_switch='materialization=off,semijoin=on';
 
---echo # The query result with semijoin is WRONG
-
 eval EXPLAIN $query;
 eval $query;
 
@@ -303,8 +301,6 @@ eval $query;
 
 set @@optimizer_switch='materialization=off,semijoin=on';
 
---echo # The query result with semijoin is WRONG
-
 eval EXPLAIN $query;
 eval $query;
 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-11-14 18:09:32 +0000
+++ b/sql/item.cc	2010-11-18 09:47:04 +0000
@@ -1457,6 +1457,13 @@ public:
                   const char *table_name_arg, const char *field_name_arg)
     :Item_ref(context_arg, item, table_name_arg, field_name_arg) {}
 
+  virtual void fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref_arg)
+  {
+    (*ref)->fix_after_pullout(parent_select, removed_select, ref);
+  }
+
   virtual inline void print (String *str, enum_query_type query_type)
   {
     if (ref)
@@ -2319,7 +2326,7 @@ table_map Item_field::resolved_used_tabl
   return field->table->map;
 }
 
-void Item_field::fix_after_pullout(st_select_lex *parent_select,
+void Item_ident::fix_after_pullout(st_select_lex *parent_select,
                                    st_select_lex *removed_select,
                                    Item **ref)
 {
@@ -2359,17 +2366,16 @@ void Item_field::fix_after_pullout(st_se
     */
     st_select_lex *child_select= context->select_lex;
 
-    if (child_select->outer_select() != depended_from)
+    while (child_select->outer_select() != depended_from)
     {
       /*
         The subquery on this level is outer-correlated with respect to the field
       */
       Item_subselect *subq_predicate= child_select->master_unit()->item;
-      subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
-    }
 
-    while (child_select->outer_select() != depended_from)
+      subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
       child_select= child_select->outer_select();
+    }
 
     /*
       child_select is select_lex immediately inner to the depended_from level.
@@ -6495,10 +6501,26 @@ void Item_ref::set_properties()
 }
 
 
+table_map Item_ref::resolved_used_tables() const
+{
+  DBUG_ASSERT((*ref)->type() == FIELD_ITEM);
+  return ((Item_field*)(*ref))->resolved_used_tables();
+}
+
+
 void Item_ref::cleanup()
 {
   DBUG_ENTER("Item_ref::cleanup");
+  /*
+    Save depended_from so that it can be restored after Item_ident::cleanup().
+    Item_ref::depended_from is not recalculated in later fix_fields() calls,
+    so this hack is needed to have correct dependency information in prepared
+    statement execution. Dependencies should not change unless an involved
+    view is updated, and this currently has much more severe implications.
+  */
+  st_select_lex *save_depended_from= depended_from;
   Item_ident::cleanup();
+  depended_from= save_depended_from;
   result_field= 0;
   DBUG_VOID_RETURN;
 }
@@ -6871,29 +6893,12 @@ void Item_ref::fix_after_pullout(st_sele
                                  st_select_lex *removed_select,
                                  Item **ref_arg)
 {
-  // @todo: Find an actual test case where depended_from == new_parent.
-  DBUG_ASSERT(depended_from != parent_select);
-  if (depended_from == parent_select)
-    depended_from= NULL;
-}
-
-void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select,
-                                             st_select_lex *removed_select,
-                                             Item **refptr)
-{
-  DBUG_EXECUTE("where",
-               print_where(*refptr,
-                           "Item_direct_view_ref::fix_after_pullout",
-                           QT_ORDINARY););
-
   (*ref)->fix_after_pullout(parent_select, removed_select, ref);
 
-  // @todo: Find an actual test case where depended_from == parent_select.
-  DBUG_ASSERT(depended_from != parent_select);
-  if (depended_from == parent_select)
-    depended_from= NULL;
+  Item_ident::fix_after_pullout(parent_select, removed_select, ref);
 }
 
+
 /**
   Compare two view column references for equality.
 

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-10-15 10:32:50 +0000
+++ b/sql/item.h	2010-11-18 09:47:04 +0000
@@ -1675,7 +1675,13 @@ public:
              const char *field_name_arg);
   Item_ident(THD *thd, Item_ident *item);
   Item_ident(TABLE_LIST *view_arg, const char *field_name_arg);
+  /*
+    Return used table information for the level on which this table is resolved.
+  */
+  virtual table_map resolved_used_tables() const= 0;
   const char *full_name() const;
+  virtual void fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select, Item **ref);
   void cleanup();
   bool remove_dependence_processor(uchar * arg);
   virtual void print(String *str, enum_query_type query_type);
@@ -1761,16 +1767,11 @@ public:
   bool send(Protocol *protocol, String *str_arg);
   void reset_field(Field *f);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *parent_select,
-                         st_select_lex *removed_select, Item **ref);
   void make_field(Send_field *tmp_field);
   int save_in_field(Field *field,bool no_conversions);
   void save_org_in_field(Field *field);
   table_map used_tables() const;
-  /*
-    Return used table information for the level on which this table is resolved.
-  */
-  table_map resolved_used_tables() const;
+  virtual table_map resolved_used_tables() const;
   enum Item_result result_type () const
   {
     return field->result_type();
@@ -2604,6 +2605,7 @@ public:
     if (!depended_from) 
       (*ref)->update_used_tables(); 
   }
+  virtual table_map resolved_used_tables() const;
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
   void set_result_field(Field *field)	{ result_field= field; }
   bool is_result_field() { return 1; }
@@ -2726,8 +2728,6 @@ public:
   {}
 
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *parent_select,
-                         st_select_lex *removed_select, Item **ref);
   bool eq(const Item *item, bool binary_cmp) const;
   Item *get_tmp_table_item(THD *thd)
   {

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-10-15 10:32:50 +0000
+++ b/sql/item_subselect.h	2010-11-18 09:47:04 +0000
@@ -172,7 +172,7 @@ public:
   friend bool Item_field::fix_fields(THD *, Item **);
   friend int  Item_field::fix_outer_field(THD *, Field **, Item **);
   friend bool Item_ref::fix_fields(THD *, Item **);
-  friend void Item_field::fix_after_pullout(st_select_lex *parent_select,
+  friend void Item_ident::fix_after_pullout(st_select_lex *parent_select,
                                             st_select_lex *removed_select,
                                             Item **ref);
   friend void mark_select_range_as_dependent(THD*,


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101118094704-8z1qtmnlqly50xsi.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525Roy Lyseng18 Nov
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525Jorgen Loland1 Dec
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525Roy Lyseng1 Dec
      • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525Jorgen Loland1 Dec
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57525Øystein Grøvlen20 Jan