Hi Sergey,
Sergey Petrunia wrote:
> Hi,
>
> On Thu, Apr 17, 2008 at 11:18:50AM +0400, Alexey Kopytov wrote:
>> diff -Nrup a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
>> --- a/mysql-test/t/func_gconcat.test 2008-02-28 14:31:18 +03:00
>> +++ b/mysql-test/t/func_gconcat.test 2008-04-17 11:18:45 +04:00
>> @@ -657,4 +657,40 @@ SELECT s1.d1 FROM
>> ) AS s1;
>> DROP TABLE t1;
>>
>> +#
>> +# Bug #35298: GROUP_CONCAT with DISTINCT can crash the server
>> +#
>> +
>> +CREATE TABLE t1 (a INT);
>> +CREATE TABLE t2 (a INT);
>> +
>> +INSERT INTO t1 VALUES(1);
>> +
>> +SELECT GROUP_CONCAT(DISTINCT t2.a) FROM t1 LEFT JOIN t2 ON t2.a = t1.a GROUP BY
> t1.a;
>> +
>> +DROP TABLE t1, t2;
>> +
>> +#
>> +# Bug #36024: group_concat distinct in subquery crash
>> +#
>> +
>> +CREATE TABLE t1 (a INT);
>> +CREATE TABLE t2 (b INT);
>> +
>> +INSERT INTO t1 VALUES (NULL), (8), (2);
>> +INSERT INTO t2 VALUES (4), (10);
>> +
>> +SELECT 1 FROM t1 WHERE t1.a NOT IN
>> +(
>> + SELECT GROUP_CONCAT(DISTINCT t1.a)
>> + FROM t1 WHERE t1.a IN
>> + (
>> + SELECT b FROM t2
>> + )
>> + AND NOT t1.a >= (SELECT t1.a FROM t1 LIMIT 1)
>> + GROUP BY t1.a
>> +);
>> +
>> +DROP TABLE t1, t2;
>> +
>> --echo End of 5.0 tests
> The commited testcase doesn't demonstrate the problem, for it we have
>
> distinct!=NULL && unique!=NULL
>
> at all times. Please use the test case provided by the bug reporter. it
> has COUNT(DISTINCT const_column_that_is_always_null) for which we really
> can have (distinct=TRUE && unique_filter==NULL)
>
> Ok to push after the above is addressed.
>
> BR
> Sergey
I just omitted KEY specification from t1 in the second test case for
brevity. Adding it back leads to unique == NULL with a non-zero
distinct. Good catch!
Thanks and regards,
--
Alexey Kopytov, Software Developer
MySQL AB, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
