List:Commits« Previous MessageNext Message »
From:Norvald H. Ryeng Date:April 20 2012 1:01pm
Subject:bzr push into mysql-trunk branch (norvald.ryeng:3708 to 3709) Bug#12888306
View as plain text  
 3709 Norvald H. Ryeng	2012-04-20
      Bug#12888306 MISSING ROWS FOR SELECT >ALL (SUBQUERY WITHOUT ROWS)
      
      Problem: Subqueries with implicit grouping may return incorrect
      results due to not evaluating the WHERE clause.
      
      Queries with ALL/ANY are rewritten to use MIN/MAX. The rewritten form
      of the query has no GROUP BY clause, so these queries fit the pattern
      for triggering this bug.
      
      JOIN::optimize() calls opt_sum_query() to replace aggregation
      functions with constant fields for implicitly grouped queries. If the
      aggregation function is MIN or MAX, it checks the argument to the
      aggregation function. If it is a constant, the constant is selected as
      the minimum/maximum value if the table has rows, and NULL if it
      doesn't. However, the WHERE clause is not checked, so the MIN/MAX may
      be evaluated to a constant when it should have been NULL.
      
      Fix: Don't replace MIN/MAX of constants if there is a WHERE clause.
     @ mysql-test/r/explain.result
        Changes to EXPLAIN for queries with implicit grouping.
     @ mysql-test/r/fulltext.result
        Corrected test case for bug #12888306.
     @ mysql-test/r/group_by.result
        Add test case for bug #12888306.
     @ mysql-test/t/fulltext.test
        Corrected test case for bug #12888306.
     @ mysql-test/t/group_by.test
        Add test case for bug #12888306.
     @ sql/opt_sum.cc
        Don't replace MIN/MAX of constants if there are conds.

    modified:
      mysql-test/r/explain.result
      mysql-test/r/fulltext.result
      mysql-test/r/group_by.result
      mysql-test/t/fulltext.test
      mysql-test/t/group_by.test
      sql/opt_sum.cc
 3708 Hemant Kumar	2012-04-20
      making main.bug12427262 test experimental on windows.

    modified:
      mysql-test/collections/default.experimental
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result	2012-04-18 13:06:39 +0000
+++ b/mysql-test/r/explain.result	2012-04-20 13:01:26 +0000
@@ -308,8 +308,9 @@ EXPLAIN SELECT 1 FROM t1
 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
 WHERE t1.f1 GROUP BY t1.f1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	t1	fulltext	f1	f1	0	NULL	1	Using where
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
  WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -317,12 +318,14 @@ PREPARE stmt FROM
  WHERE t1.f1 GROUP BY t1.f1))';
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	t1	fulltext	f1	f1	0	NULL	1	Using where
 EXECUTE stmt;
 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	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	t1	fulltext	f1	f1	0	NULL	1	Using where
 DEALLOCATE PREPARE stmt;
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
@@ -331,12 +334,14 @@ PREPARE stmt FROM
  WHERE t1.f1 GROUP BY t1.f1))';
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	t1	fulltext	f1	f1	0	NULL	1	Using where
 EXECUTE stmt;
 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	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	NULL
+2	SUBQUERY	t1	fulltext	f1	f1	0	NULL	1	Using where
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests.

=== modified file 'mysql-test/r/fulltext.result'
--- a/mysql-test/r/fulltext.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/fulltext.result	2012-04-20 13:01:26 +0000
@@ -680,11 +680,12 @@ PREPARE stmt FROM
  ALL((SELECT 1 FROM t1 JOIN t1 a
  ON (MATCH(t1.f1) against (""))
  WHERE t1.f1 GROUP BY t1.f1))';
-# See BUG#12888306. Correct result is one row with value 1.
 EXECUTE stmt;
 1
+1
 EXECUTE stmt;
 1
+1
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/group_by.result	2012-04-20 13:01:26 +0000
@@ -2649,3 +2649,12 @@ MIN(a)	b
 0	a
 
 DROP TABLE t1;
+#
+# Bug #12888306 MISSING ROWS FOR SELECT >ALL (SUBQUERY WITHOUT ROWS)
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0);
+SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a);
+1
+1
+DROP TABLE t1;

=== modified file 'mysql-test/t/fulltext.test'
--- a/mysql-test/t/fulltext.test	2011-08-26 08:16:16 +0000
+++ b/mysql-test/t/fulltext.test	2012-04-20 13:01:26 +0000
@@ -623,7 +623,6 @@ PREPARE stmt FROM
  ON (MATCH(t1.f1) against (""))
  WHERE t1.f1 GROUP BY t1.f1))';
 
---echo # See BUG#12888306. Correct result is one row with value 1.
 EXECUTE stmt;
 EXECUTE stmt;
 

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2012-03-06 14:29:42 +0000
+++ b/mysql-test/t/group_by.test	2012-04-20 13:01:26 +0000
@@ -1995,3 +1995,12 @@ let $query= SELECT MIN(a), b FROM t1 WHE
 
 --echo
 DROP TABLE t1;
+
+--echo #
+--echo # Bug #12888306 MISSING ROWS FOR SELECT >ALL (SUBQUERY WITHOUT ROWS)
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0);
+SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a);
+DROP TABLE t1;

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2012-03-14 09:25:40 +0000
+++ b/sql/opt_sum.cc	2012-04-20 13:01:26 +0000
@@ -438,16 +438,22 @@ int opt_sum_query(THD *thd,
 	  }
           removed_tables|= table->map;
         }
-        else if (!expr->const_item() || !is_exact_count)
+        else if (!expr->const_item() || conds || !is_exact_count)
         {
           /*
-            The optimization is not applicable in both cases:
-            (a) 'expr' is a non-constant expression. Then we can't
-            replace 'expr' by a constant.
-            (b) 'expr' is a costant. According to ANSI, MIN/MAX must return
-            NULL if the query does not return any rows. Thus, if we are not
-            able to determine if the query returns any rows, we can't apply
-            the optimization and replace MIN/MAX with a constant.
+            We get here if the aggregate function is not based on a field.
+            Example: "SELECT MAX(1) FROM table ..."
+
+            This constant optimization is not applicable if
+            1. the expression is not constant, or
+            2. it is unknown if the query returns any rows. MIN/MAX must return
+               NULL if the query doesn't return any rows. We can't determine
+               this if:
+               - the query has a condition, because, in contrast to the
+                 "MAX(field)" case above, the condition will not be evaluated
+                 against an index for this case, or
+               - the storage engine does not provide exact count, which means
+                 that it doesn't know whether there are any rows.
           */
           const_result= 0;
           break;
@@ -463,6 +469,8 @@ int opt_sum_query(THD *thd,
         if (!count && !outer_tables)
         {
           item_sum->aggregator_clear();
+          // Mark the aggregated value as based on no rows
+          item->no_rows_in_result();
         }
         else
           item_sum->reset_and_add();

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (norvald.ryeng:3708 to 3709) Bug#12888306Norvald H. Ryeng20 Apr