MySQL Bugs: #12885: rollup / subquery and creat table, causes incorrect cannot be null

Bug #12885	rollup / subquery and creat table, causes incorrect cannot be null
Submitted:	30 Aug 2005 16:05	Modified:	15 Sep 2005 19:12
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14, 5.0.11	OS:	FreeBSD (freebsd, Windows)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The column in the source table must be defined not null.

This is in parts related to Bug #9681:

A rolled up collumn can be null, this has been fixed, if it occurs in a subquery, it is still present  if it occurs in a "create table select" (likely views in 5.0 ?)

Additionally in a rolled up query any further column derived from the rolled up column (a+1 or length(a) / conv(a,16,10) ) will  be null, in the rollup line. This is not handled in subqueries nor create table.

sql, for results  see: how to repeat

create table t1  (a  varchar(9) not null default '');
insert into t1 select 'a';

select a,  length(a),count(*) from t1 group by a with rollup;
select * from (select a,  length(a), count(*) from t1 group by a with rollup) x;
select * from (select a,  a + 1, count(*) from t1 group by a with rollup) x;

create table t2 select a,  count(*) from t1 group by a with rollup; show warnings;
create table t3 select a,  length(a), count(*) from t1 group by a with rollup; show warnings;
create table t4 select a,  a +1, count(*) from t1 group by a with rollup; show warnings;

drop table t1; drop table t2; drop table t3; drop table t4; 

How to repeat:
mysql> create table t1  (a  varchar(9) not null default '');
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select 'a';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> select a,  length(a),count(*) from t1 group by a with rollup;
+---+-----------+----------+
| a | length(a) | count(*) |
+---+-----------+----------+
| a |         1 |        1 |
| NULL|      NULL |        1 |
+---+-----------+----------+
2 rows in set (0.23 sec)

mysql> select * from (select a,  length(a), count(*) from t1 group by a with rollup) x;
ERROR 1048 (23000): Column 'length(a)' cannot be null

mysql> select * from (select a,  a + 1, count(*) from t1 group by a with rollup) x;
ERROR 1048 (23000): Column 'a + 1' cannot be null

mysql> create table t2 select a,  count(*) from t1 group by a with rollup; show warnings;
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'a' at row 1 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.18 sec)

mysql> create table t3 select a,  length(a), count(*) from t1 group by a with rollup; show warnings;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'a' at row 1         |
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'length(a)' at row 1 |
+---------+------+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create table t4 select a,  a +1, count(*) from t1 group by a with rollup; show warnings;
Query OK, 2 rows affected, 2 warnings (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 2

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'a' at row 1 |
| Warning | 1265 | Data truncated for column 'a +1' at row 1                     |
+---------+------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> drop table t1; drop table t2; drop table t3; drop table t4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Suggested fix:
-

Yes, the same bug with views in 5.0.11:

mysql> use test;
Database changed

mysql> create table t1  (a  varchar(9) not null default '');
Query OK, 0 rows affected (0.18 sec)

mysql> insert into t1 values ('a');
Query OK, 1 row affected (0.05 sec)

mysql> select a,  length(a),count(*) from t1 group by a with rollup;
+---+-----------+----------+
| a | length(a) | count(*) |
+---+-----------+----------+
| a |         1 |        1 |
| NULL|      NULL |        1 |
+---+-----------+----------+
2 rows in set (0.04 sec)

mysql> create view v1 as select a,  length(a),count(*) from t1 group by a with rollup;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v1;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| a         | varchar(9) | YES  |     | NULL    |       |
| length(a) | bigint(10) | NO   |     | 0       |       |
| count(*)  | bigint(21) | NO   |     | 0       |       |
+-----------+------------+------+-----+---------+-------+
3 rows in set (0.16 sec)

mysql> select * from v1;
ERROR 1048 (23000): Column 'length(a)' cannot be null
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

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/internals/29525

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/internals/29535

ChangeSet
  1.2414 05/09/08 12:37:16 igor@rurik.mysql.com +3 -0
  sql_select.cc:
    Fixed bug #12885.
    Forced inheritence of the maybe_null flag for the expressions
    containing GROUP BY attributes in selects with ROLLUP.
  olap.test, olap.result:
    Added test case for bug #12885.

The fix was merged into 5.0 and a test cases with VIEW was added.

This fix will appear in 4.1.15 and 5.0.13

Noted in 4.1.15, 5.0.13 changelogs.