[
https://issues.apache.org/jira/browse/SPARK-61489?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-61489:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
>
> 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]