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)
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.21
• OS: Any
• 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.