Hi Timour, Ok to push. On Tue, Jun 05, 2012 at 04:20:35PM +0300, Timour Katchaounov wrote:
Sergey,
Could you please review yet another rather trivial fix. The only thing here is whether the semantics is correct. As the bug report says, all MariaDB/MySQL versions (except mariadb 5.3/5.5), and Postgresql return the value of the constant as part of the empty row produced when we have implicit grouping. I didn't verify this in the SQL standard, because AFAIR this is non-standard.
Timour
------------------------------------------------------------ revno: 3538 revision-id: timour@askmonty.org-20120605124124-akdmgdmr01h10s2w parent: psergey@askmonty.org-20120602121305-mhw0u2zppjk0erhk fixes bug(s): https://launchpad.net/bugs/1008773 committer: timour@askmonty.org branch nick: 5.3-lpb1008773 timestamp: Tue 2012-06-05 15:41:24 +0300 message: Fix bug lp:1008773
Analysis: Queries with implicit grouping (there is aggregate, but no group by) follow some non-obvious semantics in the case of empty result set. Aggregate functions produce some special "natural" value depending on the function. For instance MIN/MAX return NULL, COUNT returns 0.
The complexity comes from non-aggregate expressions in the select list. If the non-aggregate expression is a constant, it can be computed, so we should return its value, however if the expression is non-constant, and depends on columns from the empty result set, then the only meaningful value is NULL.
The cause of the wrong result was that for subqueries the optimizer didn't make a difference between constant and non-constant ones in the case of empty result for implicit grouping.
Solution: In all implementations of Item_subselect::no_rows_in_result() check if the subquery predicate is constant. If it is constant, do not set it to the default value for implicit grouping, instead let it be evaluated.
=== modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2012-02-28 14:41:55 +0000 +++ b/mysql-test/r/subselect4.result 2012-06-05 12:41:24 +0000 @@ -2124,6 +2124,61 @@ ERROR 21000: Subquery returns more than DROP TABLE t2; ERROR 42S02: Unknown table 't2' DROP TABLE t1; +# +# LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; +MAX(a) bb +NULL NULL +EXPLAIN +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL NULL +EXPLAIN +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL NULL +drop table t1, t2; set optimizer_switch=@subselect4_tmp; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size;
=== modified file 'mysql-test/t/subselect4.test' --- a/mysql-test/t/subselect4.test 2012-02-28 14:41:55 +0000 +++ b/mysql-test/t/subselect4.test 2012-06-05 12:41:24 +0000 @@ -1761,6 +1761,40 @@ CREATE TABLE t2 AS DROP TABLE t2; DROP TABLE t1;
+--echo # +--echo # LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; + +EXPLAIN +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; + +EXPLAIN +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; + + +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; + +EXPLAIN +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; + +EXPLAIN +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; + +drop table t1, t2;
set optimizer_switch=@subselect4_tmp;
=== modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2012-05-22 12:22:55 +0000 +++ b/sql/item_subselect.cc 2012-06-05 12:41:24 +0000 @@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String
void Item_maxmin_subselect::no_rows_in_result() { + if (const_item()) + return; value= Item_cache::get_cache(new Item_null()); null_value= 0; was_values= 0; @@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_r
void Item_singlerow_subselect::no_rows_in_result() { + if (const_item()) + return; value= Item_cache::get_cache(new Item_null()); reset(); make_const(); @@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_
void Item_exists_subselect::no_rows_in_result() { + if (const_item()) + return; value= 0; null_value= 0; make_const(); @@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String
void Item_allany_subselect::no_rows_in_result() { + if (const_item()) + return; value= 0; null_value= 0; was_null= 0;
_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog