[jira] [Commented] (CALCITE-2271) Join of two views with window aggregates produces incorrect results or NPE

["Alexey Makhmutov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16446391#comment-16446391 ] 

Alexey Makhmutov commented on CALCITE-2271:
-------------------------------------------

This issue probably can be fixed in two ways – be either ensure that every variable declared in EnumerableWindow which can be incorrectly merged has unique name or by changing append logic in BlockBuilder. While the latter approach may be more correct and beneficial in the long run, it can break a lot of existing test cases and optimizations, so I’m just scared to touch it now.

However, changing EnumerableWindow (following the same logic as in CALCITE-2081) seems to be a much safer and easier approach, which seems to be fixing cases described above. So, here is the proposed PR with such fix: https://github.com/apache/calcite/pull/673.


> Join of two views with window aggregates produces incorrect results or NPE
> --------------------------------------------------------------------------
>
>                 Key: CALCITE-2271
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2271
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.16.0
>            Reporter: Alexey Makhmutov
>            Assignee: Julian Hyde
>            Priority: Major
>
> This is a continuation of CALCITE-2081. The problem wasn't really solved in scope of that ticket:
>  # 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).
>  # 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:
> {code:sql}
> 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){code}
> 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).



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)