MySQL Bugs: #32694: NOT NULL table field in a subquery produces invalid results

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

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.

Thank you for the bug report.

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>

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)

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>

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.

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.

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.

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.

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.

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.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)