maria-developers team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxx>]

Hi Sergei, Timour

As per "SQL-99 Complete, Really" book by Gulutzan&Pelzer which many of us have on our bookselves as they're dear old colleagues, the story on MIN/MAX (NULL if there are no values or no non-NULL values) and COUNT (0 for no rows) is correct.
As per SQL standard, if aggregate functions are used, then a selected column must either be an aggregate or in the group list, otherwise a syntax error would be returned on parsing.
By default MySQL server allows non-aggregate non-grouped columns to be in the select list, but it behaves in the SQL standard way if the sql_mode ONLY_FULL_GROUP_BY is used and indeed then it would chuck a syntax error just like PostgreSQL and other SQL RDBMS would.

>From my reading of the standard, I see no particular reason for an implicit grouping to behave any different.
If ONLY_FULL_GROUP_BY is set, then only constants and aggregates would be allowed, no loose column names.
If that flag is not set, then indeed the behaviour is non-standard but it has MySQL history and thus should not be changed. Since there is no foundation in the standard, there is no reason for changing the behaviour that some apps may rely upon. So your point of reference there would be the behaviour of all earlier MySQL versions and a documented test case should be present to ensure that this behaviour continues into the future.

Timour's email makes no sense to me in terms of the PostgreSQL reference. PostgreSQL should chuck a syntax error for this scenario, it would never get to query execution.

Cheers,
Arjen.


----- Original Message -----
> From: "Sergei Petrunia" <psergey@xxxxxxxxxxxx>
> To: "Timour Katchaounov" <timour@xxxxxxxxxxxxxxxx>
> Cc: maria-developers@xxxxxxxxxxxxxxxxxxx
> 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@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
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help : https://help.launchpad.net/ListHelp

-- 
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery