Pratham Manja created SPARK-59862:
-------------------------------------
Summary: 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.2.0, 4.0.1
Reporter: Pratham Manja
{\{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]
