Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2271

Join of two views with window aggregates produces incorrect results or NPE

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.16.0
    • Fix Version/s: 1.18.0
    • Component/s: core
    • Labels:
      None

      Description

      This is a continuation of CALCITE-2081. The problem wasn't really solved in scope of that ticket:

      1. It produces incorrect results, even in the unit-test which was included into the fix (function last_value(empid) shouldn't produce NULL in such query).
      2. NPE is still present for more complex queries with joined window functions (e.g. if window functions with the same name are used in both part of the join).

      Here is the example of the query, which produces NPE in 1.16.0:

      select 
       t1.l, t1.key, t2.key
      from 
       (
        select 
         dense_rank() over (order by key) l, 
         key 
        from 
         unnest(map[1,1,2,2]) k
       ) t1 
       join 
       (
        select 
         dense_rank() over(order by key) l, 
         key 
        from 
         unnest(map[2,2]) k
       ) t2 on (t1.l = t2.l and t1.key + 1 = t2.key)

      The problem is still the same - windows produces several declarations with non-unique names, which are then incorrectly merged in BlockBuilder.append method (declaration name substitution conflicts with optimization logic).

      Two variables were fixed in scope of CALCITE-2081 - 'prevStart' and 'prevEnd', but other variables are still non-unique: result and state variables (aXwY and aXsYwZ which cause NPE during initialization) and 'list' variable (causing wrong results as both aggregates are storing results into the same variable).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                Lerm Alexey Makhmutov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: