[
https://issues.apache.org/jira/browse/CALCITE-7888?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7888:
----------------------------
Description:
{{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]
was:
{{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
> 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)
