This is an automated email from the ASF dual-hosted git repository.
JackieTien97 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 11a178a3672 Table: Support SELECT aliases in GROUP BY and ORDER BY
(#17843)
11a178a3672 is described below
commit 11a178a3672fa10b739a69221c1bb80afd9b8722
Author: DaZuiZui <[email protected]>
AuthorDate: Wed Jun 10 14:36:54 2026 +0800
Table: Support SELECT aliases in GROUP BY and ORDER BY (#17843)
---
.../it/query/recent/IoTDBTableAggregationIT.java | 69 ++++
.../relational/analyzer/StatementAnalyzer.java | 158 +++++++++-
.../relational/analyzer/SelectAliasReuseTest.java | 347 +++++++++++++++++++++
.../plan/relational/analyzer/TestMetadata.java | 13 +
4 files changed, 571 insertions(+), 16 deletions(-)
diff --git
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBTableAggregationIT.java
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBTableAggregationIT.java
index 3520f433a76..bd44d5db4f6 100644
---
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBTableAggregationIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBTableAggregationIT.java
@@ -5499,6 +5499,75 @@ public class IoTDBTableAggregationIT {
DATABASE_NAME);
}
+ @Test
+ public void selectAliasInGroupByAndOrderByTest() {
+ String[] expectedHeader = new String[] {"hour_time", "_col1"};
+ String[] retArray =
+ new String[] {
+ "2024-09-24T06:15:30.000Z,1,",
+ "2024-09-24T06:15:35.000Z,1,",
+ "2024-09-24T06:15:40.000Z,1,",
+ "2024-09-24T06:15:50.000Z,1,",
+ "2024-09-24T06:15:55.000Z,1,",
+ };
+ tableResultSetEqualTest(
+ "select date_bin(5s, time) as hour_time, count(*) from table1 where
device_id = 'd01' group by hour_time order by hour_time",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ expectedHeader = new String[] {"province", "input_province"};
+ retArray = new String[] {"d01,shanghai,", "d01,shanghai,"};
+ tableResultSetEqualTest(
+ "select device_id as province, province as input_province from table1
where device_id in ('d01', 'd09') order by province limit 2",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ expectedHeader = new String[] {"x"};
+ retArray = new String[] {"30,", "40,", "55,"};
+ tableResultSetEqualTest(
+ "select distinct s1 as x from table1 where device_id = 'd01' and s1 is
not null order by x",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+ tableResultSetEqualTest(
+ "select s1 as x from table1 where device_id = 'd01' and s1 is not null
order by x + 1",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ expectedHeader = new String[] {"rn"};
+ retArray = new String[] {"1,", "2,", "3,", "4,", "5,"};
+ tableResultSetEqualTest(
+ "select row_number() over (order by time) as rn from table1 where
device_id = 'd01' order by rn",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ tableAssertTestFail(
+ "select s1 as x, count(*) from table1 where device_id = 'd01' and s1
is not null group by rollup(x) order by x nulls last",
+ "Only support one groupingSet now",
+ DATABASE_NAME);
+ tableAssertTestFail(
+ "select s1 as x, count(*) from table1 where device_id = 'd01' and s1
is not null group by cube(x) order by x nulls last",
+ "Only support one groupingSet now",
+ DATABASE_NAME);
+
+ expectedHeader = new String[] {"x", "_col1"};
+ retArray = new String[] {"30,1,", "40,1,", "55,1,"};
+ tableResultSetEqualTest(
+ "select s1 as x, count(*) from table1 where device_id = 'd01' and s1
is not null group by grouping sets ((x)) order by x",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ tableAssertTestFail(
+ "select s1 as x, s2 as x, count(*) from table1 group by rollup(x)",
+ "Column alias 'x' is ambiguous",
+ DATABASE_NAME);
+ }
+
@Test
public void exceptionTest2() {
tableAssertTestFail(
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
index 8912ac37264..4ff58026a94 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
@@ -367,6 +367,68 @@ public class StatementAnalyzer {
MERGE,
}
+ private static final class SelectAnalysis {
+ private final List<Expression> outputExpressions;
+ private final List<SelectAlias> aliases;
+
+ private SelectAnalysis(List<Expression> outputExpressions,
List<SelectAlias> aliases) {
+ this.outputExpressions =
+ ImmutableList.copyOf(requireNonNull(outputExpressions,
"outputExpressions is null"));
+ this.aliases = ImmutableList.copyOf(requireNonNull(aliases, "aliases is
null"));
+ }
+
+ private List<Expression> getOutputExpressions() {
+ return outputExpressions;
+ }
+
+ private List<SelectAlias> getAliases() {
+ return aliases;
+ }
+ }
+
+ private static final class SelectAlias {
+ private final String canonicalName;
+ private final int position;
+
+ private SelectAlias(String canonicalName, int position) {
+ this.canonicalName = requireNonNull(canonicalName, "canonicalName is
null");
+ this.position = position;
+ }
+
+ private String getCanonicalName() {
+ return canonicalName;
+ }
+
+ private int getPosition() {
+ return position;
+ }
+ }
+
+ private static boolean resolvesToInputColumn(Scope scope, Identifier
identifier) {
+ return scope
+ .tryResolveField(identifier, QualifiedName.of(identifier.getValue()))
+ .filter(ResolvedField::isLocal)
+ .isPresent();
+ }
+
+ private static Optional<SelectAlias> resolveSelectAlias(
+ Identifier identifier, List<SelectAlias> aliases) {
+ List<SelectAlias> matches =
+ aliases.stream()
+ .filter(alias ->
alias.getCanonicalName().equals(identifier.getCanonicalValue()))
+ .collect(toImmutableList());
+ if (matches.size() > 1) {
+ throw new SemanticException(
+ String.format(
+ "Column alias '%s' is ambiguous at positions %s",
+ identifier.getValue(),
+ matches.stream()
+ .map(alias -> Integer.toString(alias.getPosition()))
+ .collect(Collectors.joining(", "))));
+ }
+ return matches.stream().findFirst();
+ }
+
/**
* Visitor context represents local query scope (if exists). The invariant
is that the local query
* scopes hierarchy should always have outer query scope (if provided) as
ancestor.
@@ -901,7 +963,8 @@ public class StatementAnalyzer {
List<Expression> orderByExpressions = emptyList();
if (node.getOrderBy().isPresent()) {
orderByExpressions =
- analyzeOrderBy(node, getSortItemsFromOrderBy(node.getOrderBy()),
queryBodyScope);
+ analyzeOrderBy(
+ node, getSortItemsFromOrderBy(node.getOrderBy()),
queryBodyScope, emptyList());
if ((queryBodyScope.getOuterQueryParent().isPresent() || !isTopLevel)
&& !node.getLimit().isPresent()
@@ -1193,9 +1256,10 @@ public class StatementAnalyzer {
node.getWhere().ifPresent(where -> analyzeWhere(node, sourceScope,
where));
- List<Expression> outputExpressions = analyzeSelect(node, sourceScope);
+ SelectAnalysis selectAnalysis = analyzeSelect(node, sourceScope);
+ List<Expression> outputExpressions =
selectAnalysis.getOutputExpressions();
Analysis.GroupingSetAnalysis groupByAnalysis =
- analyzeGroupBy(node, sourceScope, outputExpressions);
+ analyzeGroupBy(node, sourceScope, outputExpressions,
selectAnalysis.getAliases());
analyzeHaving(node, sourceScope);
Scope outputScope = computeAndAssignOutputScope(node, scope,
sourceScope);
@@ -1213,7 +1277,9 @@ public class StatementAnalyzer {
OrderBy orderBy = node.getOrderBy().get();
orderByScope = Optional.of(computeAndAssignOrderByScope(orderBy,
sourceScope, outputScope));
- orderByExpressions = analyzeOrderBy(node, orderBy.getSortItems(),
orderByScope.get());
+ orderByExpressions =
+ analyzeOrderBy(
+ node, orderBy.getSortItems(), orderByScope.get(),
selectAnalysis.getAliases());
if ((sourceScope.getOuterQueryParent().isPresent() || !isTopLevel)
&& !node.getLimit().isPresent()
@@ -1569,15 +1635,18 @@ public class StatementAnalyzer {
analysis.setWhere(node, predicate);
}
- private List<Expression> analyzeSelect(QuerySpecification node, Scope
scope) {
+ private SelectAnalysis analyzeSelect(QuerySpecification node, Scope scope)
{
ImmutableList.Builder<Expression> outputExpressionBuilder =
ImmutableList.builder();
ImmutableList.Builder<Analysis.SelectExpression> selectExpressionBuilder
=
ImmutableList.builder();
+ ImmutableList.Builder<SelectAlias> selectAliasBuilder =
ImmutableList.builder();
+ int outputPosition = 1;
for (SelectItem item : node.getSelect().getSelectItems()) {
if (item instanceof AllColumns) {
- analyzeSelectAllColumns(
- (AllColumns) item, node, scope, outputExpressionBuilder,
selectExpressionBuilder);
+ outputPosition +=
+ analyzeSelectAllColumns(
+ (AllColumns) item, node, scope, outputExpressionBuilder,
selectExpressionBuilder);
} else if (item instanceof SingleColumn) {
SingleColumn singleColumn = (SingleColumn) item;
Expression selectExpression = singleColumn.getExpression();
@@ -1594,10 +1663,16 @@ public class StatementAnalyzer {
for (Expression expression : expandedExpressions) {
analyzeSelectSingleColumn(
expression, node, scope, outputExpressionBuilder,
selectExpressionBuilder);
+ outputPosition++;
}
} else {
analyzeSelectSingleColumn(
selectExpression, node, scope, outputExpressionBuilder,
selectExpressionBuilder);
+ if (singleColumn.getAlias().isPresent()) {
+ Identifier alias = singleColumn.getAlias().get();
+ selectAliasBuilder.add(new
SelectAlias(alias.getCanonicalValue(), outputPosition));
+ }
+ outputPosition++;
}
} else {
throw new IllegalArgumentException(
@@ -1610,7 +1685,7 @@ public class StatementAnalyzer {
analysis.setContainsSelectDistinct();
}
- return outputExpressionBuilder.build();
+ return new SelectAnalysis(outputExpressionBuilder.build(),
selectAliasBuilder.build());
}
/**
@@ -2412,7 +2487,7 @@ public class StatementAnalyzer {
}
}
- private void analyzeSelectAllColumns(
+ private int analyzeSelectAllColumns(
AllColumns allColumns,
QuerySpecification node,
Scope scope,
@@ -2458,7 +2533,7 @@ public class StatementAnalyzer {
() ->
new NoSuchElementException(
DataNodeQueryMessages.NO_VALUE_PRESENT)));
- analyzeAllColumnsFromTable(
+ return analyzeAllColumnsFromTable(
fields,
allColumns,
node,
@@ -2467,7 +2542,6 @@ public class StatementAnalyzer {
selectExpressionBuilder,
relationType,
local);
- return;
}
}
// identifierChainBasis.get().getBasisType == FIELD or target
expression isn't a
@@ -2497,7 +2571,7 @@ public class StatementAnalyzer {
DataNodeQueryMessages.SELECT_NOT_ALLOWED_FROM_RELATION_THAT_HAS_NO);
}
- analyzeAllColumnsFromTable(
+ return analyzeAllColumnsFromTable(
fields,
allColumns,
node,
@@ -2550,7 +2624,7 @@ public class StatementAnalyzer {
return
fields.stream().filter(accessibleFields.build()::contains).collect(toImmutableList());
}
- private void analyzeAllColumnsFromTable(
+ private int analyzeAllColumnsFromTable(
List<Field> fields,
AllColumns allColumns,
QuerySpecification node,
@@ -2611,6 +2685,7 @@ public class StatementAnalyzer {
}
}
analysis.setSelectAllResultFields(allColumns,
itemOutputFieldBuilder.build());
+ return fields.size();
}
// private void analyzeAllFieldsFromRowTypeExpression(
@@ -2681,7 +2756,10 @@ public class StatementAnalyzer {
}
private Analysis.GroupingSetAnalysis analyzeGroupBy(
- QuerySpecification node, Scope scope, List<Expression>
outputExpressions) {
+ QuerySpecification node,
+ Scope scope,
+ List<Expression> outputExpressions,
+ List<SelectAlias> selectAliases) {
if (node.getGroupBy().isPresent()) {
ImmutableList.Builder<List<Set<FieldId>>> cubes =
ImmutableList.builder();
ImmutableList.Builder<List<Set<FieldId>>> rollups =
ImmutableList.builder();
@@ -2706,6 +2784,7 @@ public class StatementAnalyzer {
column = outputExpressions.get(toIntExact(ordinal - 1));
verifyNoAggregateWindowOrGroupingFunctions(column, "GROUP BY
clause");
} else {
+ column = resolveGroupBySelectAlias(column, scope,
outputExpressions, selectAliases);
verifyNoAggregateWindowOrGroupingFunctions(column, "GROUP BY
clause");
analyzeExpression(column, scope);
}
@@ -2732,13 +2811,18 @@ public class StatementAnalyzer {
}
} else if (groupingElement instanceof GroupingSets) {
GroupingSets element = (GroupingSets) groupingElement;
+ Map<NodeRef<Expression>, Expression> resolvedGroupingColumns = new
HashMap<>();
for (Expression column : groupingElement.getExpressions()) {
+ Expression originalColumn = column;
+ column = resolveGroupBySelectAlias(column, scope,
outputExpressions, selectAliases);
+ verifyNoAggregateWindowOrGroupingFunctions(column, "GROUP BY
clause");
analyzeExpression(column, scope);
if
(!analysis.getColumnReferences().contains(NodeRef.of(column))) {
throw new SemanticException(
String.format("GROUP BY expression must be a column
reference: %s", column));
}
+ resolvedGroupingColumns.put(NodeRef.of(originalColumn), column);
groupingExpressions.add(column);
}
@@ -2747,6 +2831,11 @@ public class StatementAnalyzer {
.map(
set ->
set.stream()
+ .map(
+ expression ->
+ requireNonNull(
+
resolvedGroupingColumns.get(NodeRef.of(expression)),
+ "resolved grouping expression is
null"))
.map(NodeRef::of)
.map(analysis.getColumnReferenceFields()::get)
.map(ResolvedField::getFieldId)
@@ -2807,6 +2896,25 @@ public class StatementAnalyzer {
return result;
}
+ private Expression resolveGroupBySelectAlias(
+ Expression expression,
+ Scope scope,
+ List<Expression> outputExpressions,
+ List<SelectAlias> selectAliases) {
+ if (!(expression instanceof Identifier)) {
+ return expression;
+ }
+
+ Identifier identifier = (Identifier) expression;
+ if (resolvesToInputColumn(scope, identifier)) {
+ return expression;
+ }
+
+ return resolveSelectAlias(identifier, selectAliases)
+ .map(alias -> outputExpressions.get(alias.getPosition() - 1))
+ .orElse(expression);
+ }
+
private boolean isDateBinGapFill(Expression column) {
return column instanceof FunctionCall
&& DATE_BIN
@@ -4124,7 +4232,7 @@ public class StatementAnalyzer {
}
private List<Expression> analyzeOrderBy(
- Node node, List<SortItem> sortItems, Scope orderByScope) {
+ Node node, List<SortItem> sortItems, Scope orderByScope,
List<SelectAlias> selectAliases) {
ImmutableList.Builder<Expression> orderByFieldsBuilder =
ImmutableList.builder();
for (SortItem item : sortItems) {
@@ -4140,6 +4248,11 @@ public class StatementAnalyzer {
}
expression = new FieldReference(toIntExact(ordinal - 1));
+ } else {
+ Optional<SelectAlias> selectAlias =
resolveOrderBySelectAlias(expression, selectAliases);
+ if (selectAlias.isPresent()) {
+ expression = new FieldReference(selectAlias.get().getPosition() -
1);
+ }
}
ExpressionAnalysis expressionAnalysis =
@@ -4170,6 +4283,15 @@ public class StatementAnalyzer {
return orderByFieldsBuilder.build();
}
+ private Optional<SelectAlias> resolveOrderBySelectAlias(
+ Expression expression, List<SelectAlias> selectAliases) {
+ if (!(expression instanceof Identifier)) {
+ return Optional.empty();
+ }
+
+ return resolveSelectAlias((Identifier) expression, selectAliases);
+ }
+
private void analyzeOffset(Offset node, Scope scope) {
long rowCount;
if (node.getRowCount() instanceof LongLiteral) {
@@ -5474,7 +5596,11 @@ public class StatementAnalyzer {
}
static void verifyNoAggregateWindowOrGroupingFunctions(Expression predicate,
String clause) {
- List<FunctionCall> aggregates =
extractAggregateFunctions(ImmutableList.of(predicate));
+ List<FunctionCall> aggregates =
+ ImmutableList.<FunctionCall>builder()
+ .addAll(extractAggregateFunctions(ImmutableList.of(predicate)))
+ .addAll(extractWindowFunctions(ImmutableList.of(predicate)))
+ .build();
if (!aggregates.isEmpty()) {
throw new SemanticException(
diff --git
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/SelectAliasReuseTest.java
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/SelectAliasReuseTest.java
new file mode 100644
index 00000000000..bc14e65324d
--- /dev/null
+++
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/SelectAliasReuseTest.java
@@ -0,0 +1,347 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.queryengine.plan.relational.analyzer;
+
+import org.apache.iotdb.commons.queryengine.common.SessionInfo;
+import org.apache.iotdb.commons.queryengine.common.SqlDialect;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.ArithmeticBinaryExpression;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.ExistsPredicate;
+import org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.Expression;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.FieldReference;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.FunctionCall;
+import org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.Identifier;
+import org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.OrderBy;
+import org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.Query;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.QuerySpecification;
+import org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.Statement;
+import
org.apache.iotdb.commons.queryengine.plan.relational.sql.ast.SubqueryExpression;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlanTester;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.parser.SqlParser;
+
+import org.junit.Test;
+
+import java.time.ZoneId;
+import java.util.List;
+
+import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.AnalyzerTest.analyzeStatement;
+import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.QUERY_CONTEXT;
+import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.TEST_MATADATA;
+import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertAnalyzeSemanticException;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertTrue;
+
+public class SelectAliasReuseTest {
+
+ @Test
+ public void groupByAliasUsesExpressionAndOrderByAliasUsesOutputField() {
+ String sql =
+ "SELECT date_bin(1h, time) AS hour_time, AVG(s1) AS avg_s1 "
+ + "FROM table1 GROUP BY hour_time ORDER BY hour_time";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertDateBin(
+
analyzedQuery.analysis.getGroupingSets(analyzedQuery.query).getOriginalExpressions());
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void groupByInputColumnTakesPrecedenceOverAlias() {
+ String sql = "SELECT x + 1 AS x, COUNT(s1) FROM table_with_x GROUP BY x";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertIdentifier(
+
analyzedQuery.analysis.getGroupingSets(analyzedQuery.query).getOriginalExpressions().get(0),
+ "x");
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void groupByAliasIsNotBlockedByOuterScopeColumn() {
+ String sql =
+ "SELECT x FROM table_with_x WHERE EXISTS ("
+ + "SELECT s1 AS x, COUNT(*) FROM table1 "
+ + "WHERE table_with_x.s1 = table1.s1 GROUP BY x)";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ QuerySpecification innerQuery = getExistsSubquery(analyzedQuery.query);
+ assertIdentifier(
+
analyzedQuery.analysis.getGroupingSets(innerQuery).getOriginalExpressions().get(0),
"s1");
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void groupingSetsAliasesUseResolvedExpressions() {
+ AnalyzedQuery rollup = analyze("SELECT s1 AS x, COUNT(*) FROM table1 GROUP
BY ROLLUP(x)");
+ Analysis.GroupingSetAnalysis rollupAnalysis =
rollup.analysis.getGroupingSets(rollup.query);
+ assertSingleOriginalIdentifier(rollupAnalysis, "s1");
+ assertEquals(1, rollupAnalysis.getRollups().size());
+
+ AnalyzedQuery cube = analyze("SELECT s1 AS x, COUNT(*) FROM table1 GROUP
BY CUBE(x)");
+ Analysis.GroupingSetAnalysis cubeAnalysis =
cube.analysis.getGroupingSets(cube.query);
+ assertSingleOriginalIdentifier(cubeAnalysis, "s1");
+ assertEquals(1, cubeAnalysis.getCubes().size());
+
+ AnalyzedQuery explicit =
+ analyze("SELECT s1 AS x, COUNT(*) FROM table1 GROUP BY GROUPING SETS
((x))");
+ Analysis.GroupingSetAnalysis explicitAnalysis =
+ explicit.analysis.getGroupingSets(explicit.query);
+ assertSingleOriginalIdentifier(explicitAnalysis, "s1");
+ assertEquals(1, explicitAnalysis.getOrdinarySets().size());
+ }
+
+ @Test
+ public void groupingSetsInputColumnTakesPrecedenceOverAlias() {
+ String sql = "SELECT x + 1 AS x, COUNT(s1) FROM table_with_x GROUP BY
ROLLUP(x)";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertSingleOriginalIdentifier(
+ analyzedQuery.analysis.getGroupingSets(analyzedQuery.query), "x");
+ }
+
+ @Test
+ public void groupingSetsAliasIsNotBlockedByOuterScopeColumn() {
+ String sql =
+ "SELECT x FROM table_with_x WHERE EXISTS ("
+ + "SELECT s1 AS x, COUNT(*) FROM table1 "
+ + "WHERE table_with_x.s1 = table1.s1 GROUP BY GROUPING SETS
((x)))";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ QuerySpecification innerQuery = getExistsSubquery(analyzedQuery.query);
+
assertSingleOriginalIdentifier(analyzedQuery.analysis.getGroupingSets(innerQuery),
"s1");
+ }
+
+ @Test
+ public void orderByOutputAliasTakesPrecedenceOverInputColumn() {
+ String sql = "SELECT s1 AS x FROM table_with_x ORDER BY x";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void orderByAliasWithoutInputColumn() {
+ String sql = "SELECT s1 AS x FROM table1 ORDER BY x";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void selectDistinctOrderByAliasUsesOutputField() {
+ String sql = "SELECT DISTINCT s1 AS x FROM table1 ORDER BY x";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void orderByExpressionUsesOrderByScopeWithoutAliasRewrite() {
+ String sql = "SELECT s1 AS x FROM table1 ORDER BY x + 1";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ Expression orderByExpression =
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0);
+ assertTrue(orderByExpression instanceof ArithmeticBinaryExpression);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void orderByWindowFunctionAliasReusesSelectOutputField() {
+ String sql = "SELECT row_number() OVER (ORDER BY s1) AS rn FROM table1
ORDER BY rn";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ OrderBy orderBy = analyzedQuery.query.getOrderBy().get();
+ List<FunctionCall> selectWindowFunctions =
+ analyzedQuery.analysis.getWindowFunctions(analyzedQuery.query);
+
+ assertEquals(1, selectWindowFunctions.size());
+ assertEquals("row_number",
selectWindowFunctions.get(0).getName().getSuffix());
+
assertTrue(analyzedQuery.analysis.getOrderByWindowFunctions(orderBy).isEmpty());
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void orderByWindowFunctionAliasExpressionUsesOrderByScope() {
+ String sql = "SELECT row_number() OVER (ORDER BY s1) AS rn FROM table1
ORDER BY rn + 1";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ OrderBy orderBy = analyzedQuery.query.getOrderBy().get();
+ List<FunctionCall> selectWindowFunctions =
+ analyzedQuery.analysis.getWindowFunctions(analyzedQuery.query);
+ Expression orderByExpression =
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0);
+
+ assertEquals(1, selectWindowFunctions.size());
+ assertEquals("row_number",
selectWindowFunctions.get(0).getName().getSuffix());
+
assertTrue(analyzedQuery.analysis.getOrderByWindowFunctions(orderBy).isEmpty());
+ assertTrue(orderByExpression instanceof ArithmeticBinaryExpression);
+
+ new PlanTester().createPlan(sql);
+ }
+
+ @Test
+ public void duplicateAliasesAreAmbiguous() {
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, s2 AS x FROM table1 ORDER BY x", "Column alias 'x' is
ambiguous");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, s2 AS x, COUNT(*) FROM table1 GROUP BY x",
+ "Column alias 'x' is ambiguous");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, s2 AS x, COUNT(*) FROM table1 GROUP BY ROLLUP(x)",
+ "Column alias 'x' is ambiguous");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, s2 AS x, COUNT(*) FROM table1 GROUP BY CUBE(x)",
+ "Column alias 'x' is ambiguous");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, s2 AS x, COUNT(*) FROM table1 GROUP BY GROUPING SETS
((x))",
+ "Column alias 'x' is ambiguous");
+ }
+
+ @Test
+ public void invalidAliasReferencesStillFail() {
+ assertAnalyzeSemanticException(
+ "SELECT AVG(s1) AS avg_s1 FROM table1 GROUP BY avg_s1",
+ "GROUP BY clause cannot contain aggregations");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, table1.x + 1 FROM table1", "Column 'table1.x' cannot
be resolved");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x FROM table1 ORDER BY table1.x", "Column 'table1.x'
cannot be resolved");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, COUNT(*) FROM table1 GROUP BY table1.x",
+ "Column 'table1.x' cannot be resolved");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x FROM table1 WHERE x > 1", "Column 'x' cannot be
resolved");
+
+ assertAnalyzeSemanticException(
+ "SELECT AVG(s1) AS avg_s1 FROM table1 HAVING avg_s1 > 1",
+ "Column 'avg_s1' cannot be resolved");
+
+ assertAnalyzeSemanticException(
+ "SELECT s1 + 1 AS x, x * 2 AS y FROM table1", "Column 'x' cannot be
resolved");
+ }
+
+ @Test
+ public void selectAliasDoesNotLeakIntoSubquery() {
+ assertAnalyzeSemanticException(
+ "SELECT s1 AS x, (SELECT x FROM table1) FROM table1", "Column 'x'
cannot be resolved");
+ }
+
+ @Test
+ public void ordinalAndFullExpressionsStillWork() {
+ new PlanTester()
+ .createPlan("SELECT date_bin(1h, time), AVG(s1) FROM table1 GROUP BY 1
ORDER BY 1");
+
+ new PlanTester()
+ .createPlan(
+ "SELECT date_bin(1h, time), AVG(s1) FROM table1 "
+ + "GROUP BY date_bin(1h, time) ORDER BY AVG(s1)");
+ }
+
+ @Test
+ public void dateBinGapFillAliasUsesRewrittenGroupingKey() {
+ String sql =
+ "SELECT date_bin_gapfill(1h, time) AS hour_time, AVG(s1) "
+ + "FROM table1 GROUP BY hour_time ORDER BY hour_time";
+
+ AnalyzedQuery analyzedQuery = analyze(sql);
+ assertNotNull(analyzedQuery.analysis.getGapFill(analyzedQuery.query));
+ assertDateBin(
+
analyzedQuery.analysis.getGroupingSets(analyzedQuery.query).getOriginalExpressions());
+ assertFieldReference(
+
analyzedQuery.analysis.getOrderByExpressions(analyzedQuery.query).get(0), 0);
+ }
+
+ private static AnalyzedQuery analyze(String sql) {
+ SqlParser sqlParser = new SqlParser();
+ Statement statement = sqlParser.createStatement(sql,
ZoneId.systemDefault(), null);
+ SessionInfo session =
+ new SessionInfo(0, "test", ZoneId.systemDefault(), "testdb",
SqlDialect.TABLE);
+ Analysis analysis =
+ analyzeStatement(statement, TEST_MATADATA, QUERY_CONTEXT, sqlParser,
session);
+ Query query = (Query) statement;
+ return new AnalyzedQuery(analysis, (QuerySpecification)
query.getQueryBody());
+ }
+
+ private static void assertDateBin(List<Expression> expressions) {
+ assertEquals(1, expressions.size());
+ assertTrue(expressions.get(0) instanceof FunctionCall);
+ assertEquals("date_bin", ((FunctionCall)
expressions.get(0)).getName().getSuffix());
+ }
+
+ private static void assertSingleOriginalIdentifier(
+ Analysis.GroupingSetAnalysis analysis, String name) {
+ assertEquals(1, analysis.getOriginalExpressions().size());
+ assertIdentifier(analysis.getOriginalExpressions().get(0), name);
+ }
+
+ private static void assertIdentifier(Expression expression, String name) {
+ assertTrue(expression instanceof Identifier);
+ assertEquals(name, ((Identifier) expression).getValue());
+ }
+
+ private static void assertFieldReference(Expression expression, int index) {
+ assertTrue(expression instanceof FieldReference);
+ assertEquals(index, ((FieldReference) expression).getFieldIndex());
+ }
+
+ private static QuerySpecification getExistsSubquery(QuerySpecification
query) {
+ assertTrue(query.getWhere().get() instanceof ExistsPredicate);
+ Expression subquery = ((ExistsPredicate)
query.getWhere().get()).getSubquery();
+ assertTrue(subquery instanceof SubqueryExpression);
+ return (QuerySpecification) ((SubqueryExpression)
subquery).getQuery().getQueryBody();
+ }
+
+ private static class AnalyzedQuery {
+ private final Analysis analysis;
+ private final QuerySpecification query;
+
+ private AnalyzedQuery(Analysis analysis, QuerySpecification query) {
+ this.analysis = analysis;
+ this.query = query;
+ }
+ }
+}
diff --git
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java
index de97615321f..ac266f19398 100644
---
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java
+++
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java
@@ -112,6 +112,7 @@ public class TestMetadata implements Metadata {
public static final String DB1 = "testdb";
public static final String TREE_DB1 = "root.test";
public static final String TABLE1 = "table1";
+ public static final String TABLE_WITH_X = "table_with_x";
public static final String TIME = "time";
private static final String TAG1 = "tag1";
private static final String TAG2 = "tag2";
@@ -121,6 +122,7 @@ public class TestMetadata implements Metadata {
private static final String S1 = "s1";
private static final String S2 = "s2";
private static final String S3 = "s3";
+ private static final String X = "x";
private static final ColumnMetadata TIME_CM = new ColumnMetadata(TIME,
TIMESTAMP);
private static final ColumnMetadata TAG1_CM = new ColumnMetadata(TAG1,
StringType.STRING);
private static final ColumnMetadata TAG2_CM = new ColumnMetadata(TAG2,
StringType.STRING);
@@ -130,6 +132,7 @@ public class TestMetadata implements Metadata {
private static final ColumnMetadata S1_CM = new ColumnMetadata(S1, INT64);
private static final ColumnMetadata S2_CM = new ColumnMetadata(S2, INT64);
private static final ColumnMetadata S3_CM = new ColumnMetadata(S3, DOUBLE);
+ private static final ColumnMetadata X_CM = new ColumnMetadata(X, INT64);
public static final String DB2 = "db2";
public static final String TABLE2 = "table2";
@@ -144,6 +147,7 @@ public class TestMetadata implements Metadata {
public boolean tableExists(final QualifiedObjectName name) {
return name.getDatabaseName().equalsIgnoreCase(DB1)
&& (name.getObjectName().equalsIgnoreCase(TABLE1)
+ || name.getObjectName().equalsIgnoreCase(TABLE_WITH_X)
|| name.getObjectName().equalsIgnoreCase(TABLE2)
|| name.getObjectName().equalsIgnoreCase(TABLE3));
}
@@ -214,6 +218,15 @@ public class TestMetadata implements Metadata {
ColumnSchema.builder(S3_CM).setColumnCategory(TsTableColumnCategory.FIELD).build());
return Optional.of(new TableSchema(TABLE1, columnSchemas));
+ } else if (name.getObjectName().equalsIgnoreCase(TABLE_WITH_X)) {
+ final List<ColumnSchema> columnSchemas =
+ Arrays.asList(
+
ColumnSchema.builder(TIME_CM).setColumnCategory(TsTableColumnCategory.TIME).build(),
+
ColumnSchema.builder(X_CM).setColumnCategory(TsTableColumnCategory.FIELD).build(),
+
ColumnSchema.builder(S1_CM).setColumnCategory(TsTableColumnCategory.FIELD).build(),
+
ColumnSchema.builder(S2_CM).setColumnCategory(TsTableColumnCategory.FIELD).build());
+
+ return Optional.of(new TableSchema(TABLE_WITH_X, columnSchemas));
} else {
List<ColumnSchema> columnSchemas =
Arrays.asList(
