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

Window function + quantified (ANY/ALL) subquery predicate over a view returns missing rows

    XMLWordPrintable

Details

    • Unexpected results

    Description

      Summary

      A SELECT that combines an aggregate window function with a quantified subquery predicate
      (col < ANY (…), col >= ALL (…)) in the `WHERE` returns wrong (missing, often zero) rows when
      the queried relation is a mergeable view rather than the base table.

      Version: 13.1.0-MariaDB-debug, source revision cded2b25e65853a75c2213cfe0832819832708bd (main, assertions on)

      Minimal repro

       
      CREATE TABLE b (id BIGINT);
      INSERT INTO b VALUES (0),(1);
      CREATE VIEW t AS SELECT id FROM b;
       
      SELECT AVG(id) OVER () FROM b WHERE id < ANY (SELECT id FROM b);  -- 1 row  (correct)
      SELECT AVG(id) OVER () FROM t WHERE id < ANY (SELECT id FROM t);  -- 0 rows (WRONG)
      

      Expected vs actual

      Query Expected Actual
      … FROM b WHERE id < ANY (SELECT id FROM b) (base table) 1 row 1 row
      … FROM t WHERE id < ANY (SELECT id FROM t) (view) 1 row *0 rows*
      … FROM t WHERE id >= ALL (SELECT id FROM t) (view) 1 row *0 rows*

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              jesse0623 Junwen An
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.