[#MDEV-367] Different results with and without subquery_cache on a query with a constant NOT IN condition

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;

SET optimizer_switch = 'subquery_cache=off';

SELECT * FROM t1

WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL )

OR a > 100;

DROP TABLE t1;

[MDEV-367] Different results with and without subquery_cache on a query with a constant NOT IN condition Created: 2012-06-25 Updated: 2012-08-27 Due: 2012-08-23 Resolved: 2012-08-27
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.25, 5.3.7
Fix Version/s:	5.5.27, 5.3.8

[MDEV-367] Different results with and without subquery_cache on a query with a constant NOT IN condition Created: 2012-06-25 Updated: 2012-08-27 Due: 2012-08-23 Resolved: 2012-08-27