maria-developers team mailing list archive

[Pantelis Theodosiou <ypercube@xxxxxxxxx>]

Hi Arjen,

I'd like to add that SQL standard is not static. I may be wrong but my
interpretation on the recent versions is that (your quote):

> 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.

is correct only up to SQL-99 standard. There was a relevant discussion in
SO:   Why does MySQL add a feature that conflicts with SQL
standards?<http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards>

Pantelis Theodosiou

<http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards>
On Mon, Jun 18, 2012 at 2:59 AM, Arjen Lentz <arjen@xxxxxxxxxxxxx> wrote:

> 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
>
>
> _______________________________________________
> 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
>