Bug #41371 Select returns 1 row with condition "col is not null and col is null"
Submitted: 10 Dec 2008 18:36 Modified: 7 Mar 2010 18:16
Reporter: Eric GuimarĂ£es Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:4.1.22, 5.0.51, 5.0.72, 5.1.30, 6.0.8 OS:Linux
Assigned to: Alexey Botchkov CPU Architecture:Any

[10 Dec 2008 18:36] Eric GuimarĂ£es
Description:
After inserting a new row in a table containing an auto-increment primary key id, the first select * query with (id is null and id is not null) condition returns one row when it should return none.

How to repeat:
The first SELECT statement returns the just-inserted row, even though the supplied condition should always evaluate to FALSE. From that on, repeating the SELECT statements appear to work ok:

mysql> CREATE TABLE b (
    ->   id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO b( id ) VALUES ( NULL );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
Empty set (0.00 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
Empty set (0.00 sec)

mysql>
[10 Dec 2008 19:38] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 4.1.22, 5.0.72, 5.1.30, 6.0.8:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.72-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE b (
    ->    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO b( id ) VALUES ( NULL );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.02 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
Empty set (0.00 sec)

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
Empty set (0.00 sec)
[4 Feb 2009 10:04] Hajo Skwirblies
We experienced the behavior also with selects on different tables.
Reproduced on: 5.0.45 (amd64),5.0.67 (amd64/i686)
On i686 the problem ocurrs only on the first "select after insert" per session.
On amd64 every "select after insert" returns the wrong set with increasing ids.

CREATE DATABASE IF NOT EXISTS bugtest;
USE bugtest;
DROP TABLE IF EXISTS one;
CREATE TABLE one (
	`id` bigint(20) NOT NULL auto_increment,
	`isdeleted` tinyint(4) NOT NULL default '0',
	PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
DROP TABLE IF EXISTS two;
CREATE TABLE two (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
INSERT INTO `two` (`id`, `name`) VALUES
	(1, 'eins'),
	(2, 'zwei'),
	(3, 'drei');

INSERT INTO one SET isdeleted='0';
SELECT id, name FROM two WHERE id IS NULL;
+----+------+
| id | name |
+----+------+
|  1 | eins |
+----+------+
[17 Jun 2009 12:42] V Venkateswaran
According to the documentation in the following link

(http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-functionality.html#connec...)

Certain ODBC applications (including Delphi and Access) may
have trouble obtaining the auto-increment value using the
previous examples. In this case, try the following statement
as an alternative:

SELECT * FROM tbl WHERE auto IS NULL;

where auto is an auto-increment column.

This special case needs to work only when the condition is alone in
the where clause (no AND/OR) and is at top-level. Hence the behavior
needs to be restricted to the following case

SELECT * FROM <table> WHERE <autoinc> IS NULL

So the following query returning a column is a bug

mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

But on the other hand the below is not a bug and is expected behaviour

SELECT id, name FROM two WHERE id IS NULL;
+----+------+
| id | name |
+----+------+
|  1 | eins |
+----+------+

-----------------------------------------------------------------------------------
|  Related Code                                                                    |
|  ------------                                                                    |
|                                                                                  |
|  from sql_select.cc (remove_eq_conds) function,                                  |
|                                                                                  |
|  Pls see the following comment in the function                                   |
|                                                                                  |
|        Handles this special case for some ODBC applications:                     |
|        The are requesting the row that was just updated with a auto_increment    |
|        value with this construct:                                                |
|                                                                                  |
|        SELECT * from table_name where auto_increment_column IS NULL              |
|        This will be changed to:                                                  |
|        SELECT * from table_name where auto_increment_column = LAST_INSERT_ID     |
|                                                                                  |
|   This function basically is used to recognize obvious equalities and            |
|   inequalities like 1=1, 1=2 etc. This function also checks for the IS NULL      |
|   condition as a special case.                                                   |
|                                                                                  |   
|   The function performs the above query transformation for the first             |
|   select after an insert.                                                        |
------------------------------------------------------------------------------------
[21 Jun 2009 1:30] V Venkateswaran
Item Tree for the where condition
---------------------------------

The Item tree for the condition (id  is not null and id is null ) 
of the query: SELECT b.id  FROM b  WHERE (id  is not null and id is null );
can be represented as below.

                  COND__ITEM (Item_func::COND_AND_FUNC)
                             |
                             |
                             |
                             |
              -------------------------------
              |                             |
              |                             |
      Item_func::ISNOTNULL_FUNC    Item_func::ISNULL_FUNC
              
COND_ITEM contains a list of Items (2 items), one for the condition "is not null" represented by
Item_func::ISNOTNULL_FUNC and one for the condition "is null" represented by Item_func::ISNULL_FUNC.

Parsing the Item Tree to remove obvious equality and unequality conditions
--------------------------------------------------------------------------

The remove_eq_conds functions parses and replaces the obvious conditions in the item tree to
increase the efficiency of the query evaluation process.

For e.g.

The condition 1=1 will be replaced by COND_TRUE
The condition 1=2 will be replaced by COND_FALSE
The condition age > 40 will be replaced by COND_OK

The function "remove_eq_conds" works by recursively parsing the tree. For example, in the above case
we pass the root pointer "COND__ITEM (Item_func::COND_AND_FUNC)" to the remove_eq_conds function.
This function then calls itself on "Item_func::ISNOTNULL_FUNC" and "Item_func::ISNULL_FUNC" using
the argument list that is part of the root(List_iterator<Item> li(*((Item_cond*) cond)->argument_list());).

Replacing the "is null" item
----------------------------

The following condition

else if (cond->type() == Item::FUNC_ITEM &&
           ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)

is used to check for the "is null" condition and if present is replaced
with a check for the last inserted autoincrement value in the following
code.

COND *new_cond;
if ((new_cond= new Item_func_eq(args[0],
                                new Item_int("last_insert_id()",
                                thd->read_first_successful_insert_id_in_prev_stmt(),
                                MY_INT64_NUM_DECIMAL_DIGITS))))
{
  cond=new_cond;
  /*
    Item_func_eq can't be fixed after creation so we do not check
    cond->fixed, also it do not need tables so we use 0 as second
    argument.
  */
  cond->fix_fields(thd, &cond);
}

Performing query transformation only in the case of "select * from <table> where <auto>' is NULL queries
---------------------------------------------------------------------------------------------------------

The easiest way of doing this would be to move the query transformation code into a separate function and
call this query transformation code before every call to remove_eq_conds.
[21 Jun 2009 1:33] V Venkateswaran
rough patch for doing restrictive query transformation

Attachment: 41371.diff.txt (text/plain), 6.99 KiB.

[21 Jun 2009 1:36] V Venkateswaran
The file 41371.diff.txt contains a rough patch that moves the
auto is NULL transformation into a separate function. The
above patch produces the following correct cases

case: 1

mysql> CREATE TABLE b (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO b( id ) VALUES ( NULL );
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT b.id  FROM b  WHERE (id  is not null and id is null );
Empty set (0.00 sec)

case: 2

mysql> CREATE TABLE b (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO b( id ) VALUES ( NULL );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT b.id  FROM b  WHERE id is null;
+----+
| id |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)
[3 Sep 2009 12:48] 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/82328

2709 Alexey Botchkov	2009-09-03
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
      For the AUTO_INCREMENT field, the IS NULL condition is turned into 'col == LAST_INSERT_ID'.
      Fixed by preventing that transformation for any other conditions than simple 'col IS NULL'.
      
      per-file comments:
        mysql-test/r/func_isnull.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test result updated
      
        mysql-test/t/func_isnull.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test case added
      
        sql/sql_select.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          remove_eq_conds() split in two parts - one only checks the upper condition,
          the req_remove_eq_conds() recursively checks all the condition tree.
[24 Oct 2009 11:43] 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/88084

2709 Alexey Botchkov	2009-10-24
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          For application compatibility reasons  MySQL converts "<autoincrement_column> IS NULL"
          predicates to "<autoincrement_column> = LAST_INSERT_ID()" in the first SELECT following an
          INSERT regardless of whether they're top level predicates or not. This causes wrong and
          obscure results when these predicates are combined with others on the same columns. Fixed
          by only doing the transformation on a single top-level predicate if a special SQL mode is
          turned on (sql_auto_is_null).
          Also made sql_auto_is_null off by default.
      
      per-file comments:
        mysql-test/r/func_isnull.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test result updated
      
        mysql-test/t/func_isnull.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test case added
      
        sql/mysqld.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          sql_auto_is_null now is OFF by default.
      
        sql/sql_select.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          remove_eq_conds() split in two parts - one only checks the upper condition,
          the req_remove_eq_conds() recursively checks all the condition tree.
[3 Nov 2009 4:38] 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/89022

2935 Alexey Botchkov	2009-11-03
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          For application compatibility reasons  MySQL converts "<autoincrement_column> IS NULL"
          predicates to "<autoincrement_column> = LAST_INSERT_ID()" in the first SELECT following an
          INSERT regardless of whether they're top level predicates or not. This causes wrong and
          obscure results when these predicates are combined with others on the same columns. Fixed
          by only doing the transformation on a single top-level predicate if a special SQL mode is
          turned on (sql_auto_is_null).
          Also made sql_auto_is_null off by default.
      
      per-file comments:
        mysql-test/r/func_isnull.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test result updated
      
        mysql-test/t/func_isnull.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test case added
      
        sql/mysqld.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          sql_auto_is_null now is OFF by default.
      
        sql/sql_select.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          remove_eq_conds() split in two parts - one only checks the upper condition,
          the req_remove_eq_conds() recursively checks all the condition tree.
      
      
        mysql-test/extra/rpl_tests/rpl_insert_id.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              test fixed (set the sql_auto_is_null variable)
        mysql-test/r/mysqlbinlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/r/mysqlbinlog2.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/r/odbc.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/r/query_cache.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/r/user_var-binlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/suite/rpl/r/rpl_insert_id.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/suite/rpl/r/rpl_sp.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
        mysql-test/t/odbc.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              test fixed (set the sql_auto_is_null variable)
[3 Nov 2009 15:26] 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/89154

2938 Alexey Botchkov	2009-11-03
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          For application compatibility reasons  MySQL converts "<autoincrement_column> IS NULL"
          predicates to "<autoincrement_column> = LAST_INSERT_ID()" in the first SELECT following an
          INSERT regardless of whether they're top level predicates or not. This causes wrong and
          obscure results when these predicates are combined with others on the same columns. Fixed
          by only doing the transformation on a single top-level predicate if a special SQL mode is
          turned on (sql_auto_is_null).
          Also made sql_auto_is_null off by default.
      
      per-file comments:
        mysql-test/r/func_isnull.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test result updated
      
        mysql-test/t/func_isnull.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test case added
      
        sql/mysqld.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          sql_auto_is_null now is OFF by default.
      
        sql/sql_select.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          remove_eq_conds() split in two parts - one only checks the upper condition,
          the req_remove_eq_conds() recursively checks all the condition tree.
      
        mysql-test/extra/rpl_tests/rpl_insert_id.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              test fixed (set the sql_auto_is_null variable)
      
        mysql-test/r/mysqlbinlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/r/mysqlbinlog2.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/r/odbc.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/r/query_cache.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/r/user_var-binlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/suite/rpl/r/rpl_insert_id.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/suite/rpl/r/rpl_sp.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              result updated
      
        mysql-test/t/odbc.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
              test fixed (set the sql_auto_is_null variable)
[4 Nov 2009 10:26] Alexey Botchkov
mysql-next-mr-bugfixing
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:holyfoot@mysql.com-20091104085357-p6tcgw8t5adbzxmn) (merge vers: 6.0.14-alpha) (pib:13)
[1 Dec 2009 20:14] Paul Dubois
Noted in 6.0.14 changelog.

For application compatibility reasons, when sql_auto_is_null is 1,
MySQL converts auto_inc_col IS NULL to auto_inc_col = 
LAST_INSERT_ID(). However, this was being done regardless of whether
the predicate was alone or at the top level. Now it occurs only when
it is a single top-level level predicate.
      
In conjunction with this bug fix, the default value of the
sql_auto_is_null system variable has been changed from 1 to 0, which
may cause incompatibilities with existing applications.

Setting report to NDI pending push to 5.6.x.
[11 Dec 2009 6:04] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:45] Paul Dubois
Noted in 5.6.0 changelog.
[6 Mar 2010 10:52] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[7 Mar 2010 18:16] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.
[27 Sep 2010 22:33] Roel Van de Paar
Bug #47005 was marked as duplicate since apparently it was fixed by this one.