From c04786d3e3d4fad53b46604ce37643f3ea4da1fa Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Apr 2012 12:59:17 +0300 Subject: Fix bug lp:985667, MDEV-229 Analysis: The reason for the wrong result is the interaction between constant optimization (in this case 1-row table) and subquery optimization. - First the outer query is optimized, and 'make_join_statistics' finds that table t2 has one row, reads that row, and marks the whole table as constant. This also means that all fields of t2 are constant. - Next, we optimize the subquery in the end of the outer 'make_join_statistics'. The field 'f2' is considered constant, with value '3'. The subquery predicate is rewritten as the constant TRUE. - The outer query execution detects early that the whole query result is empty and calls 'return_zero_rows'. Since the query is with implicit grouping, we have to produce one row with special values for the aggregates (depending on each aggregate function), and NULL values for all non-aggregate fields. This function calls 'no_rows_in_result' to set each aggregate function to the default value when it aggregates over an empty result, and then calls 'send_data', which in turn evaluates each Item in the SELECT list. - When evaluation reaches the subquery predicate, it executes the subquery with field 'f2' having a constant value '3', and the subquery produces the incorrect result '7'. Solution: Implement Item::no_rows_in_result for all subquery predicates. In order to make this work, it is also needed to make all val_* methods of all subquery predicates respect the Item_subselect::forced_const flag. Otherwise subqueries are executed anyways, and override the default value set by no_rows_in_result with whatever result is produced from the subquery evaluation. --- mysql-test/t/subselect.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t/subselect.test') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6e2b2ef86ae..be0663fc4cb 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5090,6 +5090,35 @@ SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 ); drop table t1; +--echo # +--echo # LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in +--echo # main query and implicit grouping +--echo # + +CREATE TABLE t1 (f1 int) engine=MyISAM; +INSERT INTO t1 VALUES (7),(8); + +CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM; +INSERT INTO t2 VALUES (3,'f'); + +EXPLAIN +SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; +SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; + +EXPLAIN +SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; +SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; + +EXPLAIN +SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; +SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; + +EXPLAIN +SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; +SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; + +drop table t1,t2; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; -- cgit v1.2.1