You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Magnus Mogren (Jira)" <ji...@apache.org> on 2022/11/17 15:44:00 UTC
[jira] [Created] (CALCITE-5388) No result when using ROW_NUMBER in two common table expressions
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)