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#12888306 | Norvald H. Ryeng | 20 Apr |
