[ https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dmitry Sysolyatin updated CALCITE-5388: --------------------------------------- Attachment: image-2022-11-23-19-40-02-478.png > 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)