maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04755
Re: Please review: [Commits] Rev 3538: Fix bug lp:1008773 in file:///home/tsk/mprog/src/5.3-lpb1008773/
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@xxxxxxxxxxxx-20120605124124-akdmgdmr01h10s2w
> parent: psergey@xxxxxxxxxxxx-20120602121305-mhw0u2zppjk0erhk
> fixes bug(s): https://launchpad.net/bugs/1008773
> committer: timour@xxxxxxxxxxxx
> 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@xxxxxxxxxxx
> 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
Follow ups
