[Commits] Rev 2845: Fixed LP BUG#641203: Query returns rows where no result is expected (impossible WHERE) in file:///home/tsk/mprog/src/5.3-mwl89/

timour at askmonty.org timour at askmonty.org
Fri Nov 19 12:54:25 EET 2010


At file:///home/tsk/mprog/src/5.3-mwl89/

------------------------------------------------------------
revno: 2845
revision-id: timour at askmonty.org-20101119105415-h7c235w3guu5zz00
parent: timour at askmonty.org-20101105151048-owgvmj1z8o8g98md
committer: timour at askmonty.org
branch nick: 5.3-mwl89
timestamp: Fri 2010-11-19 12:54:15 +0200
message:
  Fixed LP BUG#641203: Query returns rows where no result is expected (impossible WHERE)
  
  The cause for the bug was two-fold:
  1. Incorrect detection of whether a table is the first one in a query plan -
    "used_table & 1" actually checks if used_table is table with number "1".
  2. Missing logic to delay the evaluation of (expensive) constant conditions
    during the execution phase.
  
  The fix adds/changes:
  The patch:
  - removes incorrect treatment of expensive predicates from make_cond_for_table,
    and lets the caller decide when to evaluate expensive predicates.
  - saves expensive constant conditions in JOIN::exec_const_cond,
    which is evaluated once in the beginning of JOIN::exec.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-10-28 17:04:23 +0000
+++ b/mysql-test/r/subselect4.result	2010-11-19 10:54:15 +0000
@@ -397,3 +397,42 @@ pk
 # Restore old value for Index condition pushdown
 SET SESSION engine_condition_pushdown=@old_icp;
 DROP TABLE t1,t2;
+#
+# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+#
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1      c1
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1      c1
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1      c1      c1
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1      c1
+drop table t1, t2, t3, t4;

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-10-28 17:04:23 +0000
+++ b/mysql-test/t/subselect4.test	2010-11-19 10:54:15 +0000
@@ -370,3 +370,29 @@ WHERE 
 SET SESSION engine_condition_pushdown=@old_icp;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+--echo #
+
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+drop table t1, t2, t3, t4;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-05 12:42:58 +0000
+++ b/sql/sql_select.cc	2010-11-19 10:54:15 +0000
@@ -1821,6 +1821,9 @@ JOIN::exec()
   if (tables)
     thd->limit_found_rows= 0;
 
+  if (exec_const_cond && !exec_const_cond->val_int())
+    zero_result_cause= "Impossible WHERE noticed after reading const tables";
+
   if (zero_result_cause)
   {
     (void) return_zero_rows(this, result, select_lex->leaf_tables,
@@ -6626,11 +6629,12 @@ make_join_select(JOIN *join,SQL_SELECT *
            there inside the triggers.
       */
       {                                         // Check const tables
-        COND *const_cond=
+        join->exec_const_cond=
           make_cond_for_table(cond,
                               join->const_table_map,
-                              (table_map) 0, TRUE);
-        DBUG_EXECUTE("where",print_where(const_cond,"constants", QT_ORDINARY););
+                              (table_map) 0, FALSE);
+        DBUG_EXECUTE("where",print_where(join->exec_const_cond, "constants",
+                                         QT_ORDINARY););
         for (JOIN_TAB *tab= join->join_tab+join->const_tables;
              tab < join->join_tab+join->tables ; tab++)
         {
@@ -6639,7 +6643,7 @@ make_join_select(JOIN *join,SQL_SELECT *
             JOIN_TAB *cond_tab= tab->first_inner;
             COND *tmp= make_cond_for_table(*tab->on_expr_ref,
                                            join->const_table_map,
-                                         (  table_map) 0, FALSE);
+                                           (table_map) 0, FALSE);
             if (!tmp)
               continue;
             tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
@@ -6655,10 +6659,13 @@ make_join_select(JOIN *join,SQL_SELECT *
             cond_tab->select_cond->quick_fix_field();
           }       
         }
-        if (const_cond && !const_cond->val_int())
+
+        if (join->exec_const_cond && !join->exec_const_cond->is_expensive() &&
+            !join->exec_const_cond->val_int())
         {
-          DBUG_PRINT("info",("Found impossible WHERE condition"));
-          DBUG_RETURN(1);        // Impossible const condition
+          DBUG_PRINT("info",("Found impossible WHERE condition"));
+          join->exec_const_cond= NULL;
+          DBUG_RETURN(1);        // Impossible const condition
         }
       }
     }
@@ -14658,32 +14664,17 @@ bool test_if_ref(Item *root_cond, Item_f
 
 static Item *
 make_cond_for_table(Item *cond, table_map tables, table_map used_table,
-                    bool exclude_expensive_cond)
+                    bool exclude_expensive_cond __attribute__((unused)))
 {
   return make_cond_for_table_from_pred(cond, cond, tables, used_table,
                                        exclude_expensive_cond);
 }
-               
+
 static Item *
 make_cond_for_table_from_pred(Item *root_cond, Item *cond,
                               table_map tables, table_map used_table,
-                              bool exclude_expensive_cond)
-
+                              bool exclude_expensive_cond __attribute__((unused)))
 {
-  if (used_table && !(cond->used_tables() & used_table) &&
-      /*
-        Exclude constant conditions not checked at optimization time if
-        the table we are pushing conditions to is the first one.
-        As a result, such conditions are not considered as already checked
-        and will be checked at execution time, attached to the first table.
-
-        psergey: TODO: "used_table & 1" doesn't make sense in nearly any
-        context. Look at setup_table_map(), table bits reflect the order 
-        the tables were encountered by the parser. Check what we should
-        replace this condition with.
-      */
-      !((used_table & 1) && cond->is_expensive()))
-    return (COND*) 0;                           // Already checked
   if (cond->type() == Item::COND_ITEM)
   {
     if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
@@ -14751,12 +14742,7 @@ make_cond_for_table_from_pred(Item *root
     table_count times, we mark each item that we have examined with the result
     of the test
   */
-  if (cond->marker == 3 || (cond->used_tables() & ~tables) ||
-      /*
-        When extracting constant conditions, treat expensive conditions as
-        non-constant, so that they are not evaluated at optimization time.
-      */
-      (!used_table && exclude_expensive_cond && cond->is_expensive()))
+  if (cond->marker == 3 || (cond->used_tables() & ~tables))
     return (COND*) 0;                           // Can't check this yet
   if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
     return cond;                                // Not boolean op

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-11-05 12:42:58 +0000
+++ b/sql/sql_select.h	2010-11-19 10:54:15 +0000
@@ -1593,6 +1593,12 @@ public:
   List<TABLE_LIST> *join_list;       ///< list of joined tables in reverse order
   COND_EQUAL *cond_equal;
   COND_EQUAL *having_equal;
+  /*
+    Constant codition computed during optimization, but evaluated during
+    join execution. Typically expensive conditions that should not be
+    evaluated at optimization time.
+  */
+  Item *exec_const_cond;
   SQL_SELECT *select;                ///<created in optimisation phase
   JOIN_TAB *return_tab;              ///<used only for outer joins
   Item **ref_pointer_array; ///<used pointer reference for this select
@@ -1689,6 +1695,7 @@ public:
     initialized= 0;
     cond_equal= 0;
     having_equal= 0;
+    exec_const_cond= 0;
     group_optimized_away= 0;
     no_rows_in_result_called= 0;
 



More information about the commits mailing list