Bug #20825 rollup puts non-equal values together
Submitted: 3 Jul 2006 15:46 Modified: 25 Oct 2006 19:24
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.20 and 5.0.22, 5.0.24-BK OS:Linux (Linux, freebsd)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[3 Jul 2006 15:46] Martin Friebe
Description:
This might be 2 bugs, but i could only produce them together. Also 2nd might be a consequence of 1st. So I report them together.

1) group by / rollup on a function(column), column result in null for function(column) in the rollup results for each value of function(collumn).
It should display the result of the column

2) there is a rollup accross different values for function(column)
N
ote the column is varchar, So using date-like text entries seems missleading. (I did first discover it with a date column)

sql to repeat:

create table d1 (a varchar(22) not null , b int);
insert into d1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10), ( "2006-07-02 00:00", 100),( "2006-07-02 00:30", 1000);
select left(a,10), a, sum(b) from d1 group by 1,2 with rollup;

drop table d1;

How to repeat:
mysql> create table d1 (a varchar(22) not null , b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into d1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10), ( "2006-07-02 00:00", 100),( "2006-07-02 00:30", 1000);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select left(a,10), a, sum(b) from d1 group by 1,2 with rollup;
+------------+------------------+--------+
| left(a,10) | a                | sum(b) |
+------------+------------------+--------+
| 2006-07-01 | 2006-07-01 21:30 |      1 |
| NULL       | NULL             |      1 |
| 2006-07-01 | 2006-07-01 23:30 |     10 |
| 2006-07-02 | 2006-07-02 00:00 |    100 |
| NULL       | NULL             |    110 | # bug2
| 2006-07-02 | 2006-07-02 00:30 |   1000 |
| NULL       | NULL             |   1000 |
| NULL       | NULL             |   1111 |
+------------+------------------+--------+
8 rows in set (0.00 sec)

# Note the line marked bug2:
# rollup includes 2 diff values for left(a,10) / this can be verified by  sum(b) = 100+10

mysql> drop table d1;
Query OK, 0 rows affected (0.01 sec)

Suggested fix:
-

as a workaround it is possible to make both group-by columsn a function:

select left(a,10), concat(a,""), sum(b) from d1 group by 1,2 with rollup;
[3 Jul 2006 16:20] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.24-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.24    |
+-----------+
1 row in set (0.00 sec)

mysql> create table d1 (a varchar(22) not null , b int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into d1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10), (
    -> "2006-07-02 00:00", 100),( "2006-07-02 00:30", 1000);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select left(a,10), a, sum(b) from d1 group by 1,2 with rollup;
+------------+------------------+--------+
| left(a,10) | a                | sum(b) |
+------------+------------------+--------+
| 2006-07-01 | 2006-07-01 21:30 |      1 |
| NULL       | NULL             |      1 |
| 2006-07-01 | 2006-07-01 23:30 |     10 |
| 2006-07-02 | 2006-07-02 00:00 |    100 |
| NULL       | NULL             |    110 |
| 2006-07-02 | 2006-07-02 00:30 |   1000 |
| NULL       | NULL             |   1000 |
| NULL       | NULL             |   1111 |
+------------+------------------+--------+
8 rows in set (0.07 sec)

mysql> select left(a,10), concat(a,''), sum(b) from d1 group by 1,2 with rollup;
+------------+------------------+--------+
| left(a,10) | concat(a,'')     | sum(b) |
+------------+------------------+--------+
| 2006-07-01 | 2006-07-01 21:30 |      1 |
| 2006-07-01 | 2006-07-01 23:30 |     10 |
| 2006-07-01 | NULL             |     11 |
| 2006-07-02 | 2006-07-02 00:00 |    100 |
| 2006-07-02 | 2006-07-02 00:30 |   1000 |
| 2006-07-02 | NULL             |   1100 |
| NULL       | NULL             |   1111 |
+------------+------------------+--------+
7 rows in set (0.00 sec)
[15 Sep 2006 16:04] 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/commits/12049

ChangeSet@1.2253, 2006-09-15 20:03:49+04:00, evgen@moonbone.local +3 -0
  Fixed bug#20825: rollup puts non-equal values together
  
  Fix for bug 7894 replaces a field in a non-aggregate function with a item
  reference if such a field was specified in the GROUP BY clause in order to
  get a correct result.
  When ROLLUP is involved this lead to a wrong result due to value of a such
  field is got through a copy function and copying happens after the function
  evaluation.
  Such replacement isn't needed if that field is also specified in the select
  list.
  
  The change_group_ref() function now doesn't substitute a field with a
  reference if the field is specified in the select list.
[28 Sep 2006 19:34] 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/commits/12769

ChangeSet@1.2553, 2006-09-28 23:34:24+04:00, evgen@moonbone.local +3 -0
  Fixed bug#20825: rollup puts non-equal values together
  
  Fix for bug 7894 replaces a field(s) in a non-aggregate function with a item
  reference if such a field was specified in the GROUP BY clause in order to
  get a correct result.
  When ROLLUP is involved this lead to a wrong result due to value of a such
  field is got through a copy function and copying happens after the function
  evaluation.
  Such replacement isn't needed if grouping is also done by such a function.
  
  The change_group_ref() function now isn't called for a function present in
  the group list.
[29 Sep 2006 16: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/commits/12854

ChangeSet@1.2553, 2006-09-29 20:02:53+04:00, evgen@moonbone.local +3 -0
  Fixed bug#20825: rollup puts non-equal values together
  
  Fix for bug 7894 replaces a field(s) in a non-aggregate function with a item
  reference if such a field was specified in the GROUP BY clause in order to
  get a correct result.
  When ROLLUP is involved this lead to a wrong result due to value of a such
  field is got through a copy function and copying happens after the function
  evaluation.
  Such replacement isn't needed if grouping is also done by such a function.
  
  The change_group_ref() function now isn't called for a function present in
  the group list.
[21 Oct 2006 9:09] Georgi Kodinov
Pushed in 4.1.22/5.0.27/5.1.13-beta
[25 Oct 2006 19:24] Paul Dubois
Noted in 4.1.22, 5.0.30, 5.1.13 changelogs.