Bug #4535 "group_concat AND order by " can't work correctly

• Submitted: 13 Jul 2004 9:27
• Modified: 12 Aug 2004 10:45
• Reporter: tony chen
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.3
• OS: Linux (linux)
• Assigned to: Bugs System
• CPU Architecture: Any

[13 Jul 2004 9:27] tony chen

Description:
There are two table as below:
---------------------------------------------------------------
CREATE TABLE AAA (
	A_ID INT NOT NULL,
	A_DESC   CHAR(3) NOT NULL,
	PRIMARY KEY (A_ID)
)

INSERT INTO AAA (1,'ABC'), (2,'EFG'), (3,'HIJ')

CREATE TABLE BBB (
	A_ID INT NOT NULL,
	B_DESC   CHAR(3) NOT NULL,
	PRIMARY KEY (A_ID,B_DESC)
)

INSERT INTO BBB (1,'A'),(1,'B'),(3,'F')
---------------------------------------------------------------------
"group_concat" and "order by" can't work correctly.

example 1: (CORRECT RESULT)
SELECT AAA.A_ID, GROUP_CONCAT(BBB.B_DESC) AS B_DESC 
FROM AAA
LEFT JOIN BBB ON AAA.A_ID=BBB.A_ID 
GROUP BY AAA.A_ID

A_ID                     B_DESC
---------------------------------
1                          A,B
2                          (NULL)
3                          F
---------------------------------
example 2: (ERROR RESULT)
SELECT AAA.A_ID, GROUP_CONCAT(BBB.B_DESC) AS B_DESC 
FROM AAA
LEFT JOIN BBB ON AAA.A_ID=BBB.A_ID 
GROUP BY AAA.A_ID 
ORDER BY AAA.A_DESC

A_ID                     B_DESC
---------------------------------
1                          A,B
2                          A,B
3                          F
---------------------------------

How to repeat:
''

[13 Jul 2004 11:24] Hartmut Holzgraefe

Verified on both 4.1.3 and 5.0-bk

[12 Aug 2004 10:45] 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

Additional info:

Error was found and fixed. The problem was with maybe_null variable.