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

Wrong result (wrong values) with subquery in select list, semijoin+materialization

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 5.5.55
    • Optimizer
    • None

    Description

      Note: while fixing, please check both InnoDB and MyISAM/Aria. Currently result is incorrect for all of them, but it's somewhat different.

      Test case

      CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT);
      INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3);
       
      CREATE TABLE t2 (f2 INT);
      INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
       
      --echo # t1.pk is always IN ( SELECT f2 FROM t2 ), 
      --echo so the IN condition should be true for every row, and thus COUNT(*) should always return 5
       
      SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
      

      Result with default semijoin + materialization

      MariaDB [test]> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
      +----+------+------+
      | pk | f1   | sq   |
      +----+------+------+
      |  1 |    4 |    0 |
      |  2 |    3 |    0 |
      |  3 |    3 |    0 |
      |  4 |    6 |    0 |
      |  5 |    3 |    0 |
      +----+------+------+
      5 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> EXPLAIN EXTENDED
          -> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
      +------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
      | id   | select_type        | table       | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                           |
      +------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
      |    1 | PRIMARY            | t1          | ALL    | NULL          | NULL         | NULL    | NULL |    5 |   100.00 |                                                 |
      |    2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |   100.00 |                                                 |
      |    2 | DEPENDENT SUBQUERY | t2          | ALL    | NULL          | NULL         | NULL    | NULL |    5 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      |    3 | MATERIALIZED       | t2          | ALL    | NULL          | NULL         | NULL    | NULL |    5 |   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.pk' of SELECT #2 was resolved in SELECT #1                                                                                                                                                   |
      | Note  | 1003 | select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where (`test`.`t1`.`pk` = `test`.`t2`.`f2`))) AS `sq` from `test`.`t1` |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Results with semijoin=off

      MariaDB [test]> SET optimizer_switch = 'semijoin=off';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
      +----+------+------+
      | pk | f1   | sq   |
      +----+------+------+
      |  1 |    4 |    5 |
      |  2 |    3 |    5 |
      |  3 |    3 |    5 |
      |  4 |    6 |    5 |
      |  5 |    3 |    5 |
      +----+------+------+
      5 rows in set (0.01 sec)
       
      MariaDB [test]> 
      MariaDB [test]> EXPLAIN EXTENDED
          -> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 |             |
      |    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
      |    3 | MATERIALIZED       | t2    | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 |             |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      3 rows in set, 2 warnings (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                     |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1276 | Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                                                                                                                                                                                      |
      | Note  | 1003 | select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` where <expr_cache><`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`pk`,`test`.`t1`.`pk` in ( <materialize> (select `test`.`t2`.`f2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`pk` in <temporary table> on distinct_key where ((`test`.`t1`.`pk` = `<subquery3>`.`f2`)))))))) AS `sq` from `test`.`t1` |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Reproducible on current 5.5, 10.0, 10.1; I didn't check 5.3.
      Not reproducible on MySQL 5.5, 5.6, 5.7.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.