Magnus Mogren created CALCITE-5388: -------------------------------------- Summary: 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 Reporter: Magnus Mogren
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 to ROW_NUMBER functions are used among the common table expressions? -- This message was sent by Atlassian Jira (v8.20.10#820010)