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 <[email protected]> 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" <[email protected]> > > To: "Timour Katchaounov" <[email protected]> > > Cc: [email protected] > > 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: [email protected] > > > parent: [email protected] > > > fixes bug(s): https://launchpad.net/bugs/1008773 > > > committer: [email protected] > > > 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 > > > [email protected] > > > 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 : [email protected] > > 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 : [email protected] > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
