List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:May 26 2011 11:09am
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3122) Bug#11765699
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-trunk-11765699/ based on revid:sergey.glukhov@stripped

 3122 Jorgen Loland	2011-05-26
      Bug#11765699 (58690) !TABLE ||
                          (!TABLE->READ_SET || 
                           BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
            
      JOIN::conds points to the same expression tree as 
      SELECT_LEX::where, so modifying the expression tree of 
      JOIN::conds effectively does the same modifications to 
      SELECT_LEX::where.
      
      JOIN::conds is optimized in JOIN::optimize()
      
        conds= optimize_cond(conds);
      
      optimize_cond() makes modifications to the conds input 
      parameter. One of these modifications removes an item from 
      the input conds parameter. This was not a problem for 
      JOIN::conds because removed items are included in the 
      condition returned from the function and therefore assigned 
      to conds as can be seen above. However, the removed parts of
      the condition were lost from SELECT_LEX::where.
      
      This bug was about a query with three levels of subselects:
      
      SELECT 1 FROM t1 WHERE a =
        (SELECT 1 FROM t2 WHERE b =
           (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
         GROUP BY b
      );
      
      Prior to optimization, select#3 has
      
      join->conds = select_lex->where = "c = 1 OR t1.a = 1 AND 1 = 2"
      
      As part of optimize_cond(), remove_eq_conds() sees that
      "t1.a = 1 AND 1 = 2" is FALSE and correctly removes this 
      condition. This leaves the OR item with only one argument: 
      "c = 1". With only one argument left in this OR, 
      internal_remove_eq_conds() empties the argument list of the OR 
      and returns "c = 1". The effect is that after optimize_cond(), 
      select#3 has:
      
      join->conds = "c = 1"
      select_lex->where = ""   // <- !!
      
      Fixed by not emptying the argument list of an Item_cond even 
      when there is only one argument. The effect is that after 
      optimize_cond(), select_lex->where is an OR-item with only 
      one argument: "c = 1". This is slightly inefficient (evaluation
      would have to go through the OR), but it works correctly. Note 
      that condition evaluation is performed using the correctly 
      optimized JOIN::conds.
     @ mysql-test/include/subquery.inc
        Added test for BUG#11765699
     @ mysql-test/r/subquery_nomat_nosj.result
        Added test for BUG#11765699
     @ mysql-test/r/subquery_none.result
        Added test for BUG#11765699
     @ sql/sql_select.cc
        In internal_remove_eq_conds(): Do not empty argument_list() of 
        an Item_cond with only one argument. This will result in lost
        condition parts in SELECT_LEX::where/having

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_none.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-05-05 07:41:53 +0000
+++ b/mysql-test/include/subquery.inc	2011-05-26 11:09:57 +0000
@@ -5354,3 +5354,32 @@ CREATE TABLE t(a VARCHAR(245) DEFAULT
 INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('');
 SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d;
 DROP TABLE t;
+
+--echo #
+--echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+--echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1); 
+
+CREATE TABLE t2(
+  b TEXT, 
+  c INT, 
+  PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   ORDER BY b
+ );
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   GROUP BY b
+ );
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-05-26 11:09:57 +0000
@@ -6507,4 +6507,29 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+#                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2(
+b TEXT, 
+c INT, 
+PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+ORDER BY b
+);
+1
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_none.result	2011-05-26 11:09:57 +0000
@@ -6506,4 +6506,29 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+#                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2(
+b TEXT, 
+c INT, 
+PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+ORDER BY b
+);
+1
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-05-26 06:03:02 +0000
+++ b/sql/sql_select.cc	2011-05-26 11:09:57 +0000
@@ -1904,6 +1904,11 @@ JOIN::optimize()
       thd->restore_active_arena(arena, &backup);
   }
 
+  /*
+    Note: optimize_cond() makes changes to conds. Since
+    select_lex->where and conds points to the same condition, this
+    function call effectively changes select_lex->where as well.
+  */
   conds= optimize_cond(this, conds, join_list, TRUE, &select_lex->cond_value);
   if (thd->is_error())
   {
@@ -1913,6 +1918,7 @@ JOIN::optimize()
   }
 
   {
+    // Note above about optimize_cond() also applies to selec_lex->having
     having= optimize_cond(this, having, join_list, FALSE,
                           &select_lex->having_value);
     if (thd->is_error())
@@ -14711,7 +14717,8 @@ optimize_cond(JOIN *join, Item *conds, L
   SYNPOSIS
     remove_eq_conds()
     thd 			THD environment
-    cond                        the condition to handle
+    cond                        the condition to handle. Note that cond
+                                is changed by this function
     cond_value                  the resulting value of the condition
 
   RETURN
@@ -14773,9 +14780,34 @@ internal_remove_eq_conds(THD *thd, Item 
 	*cond_value != Item::COND_OK)
       return (Item*) 0;
     if (((Item_cond*) cond)->argument_list()->elements == 1)
-    {						// Remove list
+    {
+      /*
+        BUG#11765699:
+        We're dealing with an AND or OR item that has only one
+        argument. However, it is not an option to empty the list
+        because:
+
+         - this function is called for either JOIN::conds or
+           JOIN::having, but these point to the same condition as
+           SELECT_LEX::where and SELECT_LEX::having do.
+
+         - The return value of remove_eq_conds() is assigned to
+           JOIN::conds and JOIN::having, so emptying the list and
+           returning the only remaining item "replaces" the AND or OR
+           with item for the variables in JOIN. However, the return
+           value is not assigned to the SELECT_LEX counterparts. Thus,
+           if argument_list is emptied, SELECT_LEX forgets the item in
+           argument_list()->head().
+
+        item is therefore returned, but argument_list is not emptied.
+      */
       item= ((Item_cond*) cond)->argument_list()->head();
-      ((Item_cond*) cond)->argument_list()->empty();
+      /*
+        Consider reenabling the line below when the optimizer has been
+        split into properly separated phases.
+ 
+        ((Item_cond*) cond)->argument_list()->empty();
+      */
       return item;
     }
   }


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110526110957-hri6lm8atmw2oh46.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3122) Bug#11765699Jorgen Loland26 May