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]

Reply via email to