Bug #31063 GROUP BY WITH ROLLUP inconsistence with derived tables
Submitted: 17 Sep 2007 15:58 Modified: 18 Sep 2007 11:34
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.21 OS:Any
Assigned to: CPU Architecture:Any

[17 Sep 2007 15:58] Peter Zaitsev
Description:
If column declared NOT NULL   we can see empty column value with direct 
select (which is probbaly wrong) while it changes to NULL if we wrap it as derived table:

Note result in standard wuery - i is int so "" is not valid value for this data type at all.

How to repeat:
mysql> create table t(i int unsigned not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select i,count(*) from t group by i with rollup;
+---+----------+
| i | count(*) |
+---+----------+
| 1 |        1 | 
|   |        1 | 
+---+----------+
2 rows in set (0.00 sec)

mysql> select * from (select i,count(*) from t group by i with rollup) t;
+------+----------+
| i    | count(*) |
+------+----------+
|    1 |        1 | 
| NULL |        1 | 
+------+----------+
2 rows in set (0.00 sec)
[17 Sep 2007 16:09] MySQL Verification Team
Thank you for the bug report. I wasn't able to repeat with source server:

[miguel@skybr 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(i int unsigned not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select i,count(*) from t group by i with rollup;
+---+----------+
| i | count(*) |
+---+----------+
| 1 |        1 | 
| NULL |        1 | 
+---+----------+
2 rows in set (0.00 sec)

mysql> select * from (select i,count(*) from t group by i with rollup) t;
+------+----------+
| i    | count(*) |
+------+----------+
|    1 |        1 | 
| NULL |        1 | 
+------+----------+
2 rows in set (0.00 sec)

mysql>
[17 Sep 2007 21:10] Peter Zaitsev
I will check tomorrow.

either it is fixed in recent 5.1.x  or it is related to older version of MySQL client.
[18 Sep 2007 11:34] Peter Zaitsev
Just checked

This happens if I'm using 5.0.22 client - so it may be the issue with earlier client or client libraries.