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

    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

              • Assignee:
                igor Igor Babaev
                Reporter:
                igor Igor Babaev
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: