Bug #31450 Query causes error 1048
Submitted: 8 Oct 2007 14:21 Modified: 3 Dec 2007 17:08
Reporter: NOT_FOUND NOT_FOUND Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.1.22-rc/5.0 OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: query

[8 Oct 2007 14:21] NOT_FOUND NOT_FOUND
Description:
Running query:

select * from user_accounts where ua_invited_by_id not in (select ua_id from user_accounts)

results in ERROR 1048 (23000): Column 'ua_id' cannot be null

To create the table see "How to repeat" section. This error occurs on Win XP SP2 (using binaries provided by MYSQL) and on various Linux dists (self compiled).

How to repeat:
Table used in query (with required data) can be created by running script:

http://akson.sgh.waw.pl/~mpieck/users_error.sql

mysql> use test;
mysql> select * from user_accounts where ua_invited_by_id not in (select ua_id from user_accounts);
[8 Oct 2007 16:02] MySQL Verification Team
Thank you for the bug report.

mysql> select * from user_accounts where ua_invited_by_id not in (select ua_id from
    -> user_accounts);
Empty set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.24-debug |
+--------------+
1 row in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-debug Source distribution

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

mysql> select * from user_accounts where ua_invited_by_id not in (select ua_id from user_accounts);
ERROR 1048 (23000): Column 'ua_id' cannot be null
mysql> 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.23-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> select * from user_accounts where ua_invited_by_id not in (select ua_id from
    -> user_accounts);
ERROR 1048 (23000): Column 'ua_id' cannot be null
mysql>
[23 Oct 2007 11:12] Sergey Petrunya
The bug shows up only for decimal type. If one changes the types of ua_id and ua_invited_by_id to INT then the query succeeds and produces correct results.
[23 Oct 2007 11:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36122

ChangeSet@1.2592, 2007-10-23 15:26:36+04:00, sergefp@mysql.com +3 -0
  BUG#31450: Query causes error 1048
  - Let Item::save_in_field() call set_field_to_null_with_conversions() 
    for decimal type, like this is done for the other item result types.
[23 Oct 2007 15:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36157

ChangeSet@1.2544, 2007-10-23 19:24:59+04:00, sergefp@mysql.com +3 -0
  BUG#31450: Query causes error 1048
  - Let Item::save_in_field() call set_field_to_null_with_conversions() 
    for decimal type, like this is done for the other item result types.
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:32] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:34] Bugs System
Pushed into 6.0.4-alpha
[3 Dec 2007 16:45] Sergey Petrunya
The bug was that queries that use ref access or index-based subquery execution over indexes that have columns of type DECIMAL could fail with
  
  Column XXX cannot be null

error.
[3 Dec 2007 17:08] Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.
[27 Mar 2008 10:39] Gary F.
This bug is still present and reproduced in 5.0.54. Similar pattern of query but with the "column cannot be null" a Text column inside the subquery.
[8 Sep 2008 12:32] Sergey Petrunya
Gary,

Albeit the effects are similar, BUG#35633 is a manifestation of a different problem.  The fix for this bug is valid and can't be expected to fix BUG#35633. That bug will be addressed separately.