[
https://issues.apache.org/jira/browse/SPARK-60282?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60282:
-----------------------------------
Labels: pull-request-available (was: )
> ASOF JOIN mishandles hidden USING columns from its inputs in both analyzers
> ---------------------------------------------------------------------------
>
> Key: SPARK-60282
> URL: https://issues.apache.org/jira/browse/SPARK-60282
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.4.0
> Reporter: Luka Zdravic
> Priority: Minor
> Labels: pull-request-available
>
> Hidden USING columns (for example d.k after r LEFT JOIN d USING (k)) don't
> work correctly with ASOF JOIN. There are two separate bugs, one in each
> analyzer. Each one makes dual-run
> fail.
>
> Setup:
> {code:sql}
> SET spark.sql.join.asofJoin.enabled=true;
> CREATE OR REPLACE TEMP VIEW using_left(k, lts) AS VALUES (1, 10), (2, 20),
> (8, 30);
> CREATE OR REPLACE TEMP VIEW using_right(k, rts) AS VALUES (1, 5), (2, 12),
> (8, 15);
> CREATE OR REPLACE TEMP VIEW using_keys(k) AS VALUES (1), (2);
> {code}
>
> 1. Fixed-point: ASOF JOIN ... USING drops the hidden columns of its inputs.
> {code:sql}
> SELECT l.lts, d.k
> FROM using_left l ASOF JOIN (using_right r LEFT JOIN using_keys d USING (k))
> MATCH_CONDITION (l.lts >= r.rts) USING (k);
> {code}
> Fixed-point: UNRESOLVED_COLUMN for d.k
> Single-pass: (10, 1), (20, 2), (30, NULL)
> Dual-run: FIXED_POINT_FAILED_SINGLE_PASS_SUCCEEDED
> With JOIN ... USING (k) instead of the ASOF JOIN, both analyzers return the
> rows. The same happens for SELECT d.*, for a JOIN ... ON d.k above, and for a
> MATCH_CONDITION that uses d.k
> above, at any depth.
> Cause: ResolveAsOfJoin tags its USING Project with only its own hidden
> keys. Analyzer.commonNaturalJoinProcessing also adds
> project.child.metadataOutput.filter(_.qualifiedAccessOnly).
> Possible fix: add the same term in ResolveAsOfJoin.
>
> 2. Single-pass: MATCH_CONDITION reads the wrong column when the same table
> is on both sides.
> {code:sql}
> SELECT x.k
> FROM (using_right r LEFT JOIN using_keys d USING (k))
> ASOF JOIN using_keys x MATCH_CONDITION (d.k >= x.k);
> {code}
> Fixed-point: (1), (2)
> Single-pass: the analyzed plan has AsOfJoin (x.k >= x.k), and the job fails
> with INTERNAL_ERROR_ATTRIBUTE_NOT_FOUND
> Dual-run: LOGICAL_PLAN_COMPARISON_MISMATCH
> It works if x is a different table, if the hidden column is on the right
> side, or if the condition uses only visible columns.
> Cause (by reading the code): using_keys appears twice, so x gets new
> expression IDs, and its ID mapping sends d.k's ID to x.k's ID.
> AsOfJoinResolver merges the child mappings with
> createMappingFromChildMappings, which only protects the IDs of visible
> output columns. d.k is hidden, so the merged mapping keeps d.k -> x.k.
> materializedMatchConditionToJoin then
> resolves the materialized expressions again, and
> ExpressionIdAssigner.mapExpression turns d.k into x.k.
> Possible fix: in AsOfJoinResolver, also pass the IDs of the
> qualified-access-only hidden columns of both sides as newOutputIds.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
