MySQL Bugs: #31450: Query causes error 1048

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

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);

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>

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.

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.

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.

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

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.

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

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.

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.