| Bug #75967 | Wrong result (extra rows) with SQ and comparison to NULL, materialization=off | ||
|---|---|---|---|
| Submitted: | 19 Feb 2015 12:45 | Modified: | 20 Feb 2015 8:05 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7, 5.6.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Feb 2015 8:05]
MySQL Verification Team
Hello Elena Stepanova, Thank you for the report and test case. Thanks, Umesh
[20 Feb 2015 8:06]
MySQL Verification Team
// 5.6.24
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.24 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.24-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (1,6),(2,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
Empty set (0.03 sec)
mysql> EXPLAIN EXTENDED
-> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index |
| 2 | SUBQUERY | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (Block Nested Loop) |
| 2 | SUBQUERY | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.01 sec)
mysql> SET SESSION optimizer_switch = "materialization=off";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> EXPLAIN EXTENDED
-> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (Block Nested Loop) |
| 2 | DEPENDENT SUBQUERY | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
Description: Partial output of the test case provided in 'How to repeat' section (empty result set is the expected result, because the subquery returns NULL). MySQL [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; Empty set (0.01 sec) MySQL [test]> EXPLAIN EXTENDED -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; +----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index | | 2 | SUBQUERY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index | | 2 | SUBQUERY | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (Block Nested Loop) | | 2 | SUBQUERY | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) MySQL [test]> MySQL [test]> SET SESSION optimizer_switch = "materialization=off"; Query OK, 0 rows affected (0.00 sec) MySQL [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.01 sec) MySQL [test]> EXPLAIN EXTENDED -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; +----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (Block Nested Loop) | | 2 | DEPENDENT SUBQUERY | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+--------------------+-------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ How to repeat: CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,6),(2,4); SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; SET SESSION optimizer_switch = "materialization=off"; SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;