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:
              varun Varun Gupta
              Reporter:
              lukas.eder Lukas Eder
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: