| 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: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?
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;