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
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@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
-- 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
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@openquery.com> 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@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
-- 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
participants (3)
-
Arjen Lentz -
Pantelis Theodosiou -
Sergei Petrunia