Bug #12095 GROUP_CONCAT ingores the DISTINCT modifier
Submitted: 21 Jul 2005 21:08 Modified: 8 Aug 2005 15:44
Reporter: kaz mccoy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.12-standard-log OS:Linux (linux)
Assigned to: Igor Babaev CPU Architecture:Any

[21 Jul 2005 21:08] kaz mccoy 
Description:
GROUP_CONCAT ingores the DISTINCT modifier when used in a query joining two tables and one of the tables only has one row.

How to repeat:
CREATE TABLE `a` (
  `aID` smallint(5) unsigned NOT NULL auto_increment,
  `sometitle` varchar(255) NOT NULL default '',
  `bID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`aID`),
  UNIQUE KEY `sometitle` (`sometitle`)
);

INSERT INTO a SET sometitle = 'title1', bID = 1;
INSERT INTO a SET sometitle = 'title2', bID = 1;

CREATE TABLE `b` (
  `bID` smallint(5) unsigned NOT NULL auto_increment,
  `somename` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`bID`),
  UNIQUE KEY `somename` (`somename`)
);

INSERT INTO b SET somename = 'test';

-- does not work --
SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a JOIN b ON a.bID = b.bID;

-- now add row to b --
INSERT INTO b SET somename = 'test2';

-- works --
SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a JOIN b ON a.bID = b.bID;

-- now delete the extra record --
DELETE FROM b WHERE bID = 2;

-- Now does not work again --
SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a JOIN b ON a.bID = b.bID;
[22 Jul 2005 4:18] Aleksey Kishkin 
Hi!
 What the results did you get? When I tested I got (with one or two records in the b table)

mysql> SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a
JOIN b
    -> ON a.bID = b.bID;
+----------+--------------------------------------------------+
| COUNT(*) | GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') |
+----------+--------------------------------------------------+
|        2 | test                                             |
+----------+--------------------------------------------------+
1 row in set (0.00 sec)

and it's proper result.
[22 Jul 2005 14:48] kaz mccoy 
when run with one row in b
mysql> SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a JOIN b ON a.bID = b.bID;
+----------+--------------------------------------------------+
| COUNT(*) | GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') |
+----------+--------------------------------------------------+
|        2 | test |test                                       |
+----------+--------------------------------------------------+

when run with two row in b
mysql> SELECT COUNT(*), GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') FROM a JOIN b ON a.bID = b.bID;
+----------+--------------------------------------------------+
| COUNT(*) | GROUP_CONCAT(DISTINCT b.somename SEPARATOR ' |') |
+----------+--------------------------------------------------+
|        2 | test                                             |
+----------+--------------------------------------------------+
1 row in set (0.00 sec)
[22 Jul 2005 15:03] kaz mccoy 
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 4.1.13-log |
+---------------+------------+
and 
mysql> show variables like 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 4.1.12-standard-log |
+---------------+---------------------+
1 row in set (0.00 sec)

on Redhat 9  and slackware 9.1
[22 Jul 2005 15:08] kaz mccoy 
Linux version 2.4.20-8smp (bhcompile@porky.devel.redhat.com) (gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)) #1 SMP Thu Mar 13 17:45:54 EST 2003

Linux version 2.4.22 (root@midas) (gcc version 3.2.3) #6 Tue Sep 2 17:43:01 PDT 2003
[23 Jul 2005 7:55] MySQL Verification Team 
Verified with 4.1.14-debug-log
[29 Jul 2005 2:11] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27706
[31 Jul 2005 9:32] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27749
[2 Aug 2005 18:59] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27806
[2 Aug 2005 20:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27815
[7 Aug 2005 3:58] Igor Babaev 
By mistake I placed the final fix for the bug into two changesets:
ChangeSet
  1.2372 05/08/02 11:58:52 igor@rurik.mysql.com +2 -0
  sql_select.cc, sql_class.h:
    Reversed the changes to fix bug #12095 after review
    done by SergeyG. Applied a fix suggested by him. 

ChangeSet
  1.2373 05/08/02 13:12:41 igor@rurik.mysql.com +1 -0
  item_sum.cc:
    Reversed the changes to fix bug #12095 after review
    done by SergeyG. Applied a fix suggested by him.
    Added my comment.

The fix will appear in 4.1.14 and 5.0.12.
[8 Aug 2005 15:44] Mike Hillyer 
Documented in 4.1.14 and 5.0.12 changelogs:

<listitem><para><literal>GROUP_CONCAT</literal> ignores the <literal>DISTINCT</literal> modifier when used in a query joining multiple
tables where one of the tables has a single row. (Bug #12095)</para></listitem>