Details
Critical
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
So it returns incorrect result on second call when NULL is important (that is why the original test suite require OR).
The problem is that was_null does not survive over reinitializtion and is not set again because query is constant so it executed only once...
I see 3 ways to fix it
1) set constant subquery to constant
2) do not reset was_null & Co for constant queries
3) restore original values for was_null & Co in case we do not re-execute the query
I used way 2) (from above)
Commit e-mail subject line is:
[Commits] Rev 3552: fix for MDEV-367 in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-367/
Pushed to 5.3 (waiting for buildbot)
Pushed to 5.3
Pushed to 5.3, checked in buildbot
I see why cache fixes the problem - only the first result is correct:
SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
a ( 3, 3 ) NOT IN ( SELECT NULL, NULL )
1 NULL
2 1
3 1