[
https://issues.apache.org/jira/browse/SPARK-60239?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marko Radoicic updated SPARK-60239:
-----------------------------------
Description:
Pipe AGGREGATE accepts aliases on grouping expressions, but an alias on an
integer literal silently changes what the literal means. With
spark.sql.groupByOrdinal enabled (the default), a bare integer is an ordinal,
while an aliased integer is a constant grouping key:
{code:sql}
-- Groups by the second column of t (ordinal).
TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2
-- Groups by the constant 2 and outputs a column grp = 2.
TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp
{code}
The second query is ambiguous: users who write it most likely mean the ordinal.
Regular GROUP BY does not allow aliases on grouping expressions at all.
When spark.sql.groupByOrdinal is enabled, pipe AGGREGATE should reject an
aliased integer literal in GROUP BY (with or without AS, including
parenthesized and negative literals) with
UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE, the error it
already raises for its other GROUP BY restrictions, with the aliased grouping
expression as the query context:
{code}
[UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE] The feature is
not supported: The SQL pipe operator syntax with aggregation (using |>
AGGREGATE) does not support aliases on GROUP BY ordinals. SQLSTATE: 0A000
{code}
Bare ordinals and aliases on other grouping expressions keep working. With
spark.sql.groupByOrdinal disabled, integer literals are not ordinals, so an
aliased integer literal stays a valid constant grouping key. A legacy config,
spark.sql.legacy.allowAliasedPipeAggregateOrdinals, restores the previous
behavior.
was:
Pipe AGGREGATE accepts aliases on grouping expressions, but an alias on an
integer literal silently changes what the literal means. A bare integer is an
ordinal, while an aliased integer is a constant grouping key:
{code:sql}
-- Groups by the second aggregate-list position (ordinal).
TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2
-- Groups by the constant 2 and outputs a column grp = 2.
TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp
{code}
The second query is ambiguous: users who write it most likely mean the ordinal.
Regular GROUP BY does not allow aliases at all and rejects the same text:
{code:sql}
SELECT x, sum(x) FROM t GROUP BY 2 AS grp
-- [PARSE_SYNTAX_ERROR] Syntax error at or near 'grp': extra input 'grp'.
SQLSTATE: 42601
{code}
Pipe AGGREGATE should raise the same error for aliased integer literals (with
or without AS, including parenthesized and negative literals), while keeping
bare ordinals and aliases on non-ordinal expressions working.
> Reject aliased GROUP BY ordinals in pipe AGGREGATE
> --------------------------------------------------
>
> Key: SPARK-60239
> URL: https://issues.apache.org/jira/browse/SPARK-60239
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Marko Radoicic
> Priority: Major
> Labels: pull-request-available
>
> Pipe AGGREGATE accepts aliases on grouping expressions, but an alias on an
> integer literal silently changes what the literal means. With
> spark.sql.groupByOrdinal enabled (the default), a bare integer is an ordinal,
> while an aliased integer is a constant grouping key:
> {code:sql}
> -- Groups by the second column of t (ordinal).
> TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2
> -- Groups by the constant 2 and outputs a column grp = 2.
> TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp
> {code}
> The second query is ambiguous: users who write it most likely mean the
> ordinal. Regular GROUP BY does not allow aliases on grouping expressions at
> all.
> When spark.sql.groupByOrdinal is enabled, pipe AGGREGATE should reject an
> aliased integer literal in GROUP BY (with or without AS, including
> parenthesized and negative literals) with
> UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE, the error it
> already raises for its other GROUP BY restrictions, with the aliased grouping
> expression as the query context:
> {code}
> [UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE] The feature is
> not supported: The SQL pipe operator syntax with aggregation (using |>
> AGGREGATE) does not support aliases on GROUP BY ordinals. SQLSTATE: 0A000
> {code}
> Bare ordinals and aliases on other grouping expressions keep working. With
> spark.sql.groupByOrdinal disabled, integer literals are not ordinals, so an
> aliased integer literal stays a valid constant grouping key. A legacy config,
> spark.sql.legacy.allowAliasedPipeAggregateOrdinals, restores the previous
> behavior.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
