List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:August 10 2011 2:46pm
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3354 to 3355) Bug#12795555
View as plain text  
 3355 Olav Sandstaa	2011-08-10
      Bug#12795555: Missing rows with ANY subquery.
      
      Item_maxmin_subselect::was_values was assigned to TRUE initially. It expected
      to be reset on the first subselect execution, but when outer value is NULL
      this doesn't happen, thus causing missing rows.
      The solution is to initialize Item_maxmin_subselect::was_values to false and 
      evaluate the subquery if hasn't been evaluated yet.
      
      The patch is implemented by Evgeny Potemkin.
     @ mysql-test/include/subquery.inc
        Added a test case for the bug#12795555.
     @ mysql-test/r/subquery_nomat_nosj.result
        Added a test case for the bug#12795555.
     @ mysql-test/r/subquery_none.result
        Added a test case for the bug#12795555.
     @ sql/item_cmpfunc.cc
        Bug#12795555: Missing rows with ANY subquery.
        Item_func_not_all now evaluates the underlying subquery if needed.
     @ sql/item_cmpfunc.h
        Bug#12795555: Missing rows with ANY subquery.
        Item_func_not_all now can evaluate underlying subquery when needed.
     @ sql/item_subselect.cc
        Bug#12795555: Missing rows with ANY subquery.
        Item_in_subselect::single_value_transformer now sets subselect to
        Item_func_[nop|not]_all to allow subquery evaluation when needed.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_none.result
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/item_subselect.cc
 3354 Sven Sandberg	2011-08-10
      BUG#11766392: 59495: RPL.RPL_READ_OLD_RELAY_LOG_INFO FAILS INTERMITTENTLY IN PB2
      Wrapped debug printout in '#ifndef NO_DBUG'.

    modified:
      sql/rpl_info_file.cc
=== modified file 'mysql-test/include/subquery.inc'

=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-07-20 13:32:12 +0000
+++ b/mysql-test/include/subquery.inc	2011-08-10 14:45:26 +0000
@@ -5502,3 +5502,32 @@
 eval explain $query;
 
 DROP TABLE t1,t3;
+
+--echo #
+--echo # Bug#12795555: Missing rows with ALL/ANY subquery
+--echo #
+# Test the case when Item_maxmin_subselect is used
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VAlUES (NULL),(1),(NULL),(2);
+SELECT f1 FROM t1 WHERE f1 < ALL (SELECT 1 FROM DUAL WHERE 0);
+DROP TABLE t1;
+# Test the case when Item_sum_[max|min] is used
+CREATE TABLE t1 (k VARCHAR(1), KEY k(k));
+INSERT INTO t1 VALUES ('r'), (NULL), (NULL);
+
+CREATE TABLE t2 (c VARCHAR(1));
+INSERT INTO t2 VALUES ('g'), (NULL);
+
+CREATE TABLE t3 (c VARCHAR(1));
+
+SELECT COUNT(*)
+FROM t1 JOIN t2
+WHERE t1.k < ALL(
+  SELECT c
+  FROM t3
+);
+
+DROP TABLE t1, t2, t3; 
+
+--echo #
+

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-07-20 13:32:12 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-08-10 14:45:26 +0000
@@ -6682,4 +6682,31 @@
 2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using index condition; Using where
 2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
 DROP TABLE t1,t3;
+#
+# Bug#12795555: Missing rows with ALL/ANY subquery
+#
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VAlUES (NULL),(1),(NULL),(2);
+SELECT f1 FROM t1 WHERE f1 < ALL (SELECT 1 FROM DUAL WHERE 0);
+f1
+NULL
+1
+NULL
+2
+DROP TABLE t1;
+CREATE TABLE t1 (k VARCHAR(1), KEY k(k));
+INSERT INTO t1 VALUES ('r'), (NULL), (NULL);
+CREATE TABLE t2 (c VARCHAR(1));
+INSERT INTO t2 VALUES ('g'), (NULL);
+CREATE TABLE t3 (c VARCHAR(1));
+SELECT COUNT(*)
+FROM t1 JOIN t2
+WHERE t1.k < ALL(
+SELECT c
+FROM t3
+);
+COUNT(*)
+6
+DROP TABLE t1, t2, t3;
+#
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-07-20 13:32:12 +0000
+++ b/mysql-test/r/subquery_none.result	2011-08-10 14:45:26 +0000
@@ -6681,4 +6681,31 @@
 2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using where
 2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
 DROP TABLE t1,t3;
+#
+# Bug#12795555: Missing rows with ALL/ANY subquery
+#
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VAlUES (NULL),(1),(NULL),(2);
+SELECT f1 FROM t1 WHERE f1 < ALL (SELECT 1 FROM DUAL WHERE 0);
+f1
+NULL
+1
+NULL
+2
+DROP TABLE t1;
+CREATE TABLE t1 (k VARCHAR(1), KEY k(k));
+INSERT INTO t1 VALUES ('r'), (NULL), (NULL);
+CREATE TABLE t2 (c VARCHAR(1));
+INSERT INTO t2 VALUES ('g'), (NULL);
+CREATE TABLE t3 (c VARCHAR(1));
+SELECT COUNT(*)
+FROM t1 JOIN t2
+WHERE t1.k < ALL(
+SELECT c
+FROM t3
+);
+COUNT(*)
+6
+DROP TABLE t1, t2, t3;
+#
 set optimizer_switch=default;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-07-28 10:54:44 +0000
+++ b/sql/item_cmpfunc.cc	2011-08-10 14:45:26 +0000
@@ -327,6 +327,20 @@
 
 bool Item_func_not_all::empty_underlying_subquery()
 {
+  DBUG_ASSERT(subselect || !(test_sum_item || test_sub_item));
+  /*
+   When outer argument is NULL the subquery has not yet been evaluated, we
+   need to evaluate it to get to know whether it returns any rows to return
+   the correct result. 'ANY' subqueries are an exception because the
+   result would be false or null which for a top level item always mean false.
+   The subselect->unit->item->... chain should be used instead of
+   subselect->... to workaround subquery transformation which could make
+   subselect->engine unusable.
+  */
+  if (subselect && 
+      subselect->substype() != Item_subselect::ANY_SUBS &&
+      !subselect->unit->item->is_evaluated())
+    subselect->unit->item->exec();
   return ((test_sum_item && !test_sum_item->any_value()) ||
           (test_sub_item && !test_sub_item->any_value()));
 }

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2011-07-21 15:11:09 +0000
+++ b/sql/item_cmpfunc.h	2011-08-10 14:45:26 +0000
@@ -525,14 +525,15 @@
   /* allow to check presence of values in max/min optimization */
   Item_sum_hybrid *test_sum_item;
   Item_maxmin_subselect *test_sub_item;
+  Item_subselect *subselect;
 
   bool abort_on_null;
 public:
   bool show;
 
   Item_func_not_all(Item *a)
-    :Item_func_not(a), test_sum_item(0), test_sub_item(0), abort_on_null(0),
-     show(0)
+    :Item_func_not(a), test_sum_item(0), test_sub_item(0), subselect(0),
+     abort_on_null(0), show(0)
     {}
   virtual void top_level_item() { abort_on_null= 1; }
   bool top_level() { return abort_on_null; }
@@ -542,6 +543,7 @@
   virtual void print(String *str, enum_query_type query_type);
   void set_sum_test(Item_sum_hybrid *item) { test_sum_item= item; };
   void set_sub_test(Item_maxmin_subselect *item) { test_sub_item= item; };
+  void set_subselect(Item_subselect *item) { subselect= item; }
   bool empty_underlying_subquery();
   Item *neg_transformer(THD *thd);
 };

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-07-28 10:54:44 +0000
+++ b/sql/item_subselect.cc	2011-08-10 14:45:26 +0000
@@ -560,7 +560,7 @@
                                              Item_subselect *parent,
 					     st_select_lex *select_lex,
 					     bool max_arg)
-  :Item_singlerow_subselect(), was_values(TRUE)
+  :Item_singlerow_subselect(), was_values(false)
 {
   DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect");
   max= max_arg;
@@ -584,15 +584,7 @@
   DBUG_ENTER("Item_maxmin_subselect::cleanup");
   Item_singlerow_subselect::cleanup();
 
-  /*
-    By default it is TRUE to avoid TRUE reporting by
-    Item_func_not_all/Item_func_nop_all if this item was never called.
-
-    Engine exec() set it to FALSE by reset_value_registration() call.
-    select_max_min_finder_subselect::send_data() set it back to TRUE if some
-    value will be found.
-  */
-  was_values= TRUE;
+  was_values= false;
   DBUG_VOID_RETURN;
 }
 
@@ -1242,6 +1234,8 @@
       if (upper_item)
         upper_item->set_sub_test(item);
     }
+    if (upper_item)
+      upper_item->set_subselect(this);
     /* fix fields is already called for  left expression */
     substitution= func->create(left_expr, subs);
     DBUG_RETURN(RES_OK);

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3354 to 3355) Bug#12795555Olav Sandstaa11 Aug