[
https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17635933#comment-17635933
]
Dmitry Sysolyatin edited comment on CALCITE-5388 at 11/18/22 4:06 PM:
----------------------------------------------------------------------
The reason is that two `EnumerableWindow` generate code that looks like
{code:java}
final java.util.List tempList = (java.util.List)
org.apache.calcite.linq4j.Linq4j.asEnumerable(new Integer[] {1, 2}).into(new
java.util.ArrayList());
final java.util.Iterator iterator =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator,
tempList);
.....
tempList.remove()
final java.util.Iterator iterator0 =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator0,
tempList);
{code}
because `EnumerableWindow` generates an expression for tempList using flag
optimize = true [1]. If set it to false then the query will work properly.
I am not familiar with expression optimizer code and don't know if it is
expression optimizer bug or it is expected behavior when optimizer flag = true
(If it is expected behavior then we should just set optimize flag = false)
[1]
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java#L678]
was (Author: dmsysolyatin):
The reason is that two `EnumerableWindow` generate code that looks like
{code:java}
final java.util.List tempList = (java.util.List)
org.apache.calcite.linq4j.Linq4j.asEnumerable(new Integer[] {1, 2}).into(new
java.util.ArrayList());
final java.util.Iterator iterator =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator,
tempList);
.....
tempList.remove()
final java.util.Iterator iterator0 =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator0,
tempList);
{code}
because `EnumerableWindow` generates an expression for tempList using flag
optimize = true [1]. If set it to false then the query will work properly.
I am not familiar with expression optimizer code and don't know if it is
expression optimizer bug or if it is expected behavior when optimizer flag =
true (If it is expected behavior then we should just set optimize flag = false)
[1]
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java#L678]
> 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
> Affects Versions: 1.32.0
> Reporter: Magnus Mogren
> Priority: Major
>
> 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?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
