| Bug #59187 | Wrong result with NULL NOT IN subquery | ||
|---|---|---|---|
| Submitted: | 27 Dec 2010 15:31 | Modified: | 27 Dec 2010 15:34 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.55, 5.6.2-m5 | OS: | Any |
| Assigned to: | Roy Lyseng | CPU Architecture: | Any |
[27 Dec 2010 15:34]
Valeriy Kravchuk
Verified with current mysql-5.1 and mysql-trunk trees on Mac OS X:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.55-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE t1 (
-> pk INT PRIMARY KEY,
-> int_key INT,
-> varchar_key VARCHAR(5) UNIQUE
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t1 VALUES (9, 7,NULL), (10,8,'p');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> EXPLAIN
-> SELECT * FROM t1
-> WHERE NULL NOT IN (
-> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
-> WHERE INNR.varchar_key > 'n{');
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM t1
-> WHERE NULL NOT IN (
-> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
-> WHERE INNR.varchar_key > 'n{');
+----+---------+-------------+
| pk | int_key | varchar_key |
+----+---------+-------------+
| 9 | 7 | NULL |
| 10 | 8 | p |
+----+---------+-------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE NULL NOT IN (
-> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
-> WHERE INNR.varchar_key > 'n{');
+----+--------------------+-------+-------+---------------------+---------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------------+---------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DEPENDENT SUBQUERY | INNR | const | PRIMARY,varchar_key | PRIMARY | 4 | | 2 | Using where; Full scan on NULL key |
| 2 | DEPENDENT SUBQUERY | INNR2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+--------------------+-------+-------+---------------------+---------+---------+------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE NULL NOT IN (
-> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
-> WHERE INNR.varchar_key > 'n{');
Empty set (0.00 sec)
Description: In the following test case the two subqueries should produce the same empty result, however, the first one produces all rows of the outer table, while the second query correctly doesn't return any rows. The only difference in the two queries is LEFT vs INNER join in the subquery, however the two subqueries produce the same result if run separately. The only difference in the results of the subqueries is the nullability of the result column. macbook-pro:trunk openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.2-m5-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table if exists t1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 ( -> pk INT PRIMARY KEY, -> int_key INT, -> varchar_key VARCHAR(5) UNIQUE -> ); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO t1 VALUES (9, 7,NULL), (10,8,'p'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'; +----+ | pk | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'; +------+ | pk | +------+ | 10 | +------+ 1 row in set (0.00 sec) So, tow queries above give the same result. The only difference is illustrated below: mysql> create table t2_inr as -> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'; Query OK, 1 row affected (0.41 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create table t2_outr as -> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'; Query OK, 1 row affected (0.15 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> explain t2_inr; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | pk | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> explain t2_outr; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | pk | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) But queries with these SELECTs as subqueries produce different results: mysql> EXPLAIN -> SELECT * FROM t1 -> WHERE NULL NOT IN ( -> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'); +----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.01 sec) mysql> SELECT * FROM t1 -> WHERE NULL NOT IN ( -> SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'); +----+---------+-------------+ | pk | int_key | varchar_key | +----+---------+-------------+ | 9 | 7 | NULL | | 10 | 8 | p | +----+---------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN -> SELECT * FROM t1 -> WHERE NULL NOT IN ( -> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'); +----+--------------------+-------+-------+---------------------+---------+---------+-------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------------+---------+---------+-------+------+-----------------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | DEPENDENT SUBQUERY | INNR | const | PRIMARY,varchar_key | PRIMARY | 4 | const | 2 | Using where; Full scan on NULL key | | 2 | DEPENDENT SUBQUERY | INNR2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (BNL, incremental buffers) | +----+--------------------+-------+-------+---------------------+---------+---------+-------+------+-----------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t1 -> WHERE NULL NOT IN ( -> SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) -> WHERE INNR.varchar_key > 'n{'); Empty set (0.00 sec) Looks like all versions are affected. Bug was originally reported for MariaDB as https://bugs.launchpad.net/maria/+bug/692535 How to repeat: drop table if exists t1; CREATE TABLE t1 (pk INT PRIMARY KEY, int_key INT, varchar_key VARCHAR(5) UNIQUE); INSERT INTO t1 VALUES (9, 7,NULL), (10,8,'p'); -- let's compare results with inner and left join SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'; SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'; -- then compare EXPLAINs and results of the following query with these subqueries EXPLAIN SELECT * FROM t1 WHERE NULL NOT IN ( SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'); SELECT * FROM t1 WHERE NULL NOT IN ( SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'); EXPLAIN SELECT * FROM t1 WHERE NULL NOT IN ( SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'); SELECT * FROM t1 WHERE NULL NOT IN ( SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key) WHERE INNR.varchar_key > 'n{'); Suggested fix: Check https://bugs.launchpad.net/maria/+bug/692535 :)