[
https://issues.apache.org/jira/browse/CALCITE-7888?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18131913#comment-18131913
]
Julian Hyde commented on CALCITE-7888:
--------------------------------------
Left mark join is interesting, but let's do it in a separate case. This bug
seems to be in SubQueryRemoveRule or thereabouts, and I would like to see a fix
in that code. Whether or not we have a left mark join physical operator, we
will still want a decorrelation rule that works correctly.
> SubQueryRemoveRule produces extra rows and incorrect NULL results for
> multi-column IN
> -------------------------------------------------------------------------------------
>
> Key: CALCITE-7888
> URL: https://issues.apache.org/jira/browse/CALCITE-7888
> Project: Calcite
> Issue Type: Bug
> Reporter: zzwqqq
> Assignee: zzwqqq
> Priority: Major
>
> {{rewriteIn}} uses a LEFT JOIN to match both exact and partial-NULL rows. If
> both match the same outer row, the rewrite returns a result for each match.
> IN should return one value for that outer row, and an exact match should make
> it TRUE.
> For a non-empty subquery, the CASE also returns NULL whenever a left-hand key
> is NULL. This gives the wrong result when another column differs: {{(NULL, 2)
> = (1, 1)}} is FALSE, since {{UNKNOWN AND FALSE}} is FALSE.
> Example 1:
> {code:sql}
> with t(a, b) as (values (1, 1), (1, 1), (2, 2)),
> s(x, y) as (values (1, 1), (cast(null as integer), 1))
> select a, b, (a, b) in (select x, y from s) as p from t;
> {code}
> Expected:
> {code:java}
> +---+---+-------+
> | A | B | P |
> +---+---+-------+
> | 1 | 1 | true |
> | 1 | 1 | true |
> | 2 | 2 | false |
> +---+---+-------+
> (3 rows)
> {code}
> Actual:
> {code:java}
> +---+---+-------+
> | A | B | P |
> +---+---+-------+
> | 1 | 1 | true |
> | 1 | 1 | true |
> | 2 | 2 | false |
> | 1 | 1 | |
> | 1 | 1 | |
> +---+---+-------+
> (5 rows)
> {code}
> Example 2:
> {code:sql}
> with t(a, b) as (values (cast(null as integer), 2), (null, 1)),
> s(x, y) as (values (1, 1))
> select a, b, (a, b) in (select x, y from s) as p from t;
> {code}
> Expected:
> {code:java}
> +---+---+-------+
> | A | B | P |
> +---+---+-------+
> | | 2 | false |
> | | 1 | |
> +---+---+-------+
> (2 rows)
> {code}
> Actual:
> {code:java}
> +---+---+---+
> | A | B | P |
> +---+---+---+
> | | 1 | |
> | | 2 | |
> +---+---+---+
> (2 rows)
> {code}
> I wonder whether a *left mark join* would be a suitable long-term approach
> here. I'd like to look into its current support in Calcite and try working on
> a fix.
> [https://onecompiler.com/postgresql/45783za5b]
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
