MySQL Bugs: #7894: IFNULL produces bad results WITH ROLLUP

Bug #7894	IFNULL produces bad results WITH ROLLUP
Submitted:	14 Jan 2005 5:03	Modified:	21 Jun 2005 23:56
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
Using IFNULL() or COALESCE() (and other NULL-detecting functions) in a WITH ROLLUP query causes the result set to contain bad values for the total/subtotal rows (ie: the NULLs are replaced with the previous non-NULL value in the column).

How to repeat:
SELECT a FROM ( SELECT 1 a ) t1 GROUP BY a WITH ROLLUP;
SELECT COALESCE(a, 'TEST') FROM ( SELECT 1 a ) t1 GROUP BY a WITH ROLLUP;

SELECT a FROM ( SELECT 1 a UNION SELECT 2 ) t1 GROUP BY a WITH ROLLUP;
SELECT IFNULL(a, 'TEST') FROM ( SELECT 1 a UNION SELECT 2 ) t1 GROUP BY a WITH ROLLUP;

SELECT a, b FROM ( SELECT 1 a, 2 b UNION SELECT 3, 4 ) t1 GROUP BY a, b WITH ROLLUP;
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM ( SELECT 1 a, 2 b UNION SELECT 3, 4 ) t1 GROUP BY a, b WITH ROLLUP;

Suggested fix:
Should remain NULL in the final output.

the error is in the query:
#group by a,b

the result is groubed by a and b, not by the result of the function, the columns with the result are not grouped nor aggregate, so they are ignored, by rollup.

better visible in the following query

SELECT a, "fix"
FROM ( SELECT 1 a, 2 b UNION SELECT 3, 4 ) t1
GROUP BY a, b WITH ROLLUP;

nothing groups on "fix".

Sorry, I have been wrong in my last comment, but then it does have nothing do to with the IFNULL function

select a, a+1 , sum(b) from (select 1 a, 2 b) t group by a with rollup;

a is null, but a+1 = 2

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/25380

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/25382

ChangeSet
  1.2301 05/05/30 03:01:51 igor@rurik.mysql.com +3 -0
  olap.result, olap.test:
    Added test cases for bug #7894.
  sql_select.cc:
    Fixed bug #7894: GROUP BY queries with ROLLUP returned
    wrong results for expressions containing group by columns.
    The fix ensured correct results by replacement of all
    occurrences of group by fields in non-aggregate expressions
    for corresponding ref objects and preventing creation of
    fields in temporary tables for expression containing group
    by fields.

The fix will appear in 4.1.13 and 5.0.7

Documented in 4.1.13 and 5.0.7 changelogs:

<listitem><para>GROUP BY queries with ROLLUP returned
    wrong results for expressions containing group by columns. (Bug #7894)</para></listitem>

Revised changelog entry:

Queries with WITH ROLLUP returned incorrect results for expressions
containing GROUP BY columns. As a result of this fix, it is now
possible to test for NULL values in super-aggregate rows in the
select list or the HAVING clause (for example, HAVING col_name IS
NULL).