MySQL Bugs: #7914: rollup modifies original result (set to null) with aggrecate insifde function

Bug #7914	rollup modifies original result (set to null) with aggrecate insifde function
Submitted:	14 Jan 2005 17:46	Modified:	23 May 2005 2:22
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	-
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
a colum containing an aggregate function as part of a function, can be set to 0, if rollup is used.

How to repeat:
select a,  sum(a), sum(a)+1  from (select 1 a union select 2  ) d group by a ; +---+--------+----------+
| a | sum(a) | sum(a)+1 |
+---+--------+----------+
| 1 |      1 |        2 |
| 2 |      2 |        3 |
+---+--------+----------+

# but
select a,  sum(a), sum(a)+1  from (select 1 a union select 2  ) d group by a with rollup;
+---+--------+----------+
| a | sum(a) | sum(a)+1 |
+---+--------+----------+
| 1 |      1 |        2 |
| 2 |      2 |     NULL |
|NULL |      3 |        4 |
+---+--------+----------+

# sum(a)+1 should still be 3, group concat should only insert rows, not modify existing rows.
#the rollup is correct (as far as I can judge), I am expecting 4, because this is the rollup for the sum(which is 3) plus 1;

# this works
select a,  sum(a), sum(a+1)  from (select 1 a union select 2  ) d group by a with rollup;
+---+--------+----------+
| a | sum(a) | sum(a+1) |
+---+--------+----------+
| 1 |      1 |        2 |
| 2 |      2 |        3 |
|NULL |      3 |        5 |
+---+--------+----------+

# more non working examples; they all are fine without the rollup
select a,  sum(a), sum(a)+1, concat(sum(a),'x'), sum(a)+sum(a), sum(a) from (select 1 a,2 b union select 2 ,3 union select 5,6 ) d group by a with rollup;
+---+--------+----------+--------------------+---------------+--------+
| a | sum(a) | sum(a)+1 | concat(sum(a),'x') | sum(a)+sum(a) | sum(a) |
+---+--------+----------+--------------------+---------------+--------+
| 1 |      1 |        2 | 1x                 |             2 |      1 |
| 2 |      2 |     NULL | NULL               |          NULL |      2 |
| 5 |      5 |     NULL | NULL               |          NULL |      5 |
|NULL |      8 |        9 | 8x                 |            16 |      8 |
+---+--------+----------+--------------------+---------------+--------+
# all of the values in the rollup line are fine, just the individual lines a nulled

Suggested fix:
-

applies to functions without aggregate too:
 select a, 2, 1+1, "X", concat("X","C"), database() from (select 1 a union select 2) t group by a with rollup;
+---+---+-----+---+-----------------+------------+
| a | 2 | 1+1 | X | concat("X","C") | database() |
+---+---+-----+---+-----------------+------------+
| 1 | 2 |   0 | X |                 | NULL       |
| 2 | 2 |   0 | X |                 | NULL       |
|NULL | 2 |   0 | X |                 | NULL       |
+---+---+-----+---+-----------------+------------+

fixed values are fine, calculations like 1+1 or concat are not

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

This bug was due to the fact that this->set_items_ref_array(...) was called instead of curr_join->set_items_ref_array(...).

ChangeSet
  1.2279 05/05/18 05:15:48 igor@rurik.mysql.com +3 -0
  olap.result, olap.test:
    Added test cases for bug #7914.
  sql_select.cc:
    Fixed bug #7914: rollup over expresssions such as sum(a)+1.

The fix will appear in releases 4.1.13 and 5.0.7.

Noted in 4.1.13, 5.0.7 changelogs.