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.
