Bug #32694 NOT NULL table field in a subquery produces invalid results
Submitted: 24 Nov 2007 23:52 Modified: 20 Nov 2010 23:30
Reporter: Marton Balint Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.x, 5.1.22 OS:Linux
Assigned to: Timour Katchaounov CPU Architecture:Any
Tags: null, subquery
Triage: D2 (Serious)

[24 Nov 2007 23:52] Marton Balint
Description:
If a subquery has an IS NULL check of a NOT-NULL-type field of the table of the outer SQL query, then the result of the query is invalid. An example to reproduce this bug is in the "How to repeat" section.

How to repeat:
Execute the following SQL statements:

CREATE TABLE `mysqlbug` (
  `id` int(10) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mysqlbug` (`id`, `date`) VALUES
(1, '2007-04-25 18:30:22');

SELECT *
    FROM `mysqlbug`
    WHERE  `id` IN (
      SELECT `id`
      FROM `mysqlbug` AS mysqlbug2
      WHERE (`mysqlbug`.`date` IS NULL)
    );

The result is the following:
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+

But the expected result is an empty set.

Suggested fix:
Not a fix, but a workaround is to change the type of the date field to NULL.
[25 Nov 2007 1:29] Miguel Solorzano
Thank you for the bug report.
[25 Nov 2007 1:30] Miguel Solorzano
I forgot to paste the result:

c:\dev>5.1\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-nt Source distribution

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

mysql> SELECT *
    ->     FROM `mysqlbug`
    ->     WHERE  `id` IN (
    ->       SELECT `id`
    ->       FROM `mysqlbug` AS mysqlbug2
    ->       WHERE (`mysqlbug`.`date` IS NULL)
    ->     );
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.09 sec)

mysql>
[4 Dec 2007 22:43] Jeffrey Pugh
I could not reproduce this in 6.0.4-alpha-pb34-win32 (perhaps because of the subquery work in 6.0?):

mysql> use test
Database changed
mysql> CREATE TABLE `mysqlbug` (
    ->   `id` int(10) NOT NULL,
    ->   `date` datetime NOT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO `mysqlbug` (`id`, `date`) VALUES
    -> (1, '2007-04-25 18:30:22');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT *
    ->     FROM `mysqlbug`
    ->     WHERE  `id` IN (
    ->       SELECT `id`
    ->       FROM `mysqlbug` AS mysqlbug2
    ->       WHERE (`mysqlbug`.`date` IS NULL)
    ->     );
Empty set (0.03 sec)
[4 Dec 2007 23:24] Miguel Solorzano
Indeed can't repeat with Falcon 64-bit Windows server:

c:\dev>6.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-nt Source distribution

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

mysql> CREATE TABLE `mysqlbug` (
    ->   `id` int(10) NOT NULL,
    ->   `date` datetime NOT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql>
mysql> INSERT INTO `mysqlbug` (`id`, `date`) VALUES
    -> (1, '2007-04-25 18:30:22');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT *
    ->     FROM `mysqlbug`
    ->     WHERE  `id` IN (
    ->       SELECT `id`
    ->       FROM `mysqlbug` AS mysqlbug2
    ->       WHERE (`mysqlbug`.`date` IS NULL)
    ->     );
Empty set (0.01 sec)

mysql>
[5 Dec 2007 8:33] Timour Katchaounov
The reason you don't see the bug in 6.0 is because by default
6.0 applies subquery flattening into a semi-join, and this bug
is a flaw in the IN=>EXISTS transformation (also present in 6.0).

The bug is reproducible in 6.0 if we set the following flag:
  set @@optimizer_switch='no_semijoin';
and run the test query. This flag is publicly visible, so anyone
may set it.
[6 Dec 2007 14:30] 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/39414

ChangeSet@1.2670, 2007-12-06 16:29:07+02:00, timour@lapi.mysql.com +2 -0
  Fix for BUG#32694 "NOT NULL table field in a subquery produces invalid results"
  
  The problem was that when convert_constant_item is called for subqueries,
  this happens when we already started executing the top-level query, and
  the field argument of convert_constant_item pointed to a valid table row.
  In turn convert_constant_item used the field buffer to compute the value
  of its item argument. This copied the item's value into the field,
  and made equalities with outer references always true.
  
  The fix saves/restores the original field's value when it belongs to an
  outer table.
[8 Dec 2007 20:28] 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/39604

ChangeSet@1.2597, 2007-12-08 22:27:01+02:00, timour@lapi.mysql.com +3 -0
  Fix for BUG#32694 "NOT NULL table field in a subquery produces invalid results"
    
  The problem was that when convert_constant_item is called for subqueries,
  this happens when we already started executing the top-level query, and
  the field argument of convert_constant_item pointed to a valid table row.
  In turn convert_constant_item used the field buffer to compute the value
  of its item argument. This copied the item's value into the field,
  and made equalities with outer references always true.
    
  The fix saves/restores the original field's value when it belongs to an
  outer table.
[8 Dec 2007 21:08] 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/39605

ChangeSet@1.2597, 2007-12-08 23:05:00+02:00, timour@lapi.mysql.com +3 -0
  Fix for BUG#32694 "NOT NULL table field in a subquery produces invalid results"
    
  The problem was that when convert_constant_item is called for subqueries,
  this happens when we already started executing the top-level query, and
  the field argument of convert_constant_item pointed to a valid table row.
  In turn convert_constant_item used the field buffer to compute the value
  of its item argument. This copied the item's value into the field,
  and made equalities with outer references always true.
    
  The fix saves/restores the original field's value when it belongs to an
  outer table.
[8 Dec 2007 21:58] 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/39606

ChangeSet@1.2677, 2007-12-08 23:54:07+02:00, timour@lapi.mysql.com +2 -0
  BUG#32694 post-merge adjustment of EXPLAIN results for 5.1.
[10 Dec 2007 16: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/39654

ChangeSet@1.2724, 2007-12-10 18:24:46+02:00, timour@lapi.mysql.com +2 -0
  Adjusted test case to test BUG#32694 in 6.0.
[14 Dec 2007 8:14] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:17] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:20] Bugs System
Pushed into 6.0.5-alpha
[14 Jan 2008 18:53] Jon Stephens
Documented bugfix in 5.0.54, 5.1.23, and 6.0.5 changelogs as:

        A subquery using an IS NULL check of a column defined as NOT
        NULL in a table used in the FROM clause of the outer query produced an
        invalid result.
[16 Aug 2010 6:40] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:14] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)