Missing row in the result set of a query with IN subquery containing a left join

[Igor Babaev]

Some queries with IN subqueries that contain left joins may return incomplete result set.

The following test case provides such a query:

CREATE TABLE t1 (a varchar(1024));
INSERT INTO t1 VALUES ('v'), ('we');
CREATE TABLE t2 (
  a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
);
INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
CREATE TABLE t3 (b int, c int);
INSERT INTO t3 VALUES (4,4);
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='semijoin=off';
set optimizer_switch='materialization=off';
et join_cache_level=2;
SELECT * FROM t1
  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
                WHERE t2.c < 10 OR t3.c > 1);
set join_cache_level = default;
set optimizer_switch=@tmp_optimizer_switch;
DROP TABLE t1,t2,t3;

The last SELECT returns:

MariaDB [test]> SELECT * FROM t1
    -> WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
    -> WHERE t2.c < 10 OR t3.c > 1);
+------+
| a |
+------+
| v |
+------+

while the correct answer is:

MariaDB [test]> SELECT * FROM t1
    -> WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
    -> WHERE t2.c < 10 OR t3.c > 1);
+------+
| a |
+------+
| v |
| we |
+------+

[Igor Babaev (igorb-seattle)]

The above test case is a slightly modified version of the test case for bug #12546542 from mysql-5.6. code line.