Luka Zdravic created SPARK-60263:
------------------------------------
Summary: Single-pass analyzer outputs a hidden USING join column
under LIMIT or a table alias
Key: SPARK-60263
URL: https://issues.apache.org/jira/browse/SPARK-60263
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.4.0
Reporter: Luka Zdravic
With the single-pass analyzer, a USING or NATURAL join can return an extra
hidden column. This happens when no operator above the join names any columns,
for example a bare FROM with LIMIT, or a table alias at the plan root. The
fixed-point analyzer returns the right columns. Under dual-run, the query fails
with OUTPUT_SCHEMA_COMPARISON_MISMATCH.
Example:
{code:sql}
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);
FROM using_right r JOIN using_keys d USING (k) LIMIT 1;
{code}
Fixed-point: [k, rts] -> (1, 5)
Single-pass: [k, rts, k] -> (1, 5, 1)
The same happens for FROM (<join>) AS s, a CTE body, UNION ALL operands, and
an ASOF JOIN that uses a hidden column in MATCH_CONDITION (see SPARK-60000).
Cause (by reading the code): the resolvers add the hidden
qualified-access-only columns to the join's Project unless the join is the plan
root. PruneMetadataColumns is meant to remove the unused ones. It starts with
an empty needed set, and LIMIT, SubqueryAlias and Union only add their own
references. A Project reached with an empty set keeps its whole list, so the
hidden column stays.
Possible fix (suggested in review of
https://github.com/apache/spark/pull/59267, not tested): start
PruneMetadataColumns at the root with the root's visible output columns,
instead of an empty set.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
