[
https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dmitry Sysolyatin updated CALCITE-5388:
---------------------------------------
Description:
EnumerableWindow.implement creates expression for creating and clearing
'tempList' collection. Because state of 'tempList' is mutable, 'tempList' can
not be reused in other instance of EnumerableWindow what happens in the
following use case:
{code:java}
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{code}
Generated plan:
However, if you remove CTE.rownr2 from the selected columns it produces the 2
rows as expected:
|ROWNR1|VAL1|VAL2|
|1|1|1|
|2|2|2|
was:
This SQL produces no result.
{code:java}
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{code}
However, if you remove CTE.rownr2 from the selected columns it produces the 2
rows as expected:
|ROWNR1|VAL1|VAL2|
|1|1|1|
|2|2|2|
Same type of problem occurs of you try to compare the two rownr columns. No
result for this:
{code:java}
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.val1,
CTE2.val2
from
CTE1,
CTE2
where
CTE1.rownr1 = CTE2.rownr2{code}
Does calcite get confused over the fact that two ROW_NUMBER functions are used
among the common table expressions?
> No result when using ROW_NUMBER in two common table expressions
> ---------------------------------------------------------------
>
> Key: CALCITE-5388
> URL: https://issues.apache.org/jira/browse/CALCITE-5388
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.32.0
> Reporter: Magnus Mogren
> Assignee: Dmitry Sysolyatin
> Priority: Major
> Fix For: 1.33.0
>
>
> EnumerableWindow.implement creates expression for creating and clearing
> 'tempList' collection. Because state of 'tempList' is mutable, 'tempList' can
> not be reused in other instance of EnumerableWindow what happens in the
> following use case:
> {code:java}
> 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{code}
> Generated plan:
> However, if you remove CTE.rownr2 from the selected columns it produces the 2
> rows as expected:
> |ROWNR1|VAL1|VAL2|
> |1|1|1|
> |2|2|2|
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
