Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7599

Wrong result with ALL subquery returning NULL

    Details

      Description

      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)

      5.5 commit fdd6c111c254c5044cd9b6c2f7e4d0c74f427a79.
      Also reproducible on previous 5.5 releases, 10.0, MySQL 5.6, 5.7 (only MySQL returns 2 rows where MariaDB returns 1).

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: