Bug #9451 Selecting rows with null primary key does not always return the empty set
Submitted: 29 Mar 2005 12:58 Modified: 29 Mar 2005 19:47
Reporter: David Clements Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.10-nt-log OS:Windows (Windows XP)
Assigned to: Sergey Petrunya CPU Architecture:Any

[29 Mar 2005 12:58] David Clements
Description:
A Select statement that attempts to retrieve rows that have null primary key may not return the empty set if it is executed immediately after a row is inserted. An example of this behaviour is illustrated below.
The Provenance table has an integer auto_increment primary key named ProvenanceNo. Selecting rows with null primary keys incorrectly returns what appears to be the row associated with the current position of the primary key index if the select statement follows an insert into the same table. Executing the select statement a second time correctly returns the empty set.
The EXPLAIN statements show that on the first occasion, the optimiser uses an index but subsequently identifies an 'impossible where' to produce the correct result.

mysql> INSERT INTO Provenance (SeriesNo, EntityNo) VALUES (2, 3);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM Provenance WHERE ProvenanceNo Is Null;
+--------------+----------+----------+
| ProvenanceNo | SeriesNo | EntityNo |
+--------------+----------+----------+
|            4 |        2 |        3 |
+--------------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Provenance WHERE ProvenanceNo Is Null;
Empty set (0.02 sec)

mysql> INSERT INTO Provenance (SeriesNo, EntityNo) VALUES (2, 3);
Query OK, 1 row affected (0.06 sec)

mysql> EXPLAIN SELECT * FROM Provenance WHERE ProvenanceNo Is Null;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | Provenance | const | PRIMARY       | PRIMARY |       4 | const |    1 |       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM Provenance WHERE ProvenanceNo Is Null;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

The above behaviour has only been detected on tables having an integer auto_increment primary key and appears to be related to the state of the current connection.  If the INSERT and SELECT statements are executed on different connections the correct result is always returned by the SELECT statement.

The above example was run on MySQL-nt version 4.1.10 under Microsoft Windows XP Professional 2002 SP2. (See the attached SQLVars file for detailed information about the test environment.)  The behaviour has also been reproduced on MySQL 4.0.18 under Mandrake Linux version 10.0.

How to repeat:
It is possible to demonstrate using a single table with an integer auto_increment primary key, but cannot be repeated reliably in this simple case.
The attached script reproduces the environment used in the above example, and will reliably repeat the described behaviour.
[29 Mar 2005 13:00] David Clements
Output of SHOW VARIABLES in the test environment

Attachment: MySQLVars.txt (text/plain), 17.67 KiB.

[29 Mar 2005 13:01] David Clements
SQL Script to reproduce the behaviour

Attachment: MySQLScript.txt (text/plain), 1.47 KiB.

[29 Mar 2005 19:47] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/create-table.html
In particular, note:

To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROM tbl_name WHERE auto_col IS NULL
[30 Mar 2005 2:57] David Clements
Thank you for the quick and helpful response.
My apologies for missing the documentation describing the special behaviour of IS NULL on auto_increment columns. I did actually try to follow your instructions for submitting a bug report, but I guess we all make mistakes. Next time I'll be more cautious and go through the forums first.