You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@iotdb.apache.org by "DaZuiZui (via GitHub)" <gi...@apache.org> on 2026/06/04 02:43:17 UTC

[PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

DaZuiZui opened a new pull request, #17843:
URL: https://github.com/apache/iotdb/pull/17843

   ## Description
   
   This PR implements Part 1 of #17797 for the table model SQL analyzer.
   
   It allows explicit SELECT aliases to be referenced in `GROUP BY` and `ORDER BY`.
   
   For example:
   
   ```sql
   SELECT date_bin(1h, time) AS hour_time, AVG(s1) AS avg_s1
   FROM table1
   GROUP BY hour_time
   ORDER BY hour_time;
   ```
   
   The alias is resolved to the original SELECT expression during analysis, so existing semantic checks still apply after alias resolution.
   
   ### Name resolution rules
   
   This PR follows the name resolution rules discussed in #17797:
   
   - `GROUP BY` prefers input columns over SELECT aliases.
   - `ORDER BY` prefers SELECT aliases over input columns.
   - Duplicate matching SELECT aliases are rejected with an ambiguity error.
   
   ### Scope
   
   This PR only handles Part 1 of #17797:
   
   - SELECT alias in `GROUP BY`
   - SELECT alias in `ORDER BY`
   
   The following items are intentionally left out of scope for a follow-up PR:
   
   - Lateral column alias references in the SELECT list
   - Alias references in `WHERE`
   - Alias references in `HAVING`
   
   Refs #17797
   
   <hr>
   
   This PR has:
   
   - [x] been self-reviewed.
   - [x] added unit tests or modified existing tests to cover new code paths, ensuring the threshold for code coverage.
   
   <hr>
   
   ##### Key changed/added classes (or packages if there are too many classes) in this PR
   
   - `StatementAnalyzer`
   - `SelectAliasReuseTest`
   - `TestMetadata`
   
   ### Test
   
   ```bash
   ./mvnw test -pl iotdb-core/datanode -am -Dtest=SelectAliasReuseTest -DfailIfNoTests=false -Dsurefire.failIfNoSpecifiedTests=false -DskipITs
   ```
   
   Result:
   
   ```text
   Tests run: 9, Failures: 0, Errors: 0, Skipped: 0
   BUILD SUCCESS
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "JackieTien97 (via GitHub)" <gi...@apache.org>.
JackieTien97 merged PR #17843:
URL: https://github.com/apache/iotdb/pull/17843


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "DaZuiZui (via GitHub)" <gi...@apache.org>.
DaZuiZui commented on PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#issuecomment-4657163415

   Thanks @Caideyipi , @JackieTien97  for the careful review! 
   
   I’ve addressed the comments in the latest commits:
   - Fixed SELECT alias resolution for GROUP BY ROLLUP / CUBE / GROUPING SETS, including resolved-expression mapping for grouping sets.
   - Restored ORDER BY analysis to use orderByScope by default, while preserving explicit SELECT alias precedence.
   - Simplified SelectAlias to store only the canonical alias name and output position.
   - Added/updated analyzer UT coverage in SelectAliasReuseTest, including the ORDER BY window alias regression case.
   - Added table-model IT coverage in IoTDBTableAggregationIT.
   
   I also verified the fix locally with:
   - SelectAliasReuseTest
   - IoTDBTableAggregationIT#selectAliasInGroupByAndOrderByTest
   - spotless for datanode and integration-test


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "Caideyipi (via GitHub)" <gi...@apache.org>.
Caideyipi commented on PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#issuecomment-4628792737

   I found two correctness issues in the alias resolution path:
   
   1. `GROUP BY` input-column precedence currently treats outer-scope columns as input columns. `resolvesToInputColumn()` calls `scope.tryResolveField(...).isPresent()`, but that can resolve through the query boundary into a correlated outer scope. As a result, a correlated subquery such as:
   
   ```sql
   SELECT x,
          (SELECT COUNT(*) FROM table1 GROUP BY x)
   FROM table_with_x
   ```
   
   or an inner query with `SELECT expr AS x ... GROUP BY x` can have `GROUP BY x` blocked from resolving to the inner SELECT alias just because the outer query has a column named `x`. The stated rule is that `GROUP BY` prefers input columns, which should mean the current query source scope, not outer query columns. This should check the resolved field is local to the current source scope, e.g. via `ResolvedField.isLocal()` / relation id, before suppressing alias resolution.
   
   2. `ORDER BY` aliases that point to window functions are re-analyzed as window functions in the ORDER BY phase. For example:
   
   ```sql
   SELECT row_number() OVER (ORDER BY s1) AS rn
   FROM table1
   ORDER BY rn
   ```
   
   `ORDER BY rn` is rewritten to the `row_number() OVER (...)` expression, so the function is collected both in `analysis.getWindowFunctions(node)` and `analysis.getOrderByWindowFunctions(orderBy)`. `QueryPlanner` then plans SELECT window functions first and ORDER BY window functions again after switching to the ORDER BY scope, producing duplicate window planning instead of ordering by the SELECT output alias. For ORDER BY alias references, the planner should reuse the SELECT output symbol / field reference rather than treating the alias target as a fresh ORDER BY window expression.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "JackieTien97 (via GitHub)" <gi...@apache.org>.
JackieTien97 commented on code in PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#discussion_r3379346911


##########
iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java:
##########
@@ -4102,11 +4210,12 @@ private FieldReference getFieldReferenceForFillGroup(
     }
 
     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) {
         Expression expression = item.getSortKey();
+        Scope expressionScope = orderByScope;

Review Comment:
   I don't understand why we still need this? It seems that it will always be `orderByScope`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "JackieTien97 (via GitHub)" <gi...@apache.org>.
JackieTien97 commented on code in PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#discussion_r3377279342


##########
iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java:
##########
@@ -367,6 +367,74 @@ private enum UpdateKind {
     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 Expression expression;

Review Comment:
   no need to store this? For `resolveGroupBySelectAlias`, we only need `position` too. With `position`, you can just pick expression from `outputExpressions` just like previous `LongLiteral`



##########
iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java:
##########
@@ -4102,11 +4212,16 @@ private FieldReference getFieldReferenceForFillGroup(
     }
 
     private List<Expression> analyzeOrderBy(
-        Node node, List<SortItem> sortItems, Scope orderByScope) {
+        Node node,
+        List<SortItem> sortItems,
+        Scope sourceScope,
+        Scope orderByScope,

Review Comment:
   why we need another `sourceScope`?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#discussion_r3373109943


##########
iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java:
##########
@@ -4102,11 +4212,16 @@ private FieldReference getFieldReferenceForFillGroup(
     }
 
     private List<Expression> analyzeOrderBy(
-        Node node, List<SortItem> sortItems, Scope orderByScope) {
+        Node node,
+        List<SortItem> sortItems,
+        Scope sourceScope,
+        Scope orderByScope,
+        List<SelectAlias> selectAliases) {
       ImmutableList.Builder<Expression> orderByFieldsBuilder = ImmutableList.builder();
 
       for (SortItem item : sortItems) {
         Expression expression = item.getSortKey();
+        Scope expressionScope = sourceScope;

Review Comment:
   `analyzeOrderBy` now analyzes non-ordinal, non-alias ORDER BY expressions using `sourceScope` by default. That scope does not include the SELECT output fields, which contradicts the intent documented in `computeAndAssignOrderByScope` (ORDER BY should “see” both output and FROM fields) and changes prior behavior where expressions were analyzed against the ORDER BY scope. This can cause regressions for ORDER BY items that rely on output-field name resolution (including non-`AS` output names) when no explicit alias match is found.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "JackieTien97 (via GitHub)" <gi...@apache.org>.
JackieTien97 commented on code in PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#discussion_r3372264445


##########
iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java:
##########
@@ -2706,6 +2799,7 @@ private Analysis.GroupingSetAnalysis analyzeGroupBy(
                 column = outputExpressions.get(toIntExact(ordinal - 1));
                 verifyNoAggregateWindowOrGroupingFunctions(column, "GROUP BY clause");
               } else {
+                column = resolveGroupBySelectAlias(column, scope, selectAliases);

Review Comment:
   Could we apply this same alias-resolution step to the `GroupingSets` branch below as well? Right now only `SimpleGroupBy` rewrites SELECT aliases, so queries such as `SELECT s1 AS x, COUNT(*) FROM table1 GROUP BY ROLLUP(x)` still reach `analyzeExpression(column, scope)` with `x` unresolved and fail with `Column 'x' cannot be resolved`. Since `ROLLUP`, `CUBE`, and `GROUPING SETS` are still `GROUP BY` grouping elements, they should follow the same input-column precedence and SELECT-alias fallback rule.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Table: Support SELECT aliases in GROUP BY and ORDER BY [iotdb]

Posted by "DaZuiZui (via GitHub)" <gi...@apache.org>.
DaZuiZui commented on PR #17843:
URL: https://github.com/apache/iotdb/pull/17843#issuecomment-4657361181

   Updated the test result summary for the latest PR head.
   
   Verified locally:
   
   **UT**
   
   SelectAliasReuseTest
   
   - Tests run: 17
   - Failures: 0
   - Errors: 0
   - Skipped: 0
   - Result: BUILD SUCCESS
   
   **IT**
   
   IoTDBTableAggregationIT#selectAliasInGroupByAndOrderByTest
   
   - Tests run: 1
   - Failures: 0
   - Errors: 0
   - Skipped: 0
   - Result: BUILD SUCCESS
   
   IT command:
   
   ./mvnw -o verify -DskipUTs -Dit.test=IoTDBTableAggregationIT#selectAliasInGroupByAndOrderByTest -DfailIfNoTests=false -Dfailsafe.failIfNoSpecifiedTests=false -pl integration-test -am -PTableSimpleIT -P with-integration-tests
   
   Note: the PR description still mentions 12 UTs, while the latest SelectAliasReuseTest now runs 17 tests.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org