Wrong result (missing rows) with MERGE view or derived_merge, IN subqueries

CREATE TABLE t1 (f1 int);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (f2 INT);

INSERT INTO t2 VALUES (1),(2);

CREATE TABLE t3 (f3 INT);

INSERT INTO t3 VALUES (1),(2);

SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ;

SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg;

CREATE OR REPLACE ALGORITHM=MERGE VIEW vmerge AS 

SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ;

SELECT * FROM vmerge; 

DROP VIEW vmerge;

DROP TABLE t1, t2, t3;

MariaDB [test]> SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ;

+------+

| f1   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                 |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+

|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |                                                                       |

|    1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join)      |

|    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; End temporary; Using join buffer (incremental, BNL join) |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+

3 rows in set, 2 warnings (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                           |

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1276 | Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1                                                                                                            |

| Note  | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) where ((`test`.`t2`.`f2` = `test`.`t1`.`f1`) and (`test`.`t3`.`f3` = `test`.`t1`.`f1`)) |

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg;

Empty set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg;

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

| id   | select_type        | table       | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                           |

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

|    1 | SIMPLE             | t1          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 | Using where                                     |

|    3 | DEPENDENT SUBQUERY | <subquery4> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |   100.00 |                                                 |

|    3 | DEPENDENT SUBQUERY | t2          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |

|    4 | MATERIALIZED       | t3          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 |                                                 |

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

4 rows in set, 2 warnings (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                             |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1276 | Field or reference 'test.t1.f1' of SELECT #3 was resolved in SELECT #2                                                                                                                                                                                              |

| Note  | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`t2`.`f2` from `test`.`t2` semi join (`test`.`t3`) where ((`test`.`t1`.`f1` = `test`.`t3`.`f3`) and (<cache>(`test`.`t1`.`f1`) = `test`.`t2`.`f2`)))) |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> SELECT * FROM vmerge; 

Empty set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM vmerge;

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

| id   | select_type        | table       | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                           |

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

|    1 | SIMPLE             | t1          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 | Using where                                     |

|    3 | DEPENDENT SUBQUERY | <subquery4> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |   100.00 |                                                 |

|    3 | DEPENDENT SUBQUERY | t2          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |

|    4 | MATERIALIZED       | t3          | ALL    | NULL          | NULL         | NULL    | NULL |    2 |   100.00 |                                                 |

+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+

4 rows in set, 2 warnings (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                             |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1276 | Field or reference 'test.t1.f1' of SELECT #3 was resolved in SELECT #2                                                                                                                                                                                              |

| Note  | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`t2`.`f2` from `test`.`t2` semi join (`test`.`t3`) where ((`test`.`t1`.`f1` = `test`.`t3`.`f3`) and (<cache>(`test`.`t1`.`f1`) = `test`.`t2`.`f2`)))) |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+