MySQL Bugs: #19049: WITH ROLLUP Columns not included directly in SELECT mangled by DISTINCT

Bug #19049	WITH ROLLUP Columns not included directly in SELECT mangled by DISTINCT
Submitted:	12 Apr 2006 12:27	Modified:	27 Sep 2006 19:39
Reporter:	Giles McArdell	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (SUSE 10)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a column from a GROUP BY ... WITH ROLLUP clause is not explicitly included in the SELECT it does not get included in the ROLLUP.

How to repeat:
-- Column 'd' does not get included in the Rollup
SELECT distinct a, b, c, IFNULL(d,0) d, avg(e) g
FROM ( SELECT 1 a, 2 b, 3 c, 4 d, 5 e 
	UNION SELECT 8, 5, 4, 9, 2
	UNION SELECT 1, 2, 6, 4, 7 ) t1
GROUP BY a, b, c, d WITH ROLLUP;

Suggested fix:
Explicitly include all GROUP BY columns when using ROLLUP, IE:

SELECT distinct a, b, c, d, IFNULL(d,0) d1, avg(e) g
FROM ( SELECT NULL a, 2 b, 3 c, 4 d, 5 e 
	UNION SELECT 8, 5, 4, 9, 2
	UNION SELECT 1, 2, 6, 4, 7 ) t1
GROUP BY a, b, c, d WITH ROLLUP;

If you want what the original SELECT should have output:

SELECT a,b,c,d1 d,g FROM (
SELECT distinct a, b, c, d, IFNULL(d,0) d1, avg(e) g
FROM ( SELECT NULL a, 2 b, 3 c, 4 d, 5 e 
	UNION SELECT 8, 5, 4, 9, 2
	UNION SELECT 1, 2, 6, 4, 7 ) t1
GROUP BY a, b, c, d WITH ROLLUP) t2;

The exlusion of rows is due to DISTINCT triggering a different error which itself seems to replace NULL values for ROLLUP columns with their value from the preceeding row, i am uploading a simplified test case that will show the actual wrong behavior triggered by DISTINCT in combination with ROLLUP

mysqltest test case

Attachment: bug19049.tar.gz (application/x-gunzip, text), 982 bytes.

see also bug #19042

Duplicate of the bug#19042