| 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: | |
| 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 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.
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;