[ 
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]

Reply via email to