Luka Zdravic created SPARK-60282:
------------------------------------
Summary: 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
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]
