Bug #43912 'Column * cannot be null' when derived table has count() with no results

• Submitted: 27 Mar 2009 13:56
• Modified: 16 Oct 2012 5:00
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Won't fix
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.91
• OS: Any
• CPU Architecture: Any

[27 Mar 2009 13:56] Shane Bester

Description:
When the derived table has the count() function that returns nothing, the outer query complains about being null.

mysql> drop table if exists t5;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t5(id int not null);
Query OK, 0 rows affected (0.05 sec)

mysql> select * from (select id,count(id) as cnt from t5) as x;
ERROR 1048 (23000): Column 'id' cannot be null
mysql>

At least, old versions of mysql had a better error message:

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

How to repeat:
drop table if exists t5;
create table t5(id int not null);
select * from (select id,count(id) as cnt from t5) as x;

[30 Dec 2009 23:05] Konstantin Osipov

Setting the right lead.

[1 Aug 2010 11:32] MySQL Verification Team

only affects 5.0 now.
this is fixed in 5.1.46 and 5.5.5...

mysql> create table t5(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (select id,count(id) as cnt from t5) as x;
+----+-----+
| id | cnt |
+----+-----+
|  0 |   0 |
+----+-----+
1 row in set (0.00 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.46-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.03 sec)

[16 Oct 2012 5:00] Erlend Dahl

Not feasible to fix on 5.0.