[
https://issues.apache.org/jira/browse/SPARK-59862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18120768#comment-18120768
]
Pratham Manja commented on SPARK-59862:
---------------------------------------
I am working on this
cc [~cloud_fan]
> RewritePredicateSubquery replaces a three-valued IN subquery with a
> non-nullable existence bit
> ----------------------------------------------------------------------------------------------
>
> Key: SPARK-59862
> URL: https://issues.apache.org/jira/browse/SPARK-59862
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.0.1, 4.2.0
> Reporter: Pratham Manja
> Priority: Major
>
> {\{RewritePredicateSubquery}} rewrites a positive \{{IN}} subquery nested in
> an expression into an
> {\{ExistenceJoin}} and replaces it with the join's \{{exists}} attribute,
> which is created
> {\{nullable = false}}:
>
> {code:scala}
> val exists = AttributeReference("exists", BooleanType, nullable = false)()
> {code}
>
> {\{IN}} is three-valued: TRUE when a row matches, UNKNOWN when no row matches
> and a comparison is
> unknown, FALSE otherwise. The \{{exists}} attribute is FALSE where \{{IN}} is
> UNKNOWN, so the third
> value is lost. That is unobservable while the value is only tested for being
> TRUE, since a Filter
> drops both UNKNOWN and FALSE, but not once the value reaches anything else.
>
> h3. Reproduction
>
> On 4.0.1 and on master, with a NULL in \{{t3}} that matches nothing:
>
> {code:sql}
> create or replace temp view t1 as select * from values (1), (2), (3) as t1(a);
> create or replace temp view t2 as select * from values (1), (8), (9) as
> t2(c1);
> create or replace temp view t3 as select * from values (3), (cast(null as
> int)) as t3(col1);
>
> -- 1. projection: the IN value is returned
> select a, ((a in (select col1 from t3)) <=> false) from t1;
> -- Spark: true, true, false
> -- PostgreSQL, DuckDB: false, false, false
>
> -- 2. filter, outer-plan route: the IN value feeds <=>, not the predicate
> directly
> select * from t1 where exists (
> select 1 from t2 where a = c1 or ((a in (select col1 from t3)) <=> false));
> -- Spark: 1, 2
> -- PostgreSQL, DuckDB: 1
> {code}
>
> {\{a in (select col1 from t3)}} is UNKNOWN for every \{{a}} here, and
> \{{UNKNOWN <=> false}} is FALSE,
> while \{{exists <=> false}} is TRUE.
>
> h3. Relationship to SPARK-59351
>
> SPARK-59351 adds a second route, for a nested subquery that references the
> subquery plan rather than
> the outer plan. Rather than inherit this defect on the new route, that PR
> rejects the observable
> shapes there with
> {\{UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.NESTED_IN_SUBQUERY_WITH_UNKNOWN_RESULT}}.
> The outer-plan
> route and projections, which the reproductions above use, keep the released
> behaviour and are the subject of this issue.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
