[jira] [Commented] (CALCITE-5588) ArrayIndexOutOfBoundsException on "on not exists" or "on exists"

["Benchao Li (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5588?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17708223#comment-17708223 ] 

Benchao Li commented on CALCITE-5588:
-------------------------------------

bq. IMO, this is a similar issue with CALCITE-4913, that we should deduplicate the variables used more than once for Join condition too.

After looking deeper into the problem, I found that besides the above problem, there is another problem that we actually do not support rewriting sub-queries in {{Join}} condition when the sub-query contains field references from {{Join}}, see {{RelOptRulesTest#testExpandJoinIn}} (the query in this Jira uses correlated exists, this is also not supported).

> ArrayIndexOutOfBoundsException on "on not exists" or "on exists"
> ----------------------------------------------------------------
>
>                 Key: CALCITE-5588
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5588
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.33.0
>            Reporter: Magnus Mogren
>            Priority: Major
>
> Execute this SQL
> {code:java}
> select Header.Name from ( VALUES (1, 'A'), (2, 'B')) as Header(Id, Name) join (values (11, 1), (12, 1), (21, 2)) as Version(Id, Parent) on not exists (select 1 from (values (11, 1), (12, 1), (21, 2)) as Version2(Id, Parent) where Version2.Parent = Header.Id and Version2.Id > Version.Id) {code}
>  
> *Expected result*
> A ResultSet containing this
> |*NAME*|
> |A|
> |A|
> |B|
>  
> *Actual result*
> {color:#FF0000}ArrayIndexOutOfBoundsException: Index 2 out of bounds for length 2{color}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)