zzwqqq created CALCITE-7888:
-------------------------------
Summary: 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
{{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)
