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

Wrong(empty) result after the query with window function and materialization=off'

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (i int);
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (c int);
      INSERT INTO t2 VALUES (3),(2),(3);
       
      SELECT * FROM t1 WHERE i IN (SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      SET optimizer_switch='materialization=off';
      SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      

      MariaDB [test]>  SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0,000 sec)
       
      MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+
      | 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 | 8       | test.t1.i | 1    |   100.00 | Using where     |
      |    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL         | NULL    | NULL      | 3    |   100.00 | Using temporary |
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+
      3 rows in set, 1 warning (0,000 sec)
       
      Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from  <materialize> (/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where `test`.`t1`.`i` = `<subquery2>`.`COUNT(*) OVER (PARTITION BY c)`
       
      MariaDB [test]> set optimizer_switch='materialization=off';
      Query OK, 0 rows affected (0,000 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      Empty set (0,000 sec)
       
      MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
      | 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 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL | 3    |   100.00 | Using temporary |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
      2 rows in set, 1 warning (0,001 sec)
       
      Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where <expr_cache><`test`.`t1`.`i`>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`)))
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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