MySQL Bugs: #46989: NULL attribute gets lost when "CREATE TABLE AS SELECT ... GROUP BY WITH ROLLUP"

Bug #46989	NULL attribute gets lost when "CREATE TABLE AS SELECT ... GROUP BY WITH ROLLUP"
Submitted:	29 Aug 2009 5:52	Modified:	29 Aug 2009 6:09
Reporter:	Alexander Barkov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1,5.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
"CREATE TABLE AS SELECT..GROUP BY WITH ROLLUP" copies NOT NULL
attributes from the original columns.

This does not look correct, wrong because ROLLUP creates
extra summary rows (higher-level or super-aggregate) with NULL values.

This bug seems to be related to 
Bug #12885 (Closed): rollup / subquery and creat table, causes incorrect cannot be null

which is reported as "closed". However, I still can see this behaviour in 5.4.

How to repeat:
drop table if exists t1, t2;

-- create the data source table
mysql> create table t1 (runid varchar(10) not null, recall double not null);
mysql> insert into t1 values ('xxxx-1',0.1),('xxxx-2',0.2),('xxxx-1',0.3);

-- run a ROLLUP query and notice some NULL values in the output:

mysql> select runid, avg(recall) as recall from t1 group by runid with rollup;

+--------+---------------------+
| runid  | recall              |
+--------+---------------------+
| xxxx-1 |                 0.2 | 
| xxxx-2 |                 0.2 | 
| NULL   | 0.20000000000000004 | 
+--------+---------------------+
3 rows in set (0.00 sec)

-- Now create a table from the same ROLLUP query:

mysql> create table t2 as select runid, avg(recall) as recall from t1 group by runid with rollup;

Query OK, 3 rows affected, 1 warning (0.00 sec)

-- Hmm, what warning?...
mysql> show warnings;

+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'runid' cannot be null | 
+---------+------+-------------------------------+

mysql> show create table t1;
+-------+-------------------------+
| Table | Create Table            |
+-------+-------------------------+
| t1    | CREATE TABLE `t1` (
  `runid` varchar(10) NOT NULL,
  `recall` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------+

`runid` was created with NOT NULL attribute.

Suggested fix:
Remove the "NOT NULL" flags from the columns listed in GROUP BY when 
running a ROLLUP query.

Thank you for the bug report. Verified as described.

c:\dbs>c:\dbs\5.4\bin\mysql -uroot --port=3540 --prompt="mysql 5.4 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.4-alpha-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.4 >create database estar;
Query OK, 1 row affected (0.01 sec)

mysql 5.4 >use estar
Database changed
mysql 5.4 >create table t1 (runid varchar(10) not null, recall double not null);
Query OK, 0 rows affected (0.09 sec)

mysql 5.4 >insert into t1 values ('xxxx-1',0.1),('xxxx-2',0.2),('xxxx-1',0.3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.4 >select runid, avg(recall) as recall from t1 group by runid with rollup;
+--------+---------------------+
| runid  | recall              |
+--------+---------------------+
| xxxx-1 |                 0.2 |
| xxxx-2 |                 0.2 |
| NULL   | 0.20000000000000004 |
+--------+---------------------+
3 rows in set (0.00 sec)

mysql 5.4 >create table t2 as select runid, avg(recall) as recall from t1 group by runid with
    -> rollup;
Query OK, 3 rows affected, 1 warning (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql 5.4 >show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'runid' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql 5.4 >show create table t1;
+-------+------------------------------------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------------------------------------
| t1    | CREATE TABLE `t1` (
  `runid` varchar(10) NOT NULL,
  `recall` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql 5.4 >

GROUP BY, ROLLUP => Optimizer

Verified that the same problem exists in 5.1.

I can still see this bug as of MySQL 5.7.16.

I ran the test case per the original bug report:

----------
create table t1 (runid varchar(10) not null, recall double not null);
insert into t1 values ('xxxx-1',0.1),('xxxx-2',0.2),('xxxx-1',0.3);
create table t2 as select runid, avg(recall) as recall from t1 group by runid with rollup;
----------

The result was the same: I received the error "SQL Error (1048): Column 'runid' cannot be null".