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

Wrong result using degenerated subquery with window function

Details

    Description

      If a query contains a degenerated subquery with window function then the query may return a wrong result.
      This can be seen for the following examples:

      CREATE TABLE t1 (a int DEFAULT 10);
      INSERT INTO t1 VALUES (7), (2), (3);
      SELECT * FROM t1 WHERE (SELECT AVG(3)) > 2;
      SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2;
      INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
      SELECT * FROM t1;
      

      For the above test case we have

      MariaDB [test]> CREATE TABLE t1 (a int DEFAULT 10);
      Query OK, 0 rows affected (0.017 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (7), (2), (3);
      Query OK, 3 rows affected (0.002 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT * FROM t1 WHERE (SELECT AVG(3)) > 2;
      +------+
      | a    |
      +------+
      |    7 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.003 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2;
      Empty set (0.000 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
      Query OK, 1 row affected (0.002 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +------+
      | a    |
      +------+
      |    7 |
      |    2 |
      |    3 |
      | NULL |
      +------+
      4 rows in set (0.001 sec)
      

      For the second select the expected result is the same as for the first select. For the last select the expected result has to contain 4 instead of NULL.

      Attachments

        Activity

          igor Igor Babaev added a comment -

          The transformation of a such a subquery

             (SELECT <expr>)
          

          into

            <expr>
          

          is not valid if <expr> contains set_functions or window function.
          The transformation was not applied when <expr> contained window function, but erroneously was applied if <expr> contained only window functions.

          igor Igor Babaev added a comment - The transformation of a such a subquery (SELECT <expr>) into <expr> is not valid if <expr> contains set_functions or window function. The transformation was not applied when <expr> contained window function, but erroneously was applied if <expr> contained only window functions.
          igor Igor Babaev added a comment -

          Rex, please review the last commit in bb-10.5-igor

          igor Igor Babaev added a comment - Rex, please review the last commit in bb-10.5-igor
          Johnston Rex Johnston added a comment -

          Makes sense to me, looks good.

          Johnston Rex Johnston added a comment - Makes sense to me, looks good.
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.5. It has to be merged upstream as it is.

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.5. It has to be merged upstream as it is.

          People

            igor Igor Babaev
            igor Igor Babaev
            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.