List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 24 2011 1:21pm
Subject:Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294
View as plain text  
Hello,

Roy Lyseng a écrit, Le 22.06.2011 16:59:
> Hi Guilhem,
> 
> this bugfix is approved, I have just a comment comment below.

>> === modified file 'mysql-test/include/subquery_mat.inc'
>> --- a/mysql-test/include/subquery_mat.inc    2011-04-01 11:06:48 +0000
>> +++ b/mysql-test/include/subquery_mat.inc    2011-06-20 14:29:59 +0000
>> @@ -882,3 +882,32 @@ DROP TABLE t1, t2;
>>   DROP VIEW v3;
>>
>>   --echo # End Bug#11852644
>> +
>> +--echo
>> +--echo # Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW
>> +--echo # INSTEAD OF NULL WHEN MATERIALIZATION ON
>> +--echo
>> +
>> +CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY;
>> +CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY;
>> +INSERT INTO t2 VALUES (8),(7);
>> +CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY;
>> +INSERT INTO t3 VALUES (7);
>> +
>> +SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3
>> +FROM t3
>> +     LEFT JOIN t1
>> +     ON t1.col_int_nokey
>> +WHERE (194, 200) IN (
>> +                     SELECT SQ4_alias1.col_int_nokey,
>> +                     SQ4_alias2.col_int_nokey
>> +                     FROM t2 AS SQ4_alias1
>> +                          JOIN
>> +                          t2 AS SQ4_alias2
>> +                          ON SQ4_alias2.col_int_nokey = 5
>> +                    )
>> +GROUP BY field3 ;
> 
> t1.col_int_nokey in the SELECT list makes the query non-deterministic 
> (even though this is not a big issue for a single-row query).

I think it's deterministic because there is GROUP BY field3, and field3 
is the alias for t1.col_int_nokey.
Order of rows in result may not be deterministic, but let's not care, 
it's just one row.

> Is it 
> possible to remove that column and still have a viable test case?

>> @@ -2029,10 +2029,16 @@ public:
>>     void clear();
>>     bool save_join_tab();
>>     bool init_save_join_tab();
>> +  /**
>> +     If there is an aggregate function (sum_func_count!=0), and no 
>> GROUP BY,
>> +     and the HAVING clause evaluates to "true", we should send a row 
>> even if
>> +     the join contains no rows. This is what the standard requires.
>> +   */
> Suggestion: Structure the comment as follows:
> 
> /**
>   Send a row even if the join produced no rows if:
>    -  there is an aggregate function (sum_func_count!=0), and
>    - the query is not grouped, and
>    - a possible HAVING clause evaluates to TRUE.
> */

done

> I am not completely sure about the standards compliance, but I am unable 
> to find a test case that causes an error. In SQL-2008, there is this 
> syntax: "GROUP BY ()" which creates one group with 0 or more rows in it. 
> An aggregate function without an explicit GROUP BY implies GROUP BY (). 
> A HAVING clause without a preceding GROUP BY also implies GROUP BY ().
> 
> MySQL does not support GROUP BY (), but the construct is merely a 
> formalized version of the implicit grouping found in earlier SQL standards.
> 
> This will cause the select statement:
> SELECT 1 FROM t WHERE <impossible-where> HAVING COUNT(*) = 0;
> to report a row, and indeed it does. However, the execution does not 
> reach JOIN::send_row_on_empty_set(), so I am not quite sure how that row 
> is generated...
> 
> Beware also the MySQL extension that HAVING without a set function is 
> equivalent to a WHERE...

Uh, well... I think that the rule expressed in the comment is sound 
enough for most queries... I must admit I'm lost in subtleties... 
Regarding HAVING, my fix isn't touching HAVING, so...

>>     bool send_row_on_empty_set()
>>     {
>>       return (do_send_rows&&  tmp_table_param.sum_func_count !=
> 0&&
>> -        !group_list&&  select_lex->having_value !=
> Item::COND_FALSE);
>> +        !(group_list != NULL || group_optimized_away)&&
>> +            select_lex->having_value != Item::COND_FALSE);
>>     }
>>     bool change_result(select_result *result);
>>     bool is_top_level_join() const
> 
> Thanks,
> Roy


-- 
Mr. Guilhem Bichot <guilhem.bichot@stripped>
Oracle / MySQL / Optimizer team, Lead Software Engineer
Bordeaux, France
www.oracle.com / www.mysql.com
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Guilhem Bichot20 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Roy Lyseng23 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Guilhem Bichot25 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Jorgen Loland23 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Guilhem Bichot25 Jun
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3386) Bug#12668294Roy Lyseng25 Jun