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
