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

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