From d2bbf288a93ee50829e09d8faaf26dc0d6125476 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 12 Jul 2006 01:52:18 +0400 Subject: Fixed bug#18503: Queries with a quantified subquery returning empty set may return a wrong result. An Item_sum_hybrid object has the was_values flag which indicates whether any values were added to the sum function. By default it is set to true and reset to false on any no_rows_in_result() call. This method is called only in return_zero_rows() function. An ALL/ANY subquery can be optimized by MIN/MAX optimization. The was_values flag is used to indicate whether the subquery has returned at least one row. This bug occurs because return_zero_rows() is called only when we know that the select will return zero rows before starting any scans but often such information is not known. In the reported case the return_zero_rows() function is not called and the was_values flag is not reset to false and yet the subquery return no rows Item_func_not_all and Item_func_nop_all functions return a wrong comparison result. The end_send_group() function now calls no_rows_in_result() for each item in the fields_list if there is no rows were found for the (sub)query. mysql-test/t/subselect.test: Added test case for bug#18503: Queries with a quantified subquery returning empty set may return a wrong result. mysql-test/r/subselect.result: Added test case for bug#18503: Queries with a quantified subquery returning empty set may return a wrong result. sql/sql_select.cc: Fixed bug#18503: Queries with a quantified subquery returning empty set may return a wrong result. The end_send_group() function now calls no_rows_in_result() for each item in the fields_list if there is no matching rows were found. --- mysql-test/r/subselect.result | 15 +++++++++++++++ mysql-test/t/subselect.test | 14 ++++++++++++++ 2 files changed, 29 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 7925715a8b7..2e2e28365d7 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2835,3 +2835,18 @@ a 4 DROP TABLE t1,t2,t3; purge master logs before (select adddate(current_timestamp(), interval -4 day)); +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2); +f1 +1 +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0); +f1 +1 +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0); +f1 +1 +DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index cbc7a3afb5f..1012458a31b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1820,4 +1820,18 @@ DROP TABLE t1,t2,t3; purge master logs before (select adddate(current_timestamp(), interval -4 day)); + +# +# Bug#18503: Queries with a quantified subquery returning empty set may +# return a wrong result. +# +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0); +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0); +DROP TABLE t1, t2; # End of 4.1 tests -- cgit v1.2.1