Bug #1085 Group_concat not compatable w/ min or max
Submitted: 18 Aug 2003 15:18 Modified: 15 Sep 2003 13:03
Reporter: Allen Morris Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:mysqld Ver 4.1.1-alpha OS:pc-linux on i686
Assigned to: Vasily Kishkin CPU Architecture:Any

[18 Aug 2003 15:18] Allen Morris 
Description:
If group_concat() is used w/ min() or max() the the min/max return NULL.

How to repeat:
create table t1 ( URL_ID int(11), URL varchar(80));
create table t2 ( REQ_ID int(11), URL_ID int(11));
insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
insert into t2 values (1,4), (5,4), (5,5);
# Make this order independent
--replace_result www.help.com X www.host.com X www.google.com X
select REQ_ID, Group_Concat(URL) as URL, Min(URL) urll, Max(URL) urlg
 from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;

The expected results are:
REQ_ID  URL     urll    urlg
1       X       X       X
5       X,X,X   X       X
But the acctual results are:
REQ_ID  URL     urll    urlg
1       X       NULL    NULL
5       X,X,X   NULL    NULL

Suggested fix:
Clueless
[18 Aug 2003 15:28] Allen Morris 
Note that the group_concate does not seem to affect only it's own column as I originally thought.

create table t1 ( URL_ID int(11), URL varchar(80));
create table t2 ( REQ_ID int(11), URL_ID int(11));
insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
insert into t2 values (1,4), (5,4), (5,5);
# Make this order independent
--replace_result www.help.com X www.host.com X www.google.com X
select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll, Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;

Also returns NULL for the min and max columns.
[18 Aug 2003 15:30] Allen Morris 
Changes Synopsis.
[18 Aug 2003 15:38] Allen Morris 
Group concat does not seem to affect sum even on the same column.
[19 Aug 2003 1:02] Ramil Kalimullin 
I've tested this on FreeBSD 4.7 using the latest 4.1 bk tree.
The query:

select REQ_ID, Group_Concat(URL) as URL, Min(URL) urll, Max(URL) urlg
 from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;

works properly, but the second query:

select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll, Max(t1.URL_ID)
urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;

kills mysqld.
[27 Aug 2003 16:23] Vasily Kishkin 
Can you try another request:
select REQ_ID, count(distinct URL) as URL, Min(URL) urll, Max(URL) urlg
from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;
What is result returned in this case ?
[28 Aug 2003 11:20] Allen Morris 
+ select REQ_ID, count(distinct URL) as URL, Min(URL) urll, Max(URL) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;
+ REQ_ID        URL     urll    urlg
+ 1     1       NULL    NULL
+ 5     3       NULL    NULL
[15 Sep 2003 13:03] Vasily Kishkin 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html