[
https://issues.apache.org/jira/browse/SPARK-59862?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pratham Manja updated SPARK-59862:
----------------------------------
Description:
{{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
h3. 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.
Related to SPARK-59351
was:
{\{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.
> 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
> h3. 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.
>
> Related to SPARK-59351
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
