Wrong non-null result for [NOT] IN with an empty subquery as a left operand.

[Timour Katchaounov]

The following queries produce an incorrect non-NULL result when
the left operand of [NOT] IN is a subquery with an empty result set.

Test case:

CREATE TABLE t1 (f1a int, f1b int) ;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 ( f2 int);
INSERT IGNORE INTO t2 VALUES (3),(4);
CREATE TABLE t3 (f3a int, f3b int);
INSERT INTO t3 VALUES (1,1),(2,2);

set @old_optimizer_switch = @@session.optimizer_switch;
set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';

SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);

SET @@optimizer_switch = @old_optimizer_switch;

drop table t1, t2, t3;

[Philip Stoev (pstoev-askmonty)]

A wrong result was also observed when the subquery on the left side returns rows. On i7 test file /tmp//1313094482.test

[Philip Stoev (pstoev-askmonty)]

Unsimplified test case. Let me know if this test case will be used and I will simplify it.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Trigger's val_int() method which should mask pushed in->exists condition to check emptiness of original query was not called.