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

[14 Jan 2005 5: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 15: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 20: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 10: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 12: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 15: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
[21 Jun 2005 23: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>
[3 Feb 2017 17:09] Paul Dubois
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).