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

Wrong result (extra rows) with straight join, view, constant table and a mix of aggregate and non-aggregate

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t1 (a INT, b INT);
      INSERT INTO t1 VALUES (2,9),(0,0);
       
      CREATE TABLE t2 (c INT PRIMARY KEY);
      CREATE VIEW v2 AS SELECT * FROM t2;
       
      SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      

      10.4 c400a73d

      MariaDB [test]> SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      Empty set (0.001 sec)
       
      MariaDB [test]> SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      +------+------+
      | a    | b    |
      +------+------+
      |    0 |    0 |
      +------+------+
      1 row in set (0.001 sec)
      

      The empty result appears to be correct, because the subquery should return NULL, 0, not 0,0.

      Reproducible on 5.5-10.5 with at least MyISAM and InnoDB.
      Not reproducible on MySQL 5.6, 5.7, I didn't try other versions.

      Execution plans:

      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      +------+--------------+-------------+--------+---------------+--------------+---------+---------------------+------+----------+-------------+
      | 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 | Using where |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 12      | test.t1.a,test.t1.b | 1    |   100.00 | Using where |
      |    2 | MATERIALIZED | t2          | index  | NULL          | PRIMARY      | 4       | NULL                | 1    |   100.00 | Using index |
      +------+--------------+-------------+--------+---------------+--------------+---------+---------------------+------+----------+-------------+
      3 rows in set, 1 warning (0.003 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                             |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select `test`.`t2`.`c`,count(0) from `test`.`t2`) join `test`.`t1` where `<subquery2>`.`c` = `test`.`t1`.`a` and `test`.`t1`.`b` = `<subquery2>`.`COUNT(*)` |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 );
      +------+--------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
      | 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 | Using where |
      |    2 | MATERIALIZED | t2    | index | NULL          | PRIMARY | 4       | NULL | 1    |   100.00 | Using index |
      +------+--------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
      2 rows in set, 1 warning (0.001 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`c`,count(0) from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c` and `test`.`t1`.`b` = `<subquery2>`.`COUNT(*)`)))) |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Given the combination of oddities in the scenario, I'm setting it to Minor.

        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: