Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.25, 5.3.7
-
None
-
None
Description
The following query
SELECT * FROM t1
|
WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
|
OR a > 100
|
returns an empty result set with the default optimizer_switch (where subquery_cache=ON), and some rows with subquery_cache=OFF. I believe that the empty result set is correct, because all `a` values are less than 100, and the other part of the WHERE condition evaluates as NULL.
bzr version-info
revision-id: igor@askmonty.org-20120623220005-f4323jdj5mw7y2o5
|
date: 2012-06-23 15:00:05 -0700
|
build-date: 2012-06-26 00:01:40 +0400
|
revno: 3550
|
mysql-trunk with the default optimizer_switch also returns rows.
maria/5.2 does not.
EXPLAIN with the default optimizer_switch (subquery_cache=on):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((not(<expr_cache><3>(<in_optimizer>((3,3),<exists>(select NULL,NULL having (((3 = NULL) or isnull(NULL)) and ((3 = NULL) or isnull(NULL)) and <is_not_null_test>(NULL) and <is_not_null_test>(NULL))))))) or (`test`.`t1`.`a` > 100))
|
EXPLAIN with subquery_cache=off:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((not(<in_optimizer>((3,3),<exists>(select NULL,NULL having (((3 = NULL) or isnull(NULL)) and ((3 = NULL) or isnull(NULL)) and <is_not_null_test>(NULL) and <is_not_null_test>(NULL)))))) or (`test`.`t1`.`a` > 100))
|
Test case:
|
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
|
SELECT * FROM t1
|
WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
|
OR a > 100;
|
|
SET optimizer_switch = 'subquery_cache=off';
|
|
|
SELECT * FROM t1
|
WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
|
OR a > 100;
|
|
|
DROP TABLE t1;
|
|
|
Result:
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2),(3);
|
SELECT * FROM t1
|
WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
|
OR a > 100;
|
a
|
SET optimizer_switch = 'subquery_cache=off';
|
SELECT * FROM t1
|
WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
|
OR a > 100;
|
a
|
2
|
3
|
DROP TABLE t1;
|