Bug #7894 IFNULL produces bad results WITH ROLLUP
Submitted: 14 Jan 2005 6:03 Modified: 22 Jun 2005 1:56
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.1.10 OS:
Assigned to: Igor Babaev Target Version:

[14 Jan 2005 6:03] Dean Ellis 
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.
[14 Jan 2005 16:42] Martin Friebe 
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".
[16 Jan 2005 21:09] Martin Friebe 
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
[30 May 2005 12:02] 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/25380
[30 May 2005 14:25] 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/25382
[31 May 2005 17:29] Igor Babaev 
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
[22 Jun 2005 1:56] Mike Hillyer 
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>