Avery Qi created SPARK-61489:
--------------------------------
Summary: Correlated subquery on a field of an outer struct/variant
column keys the correlation on the whole column: a join condition on the field
fails with INTERNAL_ERROR and an equality on the field is decorrelated through
a domain join on the entire struct
Key: SPARK-61489
URL: https://issues.apache.org/jira/browse/SPARK-61489
Project: Spark
Issue Type: Bug
Components: Optimizer
Affects Versions: 3.5.0, 5.0.0
Reporter: Avery Qi
The analyzer resolves a nested access of an outer column inside-out, so
c.home.zip inside a correlated subquery becomes
GetStructField(OuterReference(home), zip) rather than OuterReference(home.zip)
(an OuterReference can only wrap a NamedExpression). Everything downstream
matches on a bare OuterReference, so the whole home struct is treated as the
correlated key. PullOutNestedDataOuterRefExpressions (SPARK-40615) already
fixes this shape for map members by projecting the member onto the outer plan,
but it does not handle struct fields or variant extractions.
```
CREATE TABLE osf_c (id INT, home STRUCT<zip: INT, region: STRING>) USING
parquet;
INSERT INTO osf_c VALUES (1, named_struct('zip', 94105, 'region', 'west')),
(2, named_struct('zip', 94105, 'region', 'east')),
(3, named_struct('zip', 10001, 'region', 'ne')), (4,
NULL);
CREATE TABLE osf_o (amount INT, ship STRUCT<zip: INT, city: STRING>, pzip INT)
USING parquet;
INSERT INTO osf_o VALUES (50, named_struct('zip', 94105, 'city', 'SF'), 94105),
(70, named_struct('zip', 94105, 'city', 'San
Francisco'), 94105),
(20, named_struct('zip', 10001, 'city', 'NYC'), 10001);
```
1. Failure: correlated join condition on an outer struct field
```
SELECT c.id, (SELECT max(o.amount) FROM osf_o o JOIN (SELECT 1 AS one) t ON
o.pzip = c.home.zip)
FROM osf_c c;
```
With `spark.sql.optimizer.decorrelateJoinPredicate.enabled=true` the correlated
join predicate is pulled up by DecorrelateInnerQuery, but the outer struct
field is not a bare outer reference, so the pulled-up condition is left
unresolved and the query fails with [INTERNAL_ERROR] Unsupported correlated
subquery. Expected: (1, 70), (2, 70), (3, 20), (4, NULL).
2. Correlated equality on an outer struct field goes through a domain join on
the whole struct
SELECT c.id, (SELECT max(o.amount) FROM osf_o o WHERE o.ship.zip = c.home.zip)
FROM osf_c c;
Analyzed plan: the correlation is a struct access over the outer reference and
the subquery's outer attribute is the whole struct:
```
Project [id#936, scalar-subquery#935 [home#937] AS scalarsubquery(home)#947]
: +- Aggregate [max(amount#939) AS max(amount)#946]
: +- Filter (ship#940.zip = outer(home#937).zip)
: +- Relation osf_o[amount#939,ship#940,pzip#941] parquet
+- Relation osf_c[id#936,home#937,home_v#938] parquet
```
Optimized plan today: the predicate is not recognized as an equality on an
outer attribute, so the domain join is on the whole struct (Join LeftOuter,
(home#965 <=> home#937), aggregate grouped by home); the result is only correct
because the struct is a finer partition than the field. Expected:
PullOutNestedDataOuterRefExpressions projects home.zip onto the outer plan and
rewrites the correlation to a bare outer reference on it:
```
Project [id#936, scalarsubquery(home)#977]
+- Project [id#936, scalar-subquery#972 [home.zip#983] AS
scalarsubquery(home)#977]
: +- Aggregate [max(amount#939) AS max(amount)#976]
: +- Filter (ship#940.zip = outer(home.zip#983))
: +- Relation osf_o[amount#939,ship#940,pzip#941] parquet
+- Project [id#936, home#937, home_v#938, home#937.zip AS home.zip#983]
+- Relation osf_c[id#936,home#937,home_v#938] parquet
```
so the domain join, where still needed, is on home.zip. The same applies to
variant_get(c.home_v, '$.zip', 'int') on the outer side and to nested chains.
Affects 4.0.0+ (the map-only rule); the join-condition failure also reproduces
on 3.5 with decorrelateJoinPredicate.enabled.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]