Nikolina Vraneš created SPARK-61976:
---------------------------------------
Summary: Fix join keywords after PIVOT and UNPIVOT being parsed as
table aliases
Key: SPARK-61976
URL: https://issues.apache.org/jira/browse/SPARK-61976
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Nikolina Vraneš
In the default keyword mode, PIVOT and UNPIVOT can consume a following join
keyword as the relation alias.
For example:
{code:sql}
SELECT * FROM t
PIVOT (sum(x) FOR y IN (1, 2))
LEFT JOIN u ON t.k = u.k;
SELECT * FROM t
UNPIVOT (v FOR c IN (x, y))
LEFT JOIN u ON t.k = u.k;
{code}
The parser treats LEFT as the alias and parses the remaining JOIN as an inner
join. This can change query results or cause unresolved references to the
original relation name.
The review also reports affected RIGHT, FULL, INNER, CROSS, LEFT SEMI, LEFT
ANTI, NATURAL, and NATURAL LEFT JOIN forms. OUTER spellings and ANSI keyword
mode parse correctly.
Expected behavior:
* A join keyword after PIVOT or UNPIVOT starts the join and preserves the
written join type.
* Ordinary aliases, quoted keyword aliases, and IDENTIFIER(...) aliases
continue to work.
Suggested fix:
* Use strictIdentifier for the trailing relation aliases in pivotClause and
unpivotClause in SqlBaseParser.g4.
* Update withPivot and withUnpivot in AstBuilder to read the new alias rule.
* Add a legacy configuration that restores the old alias behavior, because
released versions accept unquoted keyword aliases such as AS left.
* Add a migration-guide entry that describes the change, the quoted-alias
workaround, and the legacy configuration.
* Add parser tests for the affected join forms, both keyword modes, and both
legacy configuration values.
This ticket covers PIVOT and UNPIVOT. The analogous BIN BY fix is in
SPARK-61878 / PR #60620.
Review: https://github.com/apache/spark/pull/60620#discussion_r4603774126
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]