MySQL Bugs: #19042: WITH ROLLUP changes NULL to values if duplicate column name is used

Bug #19042	WITH ROLLUP changes NULL to values if duplicate column name is used
Submitted:	12 Apr 2006 10:23	Modified:	4 Sep 2006 13:49
Reporter:	Giles McArdell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Linux (SUSE 10)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
If a second column is given the same name as a grouped column in a WITH ROLLUP query then the aggregate NULL value in the grouped column gets set to a value.

How to repeat:
/* Gives the number 4 in grouped column a on the aggregate row */
SELECT a, IFNULL(a,0) a, b, sum(c)
FROM ( SELECT 1 a, 2 b, 3 c UNION SELECT 4, 5, 6 ) t1
GROUP BY a, b WITH ROLLUP;

/* Gives the correct result */
SELECT a, IFNULL(a,0) a1, b, sum(c)
FROM ( SELECT 1 a, 2 b, 3 c UNION SELECT 4, 5, 6 ) t1
GROUP BY a, b WITH ROLLUP;

Suggested fix:
Do not use duplicate column names in ROLLUP Clauses (not really good practice anyway :).

Verified, uploading test case, see also bug #19049

mysqltest test case

Attachment: bug19042.tar.gz (application/x-gunzip, text), 778 bytes.

mysql> SELECT  a, IFNULL(a,0) a, b, sum(c) FROM ( SELECT 1 a, 2 b, 3 c UNION SELECT 4, 5, 6 ) t1 GROUP BY a, b WITH ROLLUP;
+---+------+---+--------+
| a | a    | b | sum(c) |
+---+------+---+--------+
| 1 |    1 | 2 |      3 | 
| 1 |    1 |   |      3 | 
| 4 |    4 | 5 |      6 | 
| 4 |    4 |   |      6 | 
| 4 |    0 |   |      9 | 
+---+------+---+--------+
5 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1052 | Column 'a' in group statement is ambiguous | 
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

In the manual we can find: 
http://dev.mysql.com/doc/refman/5.0/en/select.html
---------
When MySQL resolves an unqualified column or alias reference in an ORDER BY, GROUP BY, or HAVING clause, it first searches for the name in the select_expr values. If the name is not found, it looks in the columns of the tables named in the FROM clause.
---------
I.e. for grouping it will use a field with specified name only if no expression with specified name(alias) is found.

Thus the field 'a' became so-called 'hidden field':

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
For such fields:
---------
The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
---------

So this is not a bug.