[
https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17637815#comment-17637815
]
Magnus Mogren edited comment on CALCITE-5388 at 11/23/22 2:56 PM:
------------------------------------------------------------------
[~dmsysolyatin] found another strange thing. Should i register another bug for
this or is it something that is missing from your bugfix?
This SQL sometimes gives faulty matches.
{code:java}
with
CTE1(rand1, val1) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals1(id) ),
CTE2(rand2, val2) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals2(id) )
select
CTE1.rand1,
CTE1.val1,
CTE2.rand2,
CTE2.val2
from
CTE1,
CTE2
where
CTE1.rand1 = CTE2.rand2 {code}
Just run it a few times and you will get faulty result.
For instance:
|RAND1|VAL1|RAND2|VAL2|
|1|1|1|1|
|*{color:#ff0000}0{color}*|1|*{color:#ff0000}1{color}*|2|
or
|RAND1|VAL1|RAND2|VAL2|
|{color:#ff0000}*1*{color}|1|*{color:#ff0000}0{color}*|1|
|0|2|0|1|
was (Author: mamo):
[~dmsysolyatin] found onother strange thing. Should i register another bug for
this or is it something that is missing from your bugfix?
This SQL sometimes gives faulty matches.
{code:java}
with
CTE1(rand1, val1) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals1(id) ),
CTE2(rand2, val2) as ( select RAND_INTEGER(2), id from (values (1), (2)) as
Vals2(id) )
select
CTE1.rand1,
CTE1.val1,
CTE2.rand2,
CTE2.val2
from
CTE1,
CTE2
where
CTE1.rand1 = CTE2.rand2 {code}
Just run it a few times and you will get faulty result.
For instance:
|RAND1|VAL1|RAND2|VAL2|
|1|1|1|1|
|*{color:#FF0000}0{color}*|1|*{color:#FF0000}1{color}*|2|
or
|RAND1|VAL1|RAND2|VAL2|
|{color:#FF0000}*1*{color}|1|*{color:#FF0000}0{color}*|1|
|0|2|0|1|
> 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)
