[CALCITE-5388] tempList expression inside EnumerableWindow.getPartitionIterator should be unoptimized

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.32.0
• Fix Version/s: 1.33.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Dmitry Sysolyatin
• Reporter: Magnus Mogren
• Created: 17/Nov/22 15:43
• Updated: 07/Feb/23 22:33
• Resolved: 12/Dec/22 09:22

Description

EnumerableWindow.getPartitionIterator method creates expression for creating 'tempList' array [1] and EnumerableWindow.implement method creates expression for clearing this 'tempList'. Because state of created collection is mutable, the collection can not be reused in other instance of EnumerableWindow but it happens in the following use case:

with
    CTE1(rownr1, val1) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id from (values (1), (2)) as Vals1(id) ),
    CTE2(rownr2, val2) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id from (values (1), (2)) as Vals2(id) )
select
    CTE1.rownr1,
    CTE1.val1,
    CTE2.rownr2,
    CTE2.val2 
from
    CTE1,
    CTE2 
where
    CTE1.val1 = CTE2.val2

Generated plan:

EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])
  EnumerableSort(sort0=[$1], dir0=[ASC])
    EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1], ID=[$t0])
      EnumerableWindow(window#0=[window(order by [0] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
        EnumerableValues(tuples=[[{ 1 }, { 2 }]])
  EnumerableSort(sort0=[$1], dir0=[ASC])
    EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1], ID=[$t0])
      EnumerableWindow(window#0=[window(order by [0] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
        EnumerableValues(tuples=[[{ 1 }, { 2 }]])

Calcite expression optimizer tries to remove duplicate expressions and as a result the same 'tempList' instance is used for both EnumerableWindow and the query returns empty result instead of:

ROWNR1	VAL1	VAL2
1	1	1
2	2	2

[1] https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java#L677
[2] https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java#L696
[3] https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java#L518
 

Issue Links

• relates to: CALCITE-5400 EnumerableCalc should not generate Enumerable.current with a non-idempotent function inside
• relates to: CALCITE-5426 BlockBuilder should not optimize expressions related to mutable objects to variable
• links to: GitHub Pull Request #2977