----- Original Message -----
> From: "Sergei Petrunia" <
psergey@askmonty.org>
> To: "Timour Katchaounov" <
timour@montyprogram.com>
> Cc:
maria-developers@lists.launchpad.net
> Sent: Thursday, 14 June, 2012 10:50:41 PM
> Subject: Re: [Maria-developers] 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@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
>
> _______________________________________________
> Mailing list:
https://launchpad.net/~maria-developers
> Post to :
maria-developers@lists.launchpad.net
> Unsubscribe :
https://launchpad.net/~maria-developers
> More help :
https://help.launchpad.net/ListHelp