This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new a1367259f4 [CALCITE-6939] Add support for Lateral Column Alias
a1367259f4 is described below
commit a1367259f470a1be09b1da36e473dd4f9629b79d
Author: Mihai Budiu <[email protected]>
AuthorDate: Fri Apr 4 16:35:20 2025 -0700
[CALCITE-6939] Add support for Lateral Column Alias
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../calcite/sql/advise/SqlAdvisorValidator.java | 3 +-
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 28 ++++
.../calcite/sql/validate/SqlConformanceEnum.java | 9 ++
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 165 +++++++++++++++++++--
.../calcite/runtime/CalciteResource.properties | 1 +
.../org/apache/calcite/test/CoreQuidemTest.java | 10 ++
.../org/apache/calcite/test/SqlValidatorTest.java | 65 ++++++++
core/src/test/resources/sql/misc.iq | 145 ++++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 40 +++++
12 files changed, 460 insertions(+), 17 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index ae75eace9d..45eba8f61d 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -350,6 +350,9 @@ ExInst<SqlValidatorException>
naturalOrUsingColumnNotCompatible(String a0,
@BaseMessage("Argument to function ''{0}'' must be a measure")
ExInst<SqlValidatorException> argumentMustBeMeasure(String functionName);
+ @BaseMessage("The definition of column ''{0}'' depends on itself through the
following columns: {1}")
+ ExInst<SqlValidatorException> columnIsCyclic(String columnName, String
dependentColumns);
+
@BaseMessage("Window ''{0}'' not found")
ExInst<SqlValidatorException> windowNotFound(String a0);
diff --git
a/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisorValidator.java
b/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisorValidator.java
index ddcfe9a6a9..4a13b173e8 100644
--- a/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisorValidator.java
+++ b/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisorValidator.java
@@ -36,6 +36,7 @@
import org.apache.calcite.util.Util;
import java.util.HashSet;
+import java.util.Map;
import java.util.Set;
/**
@@ -101,7 +102,7 @@ private void registerId(SqlIdentifier id, SqlValidatorScope
scope) {
}
@Override public SqlNode expandSelectExpr(SqlNode expr,
- SelectScope scope, SqlSelect select) {
+ SelectScope scope, SqlSelect select, Map<String, SqlNode> expansions) {
// Disable expansion. It doesn't help us come up with better hints.
return expr;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index bf73e6dd83..4922d390b3 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -41,6 +41,10 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
return SqlConformanceEnum.DEFAULT.isGroupByAlias();
}
+ @Override public SelectAliasLookup isSelectAlias() {
+ return SqlConformanceEnum.DEFAULT.isSelectAlias();
+ }
+
@Override public boolean isGroupByOrdinal() {
return SqlConformanceEnum.DEFAULT.isGroupByOrdinal();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 6909edcac2..a0c28cf5e3 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -102,6 +102,34 @@ public interface SqlConformance {
*/
boolean isGroupByAlias();
+ /**
+ * Value describing how to perform lookup for aliases defined in a SELECT
list.
+ * When enabled, this feature is called "lateral column alias" in Spark SQL
and Redshift,
+ * but also "inline column alias", or "self-referencing alias", "expression
alias reuse", or
+ * "alias chaining".
+ */
+ enum SelectAliasLookup {
+ /** Values defined in a SELECT list are not visible within the list
(standard SQL). */
+ UNSUPPORTED,
+ /** Values defined in a SELECT list are visible to the right of their
definition.
+ * */
+ LEFT_TO_RIGHT,
+ /** All values defined in a SELECT list can be used within the same list.
*/
+ ANY
+ }
+
+ /**
+ * Whether to allow aliases from the {@code SELECT} clause to be used as
+ * column names in the same {@code SELECT} clause.
+ * E.g., SELECT 1 as X, X+1 as Y;
+ * Name lookup considers an identifier in the same SELECT only
+ * if other lookups failed.
+ * Supported by Spark, Snowflake, Redshift, DuckDB.
+ *
+ * <p>Note: this usually requires {@link #isGroupByAlias} to also return
true.
+ */
+ SelectAliasLookup isSelectAlias();
+
/**
* Whether {@code GROUP BY 2} is interpreted to mean 'group by the 2nd column
* in the select list'.
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index d5af742e5d..0fadad1dc9 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -126,6 +126,15 @@ public enum SqlConformanceEnum implements SqlConformance {
}
}
+ @Override public SelectAliasLookup isSelectAlias() {
+ switch (this) {
+ case BABEL:
+ return SelectAliasLookup.ANY;
+ default:
+ return SelectAliasLookup.UNSUPPORTED;
+ }
+ }
+
@Override public boolean isGroupByOrdinal() {
switch (this) {
case BABEL:
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index aecf713750..bb752e5e6c 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -47,6 +47,10 @@ protected SqlDelegatingConformance(SqlConformance delegate) {
return delegate.isGroupByAlias();
}
+ @Override public SelectAliasLookup isSelectAlias() {
+ return delegate.isSelectAlias();
+ }
+
@Override public boolean isGroupByOrdinal() {
return delegate.isGroupByOrdinal();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 841b992c1c..dae940982a 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -180,6 +180,7 @@
import static java.util.Collections.emptyList;
import static java.util.Objects.requireNonNull;
+import static java.util.stream.Collectors.joining;
/**
* Default implementation of {@link SqlValidator}.
@@ -399,10 +400,11 @@ public SqlConformance getConformance() {
SqlSelect select, boolean includeSystemVars) {
final List<SqlNode> list = new ArrayList<>();
final PairList<String, RelDataType> types = PairList.of();
+ final Map<String, SqlNode> expansions = new HashMap<>();
for (final SqlNode selectItem : selectList) {
final RelDataType originalType = getValidatedNodeTypeIfKnown(selectItem);
expandSelectItem(selectItem, select, first(originalType, unknownType),
- list, catalogReader.nameMatcher().createSet(), types,
+ list, catalogReader.nameMatcher().createSet(), types, expansions,
includeSystemVars);
}
getRawSelectScopeNonNull(select).setExpandedSelectList(list);
@@ -458,12 +460,16 @@ public SqlConformance getConformance() {
* @param selectItems List that expanded items are written to
* @param aliases Set of aliases
* @param fields List of field names and types, in alias order
+ * @param expansions Maps simple identifiers defined in the current
SELECT
+ * statement to their expansions (used only when
+ * {@link SqlConformance#isSelectAlias()} requires
it).
* @param includeSystemVars If true include system vars in lists
* @return Whether the node was expanded
*/
private boolean expandSelectItem(final SqlNode selectItem, SqlSelect select,
RelDataType targetType, List<SqlNode> selectItems, Set<String> aliases,
- PairList<String, RelDataType> fields, boolean includeSystemVars) {
+ PairList<String, RelDataType> fields,
+ Map<String, SqlNode> expansions, boolean includeSystemVars) {
final SqlValidatorScope selectScope;
SqlNode expanded;
if (SqlValidatorUtil.isMeasure(selectItem)) {
@@ -480,7 +486,7 @@ private boolean expandSelectItem(final SqlNode selectItem,
SqlSelect select,
// parentheses-free functions such as LOCALTIME into explicit function
// calls.
selectScope = getSelectScope(select);
- expanded = expandSelectExpr(selectItem, scope, select);
+ expanded = expandSelectExpr(selectItem, scope, select, expansions);
}
final String alias =
SqlValidatorUtil.alias(selectItem, aliases.size());
@@ -4975,7 +4981,7 @@ protected void validateGroupClause(SqlSelect select) {
}
// Nodes in the GROUP BY clause are expressions except if they are calls
- // to the GROUPING SETS, ROLLUP or CUBE operators; this operators are not
+ // to the GROUPING SETS, ROLLUP or CUBE operators; these operators are not
// expressions, because they do not have a type.
for (SqlNode node : groupList) {
switch (node.getKind()) {
@@ -5123,6 +5129,8 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
final List<SqlNode> expandedSelectItems = new ArrayList<>();
final Set<String> aliases = new HashSet<>();
final PairList<String, RelDataType> fieldList = PairList.of();
+ // Populated during select expansion when SqlConformance.isSelectAlias !=
UNSUPPORTED
+ final Map<String, SqlNode> expansions = new HashMap<>();
for (SqlNode selectItem : selectItems) {
if (selectItem instanceof SqlSelect) {
@@ -5138,7 +5146,7 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
? targetRowType.getFieldList().get(fieldIdx).getType()
: unknownType;
expandSelectItem(selectItem, select, fieldType, expandedSelectItems,
- aliases, fieldList, false);
+ aliases, fieldList, expansions, false);
}
}
@@ -6785,8 +6793,8 @@ protected void validateFeature(
}
public SqlNode expandSelectExpr(SqlNode expr,
- SelectScope scope, SqlSelect select) {
- final Expander expander = new SelectExpander(this, scope, select);
+ SelectScope scope, SqlSelect select, Map<String, SqlNode> expansions) {
+ final Expander expander = new SelectExpander(this, scope, select,
expansions);
final SqlNode newExpr = expander.go(expr);
if (expr != newExpr) {
setOriginal(newExpr, expr);
@@ -7389,15 +7397,50 @@ private SqlNode nthSelectItem(int ordinal, final
SqlParserPos pos) {
/**
* Converts an expression into canonical form by fully-qualifying any
* identifiers. For common columns in USING, it will be converted to
- * COALESCE(A.col, B.col) AS col.
+ * COALESCE(A.col, B.col) AS col. When using a conformance that
+ * allows isSelectAlias, it expands identifiers that refer to
+ * local select expressions into the expressions themselves.
*/
static class SelectExpander extends Expander {
final SqlSelect select;
-
- SelectExpander(SqlValidatorImpl validator, SelectScope scope,
- SqlSelect select) {
+ // Maps simple identifiers to their expansions.
+ final Map<String, SqlNode> expansions;
+ // List of identifiers that are currently being looked-up. Used to detect
+ // circular dependencies when SqlConformance#isSelectAlias is ANY.
+ final Set<String> lookingUp;
+ @Nullable SqlNode root = null;
+
+ private SelectExpander(SqlValidatorImpl validator, SelectScope scope,
+ SqlSelect select, Map<String, SqlNode> expansions, Set<String>
lookingUp) {
super(validator, scope);
this.select = select;
+ this.expansions = expansions;
+ this.lookingUp = lookingUp;
+ }
+
+ /**
+ * Creates an expander for the items in a SELECT list.
+ *
+ * @param validator Validator to use.
+ * @param scope Scope to lookup identifiers in.
+ * @param select Select that is being expanded.
+ * @param expansions List of expansions computed for identifiers that are
+ * defined in the current select list and also used in
the select list.
+ * Only used if {@link SqlConformance#isSelectAlias()}
requires it.
+ */
+ private SelectExpander(SqlValidatorImpl validator, SelectScope scope,
+ SqlSelect select, Map<String, SqlNode> expansions) {
+ this(validator, scope, select, expansions, new HashSet<>());
+ }
+
+ private SelectExpander(SelectExpander expander) {
+ this(expander.validator, (SelectScope) expander.getScope(),
+ expander.select, expander.expansions, expander.lookingUp);
+ }
+
+ @Override public SqlNode go(SqlNode root) {
+ this.root = root;
+ return super.go(root);
}
@Override public @Nullable SqlNode visit(SqlIdentifier id) {
@@ -7406,9 +7449,87 @@ static class SelectExpander extends Expander {
if (node != id) {
return node;
} else {
- return super.visit(id);
+ try {
+ return super.visit(id);
+ } catch (Exception ex) {
+ if (!(ex instanceof CalciteContextException)) {
+ throw ex;
+ }
+ String message = ex.getMessage();
+ if (message != null && !message.contains("not found")) {
+ throw ex;
+ }
+ // This point is reached only if the name lookup failed using
standard rules
+ SqlConformance.SelectAliasLookup selectAlias =
validator.getConformance().isSelectAlias();
+ if (selectAlias == SqlConformance.SelectAliasLookup.UNSUPPORTED ||
!id.isSimple()) {
+ throw ex;
+ }
+ return expandAliases(id, (CalciteContextException) ex);
+ // end of catch block
+ }
}
}
+
+ // Handle "SELECT expr as X, X+1 as Y"
+ // where X is not defined previously.
+ // Try to look up the item in the select list itself.
+ private SqlNode expandAliases(SqlIdentifier id, CalciteContextException
ex) {
+ String name = id.getSimple();
+ if (lookingUp.contains(name)) {
+ final String dependentColumns = lookingUp.stream()
+ .map(s -> "'" + s + "'")
+ .collect(joining(", "));
+ throw validator.newValidationError(id,
+ RESOURCE.columnIsCyclic(name, dependentColumns));
+ }
+ SqlConformance.SelectAliasLookup selectAlias =
validator.getConformance().isSelectAlias();
+ // Check whether we already have an expansion for this identifier
+ @Nullable SqlNode expr = null;
+ if (expansions.containsKey(name)) {
+ expr = expansions.get(name);
+ } else {
+ final SqlNameMatcher nameMatcher =
+ validator.catalogReader.nameMatcher();
+ int matches = 0;
+ for (SqlNode s : select.getSelectList()) {
+ if (s == this.root && selectAlias ==
SqlConformance.SelectAliasLookup.LEFT_TO_RIGHT) {
+ // Stop lookup at the current item
+ break;
+ }
+ final String alias = SqlValidatorUtil.alias(s);
+ if (alias != null && nameMatcher.matches(alias, name)) {
+ expr = s;
+ matches++;
+ }
+ }
+ if (matches == 0) {
+ // Throw the original exception
+ throw ex;
+ } else if (matches > 1) {
+ // More than one column has this alias.
+ throw validator.newValidationError(id,
+ RESOURCE.columnAmbiguous(name));
+ }
+ expr = stripAs(requireNonNull(expr, "expr"));
+ }
+ // Recursively expand the result
+ lookingUp.add(name);
+ SelectExpander expander = new SelectExpander(this);
+ final SqlNode newExpr = expander.go(expr);
+ lookingUp.remove(name);
+ if (expr != newExpr) {
+ validator.setOriginal(newExpr, expr);
+ }
+ expr = newExpr;
+ if (expr instanceof SqlIdentifier) {
+ expr = getScope().fullyQualify((SqlIdentifier) expr).identifier;
+ }
+ if (!expansions.containsKey(name)) {
+ expansions.put(name, expr);
+ validator.setOriginal(expr, id);
+ }
+ return expr;
+ }
}
/**
@@ -7438,10 +7559,10 @@ static class ExtendedExpander extends Expander {
return super.visit(id);
}
+ final SelectScope selectScope =
validator.getRawSelectScopeNonNull(select);
final boolean replaceAliases =
clause.shouldReplaceAliases(validator.config);
if (!replaceAliases || (clause == Clause.GROUP_BY &&
!aliasOrdinalExpandSet.contains(id))) {
- final SelectScope scope = validator.getRawSelectScopeNonNull(select);
- SqlNode node = expandCommonColumn(select, id, scope, validator);
+ SqlNode node = expandCommonColumn(select, id, selectScope, validator);
if (node != id) {
return node;
}
@@ -7475,6 +7596,14 @@ static class ExtendedExpander extends Expander {
}
}
+ // expr cannot be null; in that case n = 0 would have returned
+ requireNonNull(expr, "expr");
+ if (validator.getConformance().isSelectAlias()
+ != SqlConformance.SelectAliasLookup.UNSUPPORTED) {
+ Map<String, SqlNode> expansions = new HashMap<>();
+ final Expander expander = new SelectExpander(validator, selectScope,
select, expansions);
+ expr = expander.go(expr);
+ }
expr = stripAs(expr);
if (expr instanceof SqlIdentifier) {
SqlIdentifier sid = (SqlIdentifier) expr;
@@ -8089,10 +8218,14 @@ private enum Clause {
boolean shouldReplaceAliases(Config config) {
switch (this) {
case GROUP_BY:
- return config.conformance().isGroupByAlias();
+ return config.conformance().isGroupByAlias()
+ || (config.conformance().isSelectAlias()
+ != SqlConformance.SelectAliasLookup.UNSUPPORTED);
case HAVING:
- return config.conformance().isHavingAlias();
+ return config.conformance().isHavingAlias()
+ || (config.conformance().isSelectAlias()
+ != SqlConformance.SelectAliasLookup.UNSUPPORTED);
case QUALIFY:
return true;
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 495c273169..201287134c 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -120,6 +120,7 @@ NaturalOrUsingColumnNotCompatible=Column ''{0}'' matched
using NATURAL keyword o
MeasureInAggregateQuery=MEASURE not valid in aggregate or DISTINCT query
MeasureIsCyclic=Measure ''{0}'' is cyclic; its definition depends on the
following measures: {1}
ArgumentMustBeMeasure=Argument to function ''{0}'' must be a measure
+ColumnIsCyclic=The definition of column ''{0}'' depends on itself through the
following columns: {1}
WindowNotFound=Window ''{0}'' not found
DisallowsNullTreatment=Cannot specify IGNORE NULLS or RESPECT NULLS following
''{0}''
NotGroupExpr=Expression ''{0}'' is not being grouped
diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
index 614a4e11fa..bcf14f8a15 100644
--- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
@@ -124,6 +124,16 @@ public static void main(String[] args) throws Exception {
SqlConformanceEnum.LENIENT)
.with(CalciteAssert.Config.SCOTT)
.connect();
+ case "scott-babel":
+ // Same as "scott", but uses BABEL conformance.
+ // connection
+ return CalciteAssert.that()
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.BABEL)
+ .with(CalciteAssert.Config.SCOTT)
+ .connect();
case "scott-mysql":
// Same as "scott", but uses MySQL conformance.
return CalciteAssert.that()
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 0b45476964..7be6bcf4b2 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6677,6 +6677,71 @@ public boolean isBangEqualAllowed() {
.withConformance(lenient).fails("Column 'E' not found in any table");
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6939";>
+ * [CALCITE-6939] Add support for Lateral Column Alias</a>. */
+ @Test void testAliasInSelect() {
+ final SqlConformance leftRoRight = new SqlAbstractConformance() {
+ @Override public SelectAliasLookup isSelectAlias() {
+ return SelectAliasLookup.LEFT_TO_RIGHT;
+ }
+ @Override public boolean isGroupByAlias() {
+ return true;
+ }
+ };
+ final SqlConformance any = new SqlAbstractConformance() {
+ @Override public SelectAliasLookup isSelectAlias() {
+ return SelectAliasLookup.ANY;
+ }
+ };
+
+ // Standard conformance: error
+ sql("select 1 AS x, ^x^ as Y")
+ .fails("Column 'X' not found in any table");
+ // same query, conformance that allows select to use its own aliases
+ sql("select 1 AS x, x as Y")
+ .withConformance(leftRoRight)
+ .ok();
+ // column used before it is defined
+ sql("select ^x^ as Y, 1 AS x")
+ .withConformance(leftRoRight)
+ .fails("Column 'X' not found in any table");
+ sql("select x as Y, 1 AS x")
+ .withConformance(any)
+ .ok();
+ // multiple aliases in the same select
+ sql("select 1 AS x, 2 as x, ^x^ AS y")
+ .withConformance(leftRoRight)
+ .fails("Column 'X' is ambiguous");
+ // multiple aliases in the same select
+ sql("select 1 AS x, 2 as x, ^x^ AS y")
+ .withConformance(any)
+ .fails("Column 'X' is ambiguous");
+ // Circular dependency
+ sql("select ^x^ as y, y + 1 AS x")
+ .withConformance(any)
+ .fails("The definition of column 'X' depends on itself through the
following columns: "
+ + "'X', 'Y'");
+ // Using a FROM clause with a table
+ sql("select empno AS x, x as y FROM emp")
+ .withValidatorIdentifierExpansion(true)
+ .withConformance(leftRoRight)
+ .ok();
+ // aliases are visible in group by
+ sql("select empno AS x, x as y FROM emp GROUP BY y")
+ .withValidatorIdentifierExpansion(true)
+ .withConformance(leftRoRight)
+ .ok();
+ // the following is not legal in any conformance
+ // because 'empno' is visible in the FROM clause
+ sql("select count(empno + deptno) as empno, ^empno^ as x from emp"
+ + " group by empno + deptno")
+ .fails("Expression 'EMPNO' is not being grouped")
+ .withConformance(leftRoRight)
+ .fails("Expression 'EMPNO' is not being grouped")
+ .withConformance(any)
+ .fails("Expression 'EMPNO' is not being grouped");
+ }
+
/**
* Tests validation of the aliases in HAVING.
*
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 293470029d..74c44a9798 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2520,4 +2520,149 @@ values pi();
!ok
+!use scott-babel
+# Tests for https://issues.apache.org/jira/browse/CALCITE-6939
+# [CALCITE-6939] Add support for Lateral Column Alias
+# These need the BABEL conformance level.
+
+select empno as x, x as y from emp;
++------+------+
+| X | Y |
++------+------+
+| 7369 | 7369 |
+| 7499 | 7499 |
+| 7521 | 7521 |
+| 7566 | 7566 |
+| 7654 | 7654 |
+| 7698 | 7698 |
+| 7782 | 7782 |
+| 7788 | 7788 |
+| 7839 | 7839 |
+| 7844 | 7844 |
+| 7876 | 7876 |
+| 7900 | 7900 |
+| 7902 | 7902 |
+| 7934 | 7934 |
++------+------+
+(14 rows)
+
+!ok
+
+select empno as x, x+1 as y from emp
+group by empno, empno+1;
++------+------+
+| X | Y |
++------+------+
+| 7369 | 7370 |
+| 7499 | 7500 |
+| 7521 | 7522 |
+| 7566 | 7567 |
+| 7654 | 7655 |
+| 7698 | 7699 |
+| 7782 | 7783 |
+| 7788 | 7789 |
+| 7839 | 7840 |
+| 7844 | 7845 |
+| 7876 | 7877 |
+| 7900 | 7901 |
+| 7902 | 7903 |
+| 7934 | 7935 |
++------+------+
+(14 rows)
+
+!ok
+
+# group by lateral aliases introduced
+select empno as x, x+1 as y from emp
+group by x, y;
++------+------+
+| X | Y |
++------+------+
+| 7369 | 7370 |
+| 7499 | 7500 |
+| 7521 | 7522 |
+| 7566 | 7567 |
+| 7654 | 7655 |
+| 7698 | 7699 |
+| 7782 | 7783 |
+| 7788 | 7789 |
+| 7839 | 7840 |
+| 7844 | 7845 |
+| 7876 | 7877 |
+| 7900 | 7901 |
+| 7902 | 7903 |
+| 7934 | 7935 |
++------+------+
+(14 rows)
+
+!ok
+
+# group by a mix of expressions and aliases
+select empno as x, x+1 as y from emp
+group by empno, y;
++------+------+
+| X | Y |
++------+------+
+| 7369 | 7370 |
+| 7499 | 7500 |
+| 7521 | 7522 |
+| 7566 | 7567 |
+| 7654 | 7655 |
+| 7698 | 7699 |
+| 7782 | 7783 |
+| 7788 | 7789 |
+| 7839 | 7840 |
+| 7844 | 7845 |
+| 7876 | 7877 |
+| 7900 | 7901 |
+| 7902 | 7903 |
+| 7934 | 7935 |
++------+------+
+(14 rows)
+
+!ok
+
+# having with lateral aliases
+select empno as x, x+1 as y from emp
+group by x, y
+having x > 7800;
++------+------+
+| X | Y |
++------+------+
+| 7839 | 7840 |
+| 7844 | 7845 |
+| 7876 | 7877 |
+| 7900 | 7901 |
+| 7902 | 7903 |
+| 7934 | 7935 |
++------+------+
+(6 rows)
+
+!ok
+
+# expression using new alias used in group by, and also used laterally
+select empno + deptno as z, count(z) as w, w+1 as x from emp
+group by z;
++------+---+---+
+| Z | W | X |
++------+---+---+
+| 7389 | 1 | 2 |
+| 7529 | 1 | 2 |
+| 7551 | 1 | 2 |
+| 7586 | 1 | 2 |
+| 7684 | 1 | 2 |
+| 7728 | 1 | 2 |
+| 7792 | 1 | 2 |
+| 7808 | 1 | 2 |
+| 7849 | 1 | 2 |
+| 7874 | 1 | 2 |
+| 7896 | 1 | 2 |
+| 7922 | 1 | 2 |
+| 7930 | 1 | 2 |
+| 7944 | 1 | 2 |
++------+---+---+
+(14 rows)
+
+!ok
+
# End misc.iq
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index bcf270fd82..4d8b153eac 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -12070,6 +12070,46 @@ void assertSubFunReturns(boolean binary, String s, int
start,
f0.forEachLibrary(libraries, consumer);
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6939";>
+ * [CALCITE-6939] Add support for Lateral Column Alias</a>. */
+ @Test void testAliasInSelect() {
+ final SqlOperatorFixture f = fixture()
+ // Babel sets isSelectAlias to ANY
+ .withConformance(SqlConformanceEnum.BABEL);
+ // Y uses the local X
+ f.check("select Y from (select 1 AS x, x as Y)",
+ "INTEGER NOT NULL", "1");
+ // X used twice
+ f.check("select Y from (select 1 AS x, x+x as Y)",
+ "INTEGER NOT NULL", "2");
+ // Chain of uses
+ f.check("select W from (select 1 AS x, x+x as Y, y+y as Z, z+z as W)",
+ "INTEGER NOT NULL", "8");
+ // Out of order lookup
+ f.check("SELECT W FROM (select z+z AS W, y+y AS Z, x+x AS Y, 1 AS x)",
+ "INTEGER NOT NULL", "8");
+ // Duplicate local column definition
+ f.checkFails("SELECT ^x^ FROM (select 1 AS x, 2 as x)",
+ "Column 'X' is ambiguous", false);
+ // Duplicate local column definition
+ f.checkFails("SELECT W FROM (select 1 AS x, 2 as x, ^x^ as W)",
+ "Column 'X' is ambiguous", false);
+ // Inner columns are not used if scope includes outer columns
+ f.check("SELECT W FROM (SELECT 1 AS X, X AS W FROM (SELECT 2 AS X))",
+ "INTEGER NOT NULL", "2");
+ // Circular dependency
+ f.checkFails("SELECT X FROM (SELECT ^X^ AS Y, Y AS X)",
+ "The definition of column 'X' depends on itself through "
+ + "the following columns: 'X', 'Y'", false);
+ // Circular dependency
+ f.checkFails("SELECT X FROM (SELECT ^X^ + 1 AS Y, Y + 1 AS X)",
+ "The definition of column 'X' depends on itself through "
+ + "the following columns: 'X', 'Y'", false);
+ // No circular dependency if a column is defined in an outer scope
+ f.check("SELECT X FROM (SELECT X + 1 AS Y, Y + 1 AS X FROM (SELECT 2 AS
X))",
+ "INTEGER NOT NULL", "4");
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5634";>
* [CALCITE-5634] Enable GREATEST, LEAST functions in PostgreSQL
library</a>. */
@Test void testLeastPgFunc() {
