Wrong result with ALL subquery returning NULL

CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;

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

CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;

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

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

EXPLAIN EXTENDED

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

SET SESSION optimizer_switch = "materialization=off";

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

EXPLAIN EXTENDED

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

Empty set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

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

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

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

|    1 | PRIMARY      | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using where; Using index                               |

|    2 | MATERIALIZED | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using index                                            |

|    2 | MATERIALIZED | t2x   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (flat, BNL join)                     |

|    2 | MATERIALIZED | t2y   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (incremental, BNL join) |

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

4 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                   |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`MIN(a)`)))))))) |

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

1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch = "materialization=off";

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

+------+

| a    |

+------+

|    2 |

+------+

1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

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

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

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

|    1 | PRIMARY            | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using where; Using index                               |

|    2 | DEPENDENT SUBQUERY | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using index                                            |

|    2 | DEPENDENT SUBQUERY | t2x   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (flat, BNL join)                     |

|    2 | DEPENDENT SUBQUERY | t2y   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (incremental, BNL join) |

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

4 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                    |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) having trigcond((<cache>(`test`.`t1`.`a`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))))))) |

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

1 row in set (0.00 sec)