[
https://issues.apache.org/jira/browse/CALCITE-6939?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17941703#comment-17941703
]
Julian Hyde commented on CALCITE-6939:
--------------------------------------
In my opinion, Spark don't have a great record for well-designed extensions to
SQL. I generally place less weight on their extensions than Postgres, Oracle or
Snowflake. Can you send me a link to the Snowflake and BigQuery specifications?
Here's an idea. The measures proposal allows a column to be referenced in the
SELECT clause if it is declared using 'AS MEASURE'. For example:
{code:java}
SELECT *,
SUM(units * price_per_unit) AS MEASURE revenue,
SUM(units * cost_per_unit) AS MEASURE cost,
revenue - cost AS MEASURE profit
FROM Orders{code}
How about adding a new keyword that allows non-measure columns to be used in
the same way?
However we slice it, this is a breaking change. A keyword allows people to opt
in to the change. Your proposal to resolve lateral references 'last' will cause
unexpected behavior when we add the next feature (and is probably already in
direct conflict with the measures proposal).
> Add support for Lateral Column Alias
> ------------------------------------
>
> Key: CALCITE-6939
> URL: https://issues.apache.org/jira/browse/CALCITE-6939
> Project: Calcite
> Issue Type: Wish
> Components: core
> Affects Versions: 1.39.0
> Reporter: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
>
> Several SQL dialects allow a SELECT to refer to aliases introduced previously
> in the same select.
> Examples are: Spark, BigQuery, Snowflake. Other names for this feature are
> "column aliasing with immediate reference", or "column alias visibility in
> the same SELECT statement".
> This feature was already proposed and implemented by [CALCITE-5248], but that
> issue and PR contains multiple other unrelated features.
> I am also proposing adding a conformance flag `isSelectAlias` which enables
> this feature. This is strictly a validator feature.
> The semantics has to be clearly defined; we do not want to change the
> semantics of any existing query. My proposal is to allow a name to be
> resolved in the current SELECT statement only if all the other name lookup
> methods have failed.
> I also think it's a good idea to introduce new aliases from left to right, so
> a expression can only refer to aliases introduced to the left of it. This
> removes a few unpleasant corner cases too.
> Another difficult problem is the handling of nondeterministic expressions.
> When you refer to a prior expression by an alias, can/is the expression
> re-evaluated? Unfortunately I believe that the current Calcite IR makes it
> difficult to represent the program in such a way that re-evaluation is
> avoided. (But this is a problem with many other Calcite rewrite rules as
> well.) This feature is still useful when applied to deterministic expressions.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
