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) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.91 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[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] Shane Bester
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.