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

Wrong result for query with window function when all tables are optimized away

    XMLWordPrintable

Details

    Description

      For the following table

      create table t1 (id int);
      insert into t1 values (1), (2), (3), (2);
      

      the query

      select sum(max(id)) over (order by max(id)) from t1;
      

      returns the correct result:

      MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1;
      +--------------------------------------+
      | sum(max(id)) over (order by max(id)) |
      +--------------------------------------+
      | 3                                    |
      +--------------------------------------+
      

      However after creation of an index on t1(id) the same query returns a wrong result:

       
      MariaDB [test]> create index idx on t1(id);
      Query OK, 0 rows affected (0.05 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1;
      +--------------------------------------+
      | sum(max(id)) over (order by max(id)) |
      +--------------------------------------+
      | NULL                                 |
      +--------------------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.