| Bug #30874 | rollup doesn't work with substr | ||
|---|---|---|---|
| Submitted: | 6 Sep 2007 18:12 | Modified: | 7 Sep 2007 14:58 |
| Reporter: | Brian Duggan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.26 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | rollup | ||
[6 Sep 2007 21:20]
MySQL Verification Team
Thank you for the bug report. Your server version is pretty older please
upgrade or wait the next release. I can't repeat with current source server:
[miguel@skybr 5.0]$ bin/mysqladmin -uroot create db9
[miguel@skybr 5.0]$ bin/mysql -uroot db9
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.50-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table foobar ( something varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into foobar values ('aaa'),('aab'),('aac'),('bbb');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> select substr(something,1,1),
-> something,
-> count(1) from foobar
-> group by 1,2 with rollup;
+-----------------------+-----------+----------+
| substr(something,1,1) | something | count(1) |
+-----------------------+-----------+----------+
| a | aaa | 1 |
| a | aab | 1 |
| a | aac | 1 |
| a | NULL | 3 |
| b | bbb | 1 |
| b | NULL | 1 |
| NULL | NULL | 4 |
+-----------------------+-----------+----------+
7 rows in set (0.00 sec)
mysql>
[7 Sep 2007 14:58]
Brian Duggan
Okay, looks like this was fixed in release 0.33 : http://bugs.mysql.com/bug.php?id=20825 thanks.
Description: Rollup reports don't seem to work properly when grouping by a column that's a substr. create table foobar ( something varchar(20)); insert into foobar values ('aaa'),('aab'),('aac'),('bbb'); select substr(something,1,1), something, count(1) from foobar group by 1,2 with rollup; +-----------------------+-----------+----------+ | substr(something,1,1) | something | count(1) | +-----------------------+-----------+----------+ | a | aaa | 1 | | NULL | NULL | 1 | | a | aab | 1 | | a | aac | 1 | | b | bbb | 1 | | NULL | NULL | 3 | | NULL | NULL | 4 | +-----------------------+-----------+----------+ This output is wrong. Instead, the output should look like this : +------------+-----------+----------+ | a | aaa | 1 | | a | aab | 1 | | a | aac | 1 | | a | NULL | 3 | | b | bbb | 1 | | b | NULL | 1 | | NULL | NULL | 4 | +------------+-----------+----------+ How to repeat: create table foobar ( something varchar(20)); insert into foobar values ('aaa'),('aab'),('aac'),('bbb'); select substr(something,1,1), something, count(1) from foobar group by 1,2 with rollup; Suggested fix: Dunno, but a workaround is to use a subquery : select s.someletter, s.something, count(1) from (select substr(something,1,1) as someletter, something from foobar ) s group by 1,2 with rollup; +------------+-----------+----------+ | someletter | something | count(1) | +------------+-----------+----------+ | a | aaa | 1 | | a | aab | 1 | | a | aac | 1 | | a | NULL | 3 | | b | bbb | 1 | | b | NULL | 1 | | NULL | NULL | 4 | +------------+-----------+----------+