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

Wrong window function calculation

    XMLWordPrintable

    Details

      Description

      Consider this query:

      with t(a, b) as (values (1, 1), (2, 1), (3, 2), (4, 2))
      select
        a,
        sum(a) over () b,
        sum(a) over (partition by b) c, 
        sum(a) over (order by a rows between unbounded preceding and 1 preceding) d
      from t
      order by a;
      

      The expected result is:

      a|b |c|d|
      -|--|-|-|
      1|10|3| |
      2|10|3|1|
      3|10|7|3|
      4|10|7|6|
      

      The actual result is:

      a|b |c|d|
      -|--|-|-|
      1|10|3|2|
      2|10|3| |
      3|10|7|3|
      4|10|7|6|
      

      This can be illustrated when not projecting column c, in case of which the problem goes away:

      with t(a, b) as (values (1, 1), (2, 1), (3, 2), (4, 2))
      select
        a,
        sum(a) over () b,
        -- sum(a) over (partition by b) c, 
        sum(a) over (order by a rows between unbounded preceding and 1 preceding) d
      from t
      order by a;
      

      The result is now correct:

      a|b |d|
      -|--|-|
      1|10| |
      2|10|1|
      3|10|3|
      4|10|6|
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            lukas.eder Lukas Eder
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated: