Bug #35633 Column cant be NULL error on SELECT...FROM (SELECT not_null_column, agg_func...)
Submitted: 28 Mar 2008 8:19 Modified: 28 Mar 2008 8:24
Reporter: Gary F. Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.54, 5.0.56 OS:Linux (2.6.25-rc4)
Assigned to: CPU Architecture:Any
Tags: 1048 subquery
Triage: Triaged: D2 (Serious)

[28 Mar 2008 8:19] Gary F.
Description:
mysql> SELECT t1.* FROM (SELECT content, IFNULL(sum(v.vote),0) as cvotes FROM btFiles_comments c left join comments_votes v on c.id=v.comment_id where 1=0) t1;

ERROR 1048 (23000): Column 'content' cannot be null

table schemas in How to repeat

How to repeat:
CREATE TABLE `btFiles_comments` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` mediumint(8) NOT NULL default '0',
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `classification` enum('none','fake','misnamed','spam','pwed') NOT NULL default 'none',
  `hash` varchar(40) character set latin1 NOT NULL,
  `poster_ip` varchar(15) character set latin1 NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `classification` (`classification`),
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `comments_votes` (
  `comment_id` int(11) NOT NULL default '0',
  `user_id` mediumint(8) NOT NULL default '0',
  `vote` tinyint(1) NOT NULL default '1',
  `voted_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `user_ip` varchar(15) character set latin1 NOT NULL,
  PRIMARY KEY  (`comment_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[28 Mar 2008 8:24] Valeriy Kravchuk
Thank you for a bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.56-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `btFiles_comments` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `user_id` mediumint(8) NOT NULL default '0',
    ->   `created_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update

    -> CURRENT_TIMESTAMP,
    ->   `classification` enum('none','fake','misnamed','spam','pwed') NOT NULL
default 'none',
    ->   `hash` varchar(40) character set latin1 NOT NULL,
    ->   `poster_ip` varchar(15) character set latin1 NOT NULL,
    ->   `content` text NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `classification` (`classification`),
    ->   KEY `hash` (`hash`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE `comments_votes` (
    ->   `comment_id` int(11) NOT NULL default '0',
    ->   `user_id` mediumint(8) NOT NULL default '0',
    ->   `vote` tinyint(1) NOT NULL default '1',
    ->   `voted_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update
    -> CURRENT_TIMESTAMP,
    ->   `user_ip` varchar(15) character set latin1 NOT NULL,
    ->   PRIMARY KEY  (`comment_id`,`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT t1.* FROM (SELECT content, IFNULL(sum(v.vote),0) as cvotes FROM
    -> btFiles_comments c left join comments_votes v on c.id=v.comment_id where
1=0) t1;
ERROR 1048 (23000): Column 'content' cannot be null
[4 Jun 2008 6:45] Sergey Petrunya
Repeatable on 6.0-bk also (tip cset: fix for BUG#35468)
[8 Sep 2008 11:56] Sergey Petrunya
Exploring on 5.0: 

  Breakpoint 1, my_error (nr=1048, MyFlags=0) at my_error.c:77
  Current language:  auto; currently c
(gdb) wher
  #0  my_error (nr=1048, MyFlags=0) at my_error.c:77
  #1  0x0839ba02 in set_field_to_null_with_conversions (field=0x8dd11f0, no_conversions=false) at field_conv.cc:184
  #2  0x081ae474 in Item_field::save_in_field (this=0x8dd4db0, to=0x8dd11f0, no_conversions=false) at item.cc:4607
  #3  0x0829dc62 in fill_record (thd=0x8d9b928, ptr=0x8dd102c, values=@0x8dd4a5c, ignore_errors=true) at sql_base.cc:5883
  #4  0x083d4a6f in select_union::send_data (this=0x8dd5ed8, values=@0x8dd4a5c) at sql_union.cc:60
  #5  0x082da169 in return_zero_rows (join=0x8ddbaf8, result=0x8dd5ed8, tables=0x8dd5138, fields=@0x8dd4a5c, send_row=true, select_options=2424588800, info=0x86e51e2 "Impossible WHERE", having=0x0) at sql_select.cc:6758

(gdb) up
  #2  0x081ae474 in Item_field::save_in_field (this=0x8dd4db0, to=0x8dd11f0, no_conversions=false) at item.cc:4
(gdb) p this->is_null() 
  $10 = true
(gdb) p this->maybe_null
  $11 = 0 '\0'

btFiles_comments.content has NULL value while being defined as non-nullable.

The reason for is probably that this property of  btFiles_comments.content:  It is defined as NON-null, however, it referred from the select list and the query has grouping, so it can take a NULL value.

I'm not sure how this should be addressed.  Should we make all references to table columns from post-group-by contexts to have maybe_null=TRUE?
[8 Sep 2008 12:28] Sergey Petrunya
A simpler testcase:

mysql> 
  select t1.* from (SELECT content, sum(1) as cvotes FROM btFiles_comments c where 1=0) t1 ; 
ERROR 1048 (23000): Column 'content' cannot be null
[11 Sep 2008 21:49] Sergey Petrunya
Changed synopsis to better reflect the bug's nature
[1 Oct 2013 19:09] Valeriy Kravchuk
Looks fixed in 5.6 at least:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE `btFiles_comments` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `user_id` mediumint(8) NOT NULL default '0',
    ->   `created_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update
 CURRENT_TIMESTAMP,
    ->   `classification` enum('none','fake','misnamed','spam','pwed') NOT NULL
default 'none',
    ->   `hash` varchar(40) character set latin1 NOT NULL,
    ->   `poster_ip` varchar(15) character set latin1 NOT NULL,
    ->   `content` text NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `classification` (`classification`),
    ->   KEY `hash` (`hash`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.79 sec)

mysql>
mysql> CREATE TABLE `comments_votes` (
    ->   `comment_id` int(11) NOT NULL default '0',
    ->   `user_id` mediumint(8) NOT NULL default '0',
    ->   `vote` tinyint(1) NOT NULL default '1',
    ->   `voted_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update C
URRENT_TIMESTAMP,
    ->   `user_ip` varchar(15) character set latin1 NOT NULL,
    ->   PRIMARY KEY  (`comment_id`,`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)

mysql>  SELECT t1.* FROM (SELECT content, IFNULL(sum(v.vote),0) as cvotes FROM b
tFiles_comments c left join comments_votes v on c.id=v.comment_id where 1=0) t1;

+---------+--------+
| content | cvotes |
+---------+--------+
| NULL    |      0 |
+---------+--------+
1 row in set (0.31 sec)

mysql> select t1.* from (SELECT content, sum(1) as cvotes FROM btFiles_comments
c where 1=0) t1 ;
+---------+--------+
| content | cvotes |
+---------+--------+
| NULL    |   NULL |
+---------+--------+
1 row in set (0.01 sec)

Time to update bug status maybe?