[
https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17636827#comment-17636827
]
Julian Hyde commented on CALCITE-5388:
--------------------------------------
[~dmsysolyatin], Can you convert your 'simple fix' into a proper fix? I'd start
by changing the jira summary to describe the problem (as opposed to the
symptom). And change the code so that it is clear what is going on.
{quote}In the future if calcite continues to use self-made optimizer{quote}
Yes, that is the likely future. I think you should make the 'self-made
optimizer' a bit better.
> 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
>
>
> 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)
