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: | |
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 |
[28 Mar 2008 8:19]
Gary F.
[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?