This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.2
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.2 by this push:
new b89d16b1f7f branch-4.2: [fix](nereids) allow constant output column
not in GROUP BY under only_full_group_by #64458 (#68183)
b89d16b1f7f is described below
commit b89d16b1f7f566a37c73c1bb3514de2106f1ed53
Author: 924060929 <[email protected]>
AuthorDate: Sun Sep 20 10:35:29 2026 +0800
branch-4.2: [fix](nereids) allow constant output column not in GROUP BY
under only_full_group_by #64458 (#68183)
Cherry-picked from #64458
---------
Signed-off-by: Aurélien Pupier <[email protected]>
Co-authored-by: Aurélien Pupier <[email protected]>
---
.../nereids/rules/analysis/NormalizeAggregate.java | 47 +++++++++-
.../analysis/OnlyFullGroupByConstantTest.java | 102 +++++++++++++++++++++
.../test_group_by_constant_output.out | 10 ++
.../test_group_by_constant_output.groovy | 46 ++++++++++
4 files changed, 200 insertions(+), 5 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeAggregate.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeAggregate.java
index 2cc864192fb..26021b27ba2 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeAggregate.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeAggregate.java
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.analysis;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.properties.DataTrait;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
@@ -302,10 +303,42 @@ public class NormalizeAggregate implements
RewriteRuleFactory, NormalizeToSlot {
}
}
if (!missingSlotsInAggregate.isEmpty()) {
- if (SqlModeHelper.hasOnlyFullGroupBy()) {
- throw new AnalysisException(String.format("%s not in
aggregate's output", missingSlotsInAggregate
-
.stream().map(NamedExpression::getName).collect(Collectors.joining(", "))));
- } else {
+ // Under only_full_group_by, a non-grouped, non-aggregated
output column that is constant for
+ // every input row (uniform and not null) is valid (MySQL
functional dependency), e.g.
+ // SELECT a AS b, b AS c FROM (SELECT 1 AS a, 2 AS b) t
GROUP BY b, c
+ // where 'a' is a constant column of the derived table. We add
such a column to the group-by
+ // keys rather than wrapping it in any_value(): grouping by a
constant does not change the
+ // grouping, makes the column a valid output, and changes no
exprId, so references in ancestors
+ // (e.g. the result sink) stay valid -- any_value() would need
a new exprId that this rule
+ // cannot propagate upward. The redundant uniform key is later
removed by
+ // EliminateGroupByKeyByUniform. Only done when the aggregate
already has group-by keys; for a
+ // global aggregate adding a key would change empty-input
semantics. isUniformAndNotNull (not
+ // isUniform) excludes the nullable side of an outer join,
which holds the uniform value on
+ // matched rows but NULL on unmatched rows of the same group.
+ Set<Slot> constantMissingSlots = new HashSet<>();
+ if (SqlModeHelper.hasOnlyFullGroupBy() &&
!normalizedGroupExprs.isEmpty()) {
+ DataTrait childTrait =
aggregate.child().getLogicalProperties().getTrait();
+ for (Slot slot : missingSlotsInAggregate) {
+ if (childTrait.isUniformAndNotNull(slot)) {
+ constantMissingSlots.add(slot);
+ }
+ }
+ }
+ if (!constantMissingSlots.isEmpty()) {
+ bottomProjects = Sets.union(bottomProjects,
constantMissingSlots);
+ normalizedGroupExprs = ImmutableList.<Expression>builder()
+
.addAll(normalizedGroupExprs).addAll(constantMissingSlots).build();
+ for (Slot slot : constantMissingSlots) {
+ normalizedAggOutputBuilder.add(slot);
+ }
+ missingSlotsInAggregate.removeAll(constantMissingSlots);
+ }
+ if (!missingSlotsInAggregate.isEmpty()) {
+ if (SqlModeHelper.hasOnlyFullGroupBy()) {
+ throw new AnalysisException(String.format(
+ "%s not in aggregate's output",
missingSlotsInAggregate.stream()
+
.map(NamedExpression::getName).collect(Collectors.joining(", "))));
+ }
// for any slots missing in aggregate's output, we should
add a any_value(slot) into
// aggregate's output list and slot itself into bottom
project's output list
bottomProjects = Sets.union(bottomProjects,
missingSlotsInAggregate);
@@ -333,8 +366,12 @@ public class NormalizeAggregate implements
RewriteRuleFactory, NormalizeToSlot {
LogicalAggregate<?> newAggregate =
aggregate.withNormalized(normalizedGroupExprs,
normalizedAggOutputBuilder.build(), bottomPlan);
ExpressionRewriteContext rewriteContext = new
ExpressionRewriteContext(ctx);
+ // Use the current aggregate output (newAggregate already contains the
constant group-key slots added
+ // for missing slots above), not the stale normalizedAggOutput
snapshot, so constant group-key
+ // elimination keeps those outputs and does not leave dangling
references in upperProjects.
LogicalProject<Plan> project =
eliminateGroupByConstant(groupByExprContext, rewriteContext,
- normalizedGroupExprs, normalizedAggOutput, bottomProjects,
aggregate, upperProjects, newAggregate);
+ normalizedGroupExprs, newAggregate.getOutputExpressions(),
bottomProjects, aggregate,
+ upperProjects, newAggregate);
if (having.isPresent()) {
Set<Slot> havingUsedSlots = having.get().getInputSlots();
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/OnlyFullGroupByConstantTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/OnlyFullGroupByConstantTest.java
new file mode 100644
index 00000000000..efd2ed19e13
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/OnlyFullGroupByConstantTest.java
@@ -0,0 +1,102 @@
+// 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.doris.nereids.rules.analysis;
+
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.util.PlanChecker;
+import org.apache.doris.utframe.TestWithFeService;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Under only_full_group_by, a non-grouped, non-aggregated output column that
is constant for every input
+ * row (uniform and not null) is accepted (MySQL functional dependency):
NormalizeAggregate adds it to the
+ * group-by keys. Non-constant columns, and uniform-but-nullable columns from
the nullable side of an outer
+ * join, are still rejected. All cases are verified through rewrite() (not
only analyze()).
+ */
+public class OnlyFullGroupByConstantTest extends TestWithFeService {
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("test");
+ // columns 'a','b' deliberately collide with the output aliases used
below
+ createTable("CREATE TABLE test.t_ab (a INT, b INT) ENGINE=OLAP\n"
+ + "DUPLICATE KEY(a) DISTRIBUTED BY HASH(a) BUCKETS 1
PROPERTIES ('replication_num' = '1');");
+ createTable("CREATE TABLE test.t_v (k INT, v INT) ENGINE=OLAP\n"
+ + "DUPLICATE KEY(k) DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ('replication_num' = '1');");
+ }
+
+ private void rewriteOk(String sql) {
+ Assertions.assertDoesNotThrow(() ->
PlanChecker.from(connectContext).analyze(sql).rewrite().getPlan(),
+ "should plan without error: " + sql);
+ }
+
+ private void rejected(String sql) {
+ AnalysisException ex = Assertions.assertThrows(AnalysisException.class,
+ () -> PlanChecker.from(connectContext).analyze(sql).rewrite(),
"should be rejected: " + sql);
+ Assertions.assertTrue(ex.getMessage().contains("not in aggregate's
output"),
+ "unexpected message: " + ex.getMessage());
+ }
+
+ @Test
+ public void aliasCollidesWithConstantColumn() {
+ // alias 'b' collides with the derived-table column 'b', so
column-first binding groups by column 'b'
+ // and leaves the constant column 'a' ungrouped. 'a' is constant ->
allowed.
+ rewriteOk("SELECT a as b, b as c FROM (SELECT 1 as a, 2 as b) t1 GROUP
BY b, c");
+ }
+
+ @Test
+ public void constantColumnNotInGroupBy() {
+ // 'b' is constant and not grouped (a bare output column) -> allowed
+ rewriteOk("SELECT a, b FROM (SELECT 1 as a, 2 as b) t1 GROUP BY a");
+ }
+
+ @Test
+ public void constantGroupByLiteral() {
+ // group-by key is itself a constant and gets eliminated; constant
outputs a, b stay valid
+ rewriteOk("SELECT a, b FROM (SELECT 1 as a, 2 as b) t1 GROUP BY 'g'");
+ }
+
+ @Test
+ public void nonConstantColumnStillRejected() {
+ // same shape over a real table: 'a' is non-constant and ungrouped ->
still rejected (MySQL parity)
+ rejected("SELECT a as b, b as c FROM test.t_ab GROUP BY b, c");
+ }
+
+ @Test
+ public void outerJoinNullableUniformRejected() {
+ // r.v is uniform (LIMIT 1) but propagated through the nullable side
of a LEFT JOIN, so it is the
+ // uniform value on matched rows and NULL on unmatched rows of the
same group -> must stay rejected.
+ rejected("SELECT r.v FROM (SELECT 1 AS g, 1 AS k UNION ALL SELECT 1 AS
g, 2 AS k) l "
+ + "LEFT JOIN (SELECT k, v FROM test.t_v LIMIT 1) r ON l.k =
r.k GROUP BY l.g");
+ }
+
+ @Test
+ public void mixedReportOnlyNonConstant() {
+ // group by b2; 'k' (constant 1) is allowed, 'a' (non-constant) must
be reported and 'k' must not.
+ AnalysisException ex = Assertions.assertThrows(AnalysisException.class,
+ () -> PlanChecker.from(connectContext).analyze(
+ "SELECT x.a, x.k, x.b2 FROM (SELECT a, 1 as k, b as b2
FROM test.t_ab) x GROUP BY x.b2")
+ .rewrite());
+ Assertions.assertTrue(ex.getMessage().contains("a not in aggregate's
output"),
+ "should report 'a': " + ex.getMessage());
+ Assertions.assertFalse(ex.getMessage().contains("k not in aggregate"),
+ "should NOT report constant 'k': " + ex.getMessage());
+ }
+}
diff --git
a/regression-test/data/nereids_syntax_p0/test_group_by_constant_output.out
b/regression-test/data/nereids_syntax_p0/test_group_by_constant_output.out
new file mode 100644
index 00000000000..37b8301f343
--- /dev/null
+++ b/regression-test/data/nereids_syntax_p0/test_group_by_constant_output.out
@@ -0,0 +1,10 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !const_alias_collision --
+1 2
+
+-- !const_not_in_groupby --
+1 2
+
+-- !const_group_key --
+1 2
+
diff --git
a/regression-test/suites/nereids_syntax_p0/test_group_by_constant_output.groovy
b/regression-test/suites/nereids_syntax_p0/test_group_by_constant_output.groovy
new file mode 100644
index 00000000000..73f2d1c8bee
--- /dev/null
+++
b/regression-test/suites/nereids_syntax_p0/test_group_by_constant_output.groovy
@@ -0,0 +1,46 @@
+// 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.
+
+suite("test_group_by_constant_output") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ // The output alias 'b' collides with the derived-table column 'b', so
under only_full_group_by the
+ // column 'b' is grouped and the constant column 'a' is left ungrouped.
Because 'a' is constant for
+ // every input row it is accepted (MySQL functional-dependency behavior),
and the query returns (1, 2).
+ qt_const_alias_collision "SELECT a as b, b as c FROM (SELECT 1 as a, 2 as
b) t1 GROUP BY b, c"
+
+ // A constant column that is a bare output and not in GROUP BY is allowed.
+ qt_const_not_in_groupby "SELECT a, b FROM (SELECT 1 as a, 2 as b) t1 GROUP
BY a"
+
+ // A constant group-by key is eliminated, and the constant outputs stay
valid.
+ qt_const_group_key "SELECT a, b FROM (SELECT 1 as a, 2 as b) t1 GROUP BY
'g'"
+
+ // The same shape over a real table leaves a non-constant column
ungrouped, which is still rejected
+ // (matching MySQL, where only constant / functionally-dependent columns
are allowed).
+ sql "DROP TABLE IF EXISTS test_gb_const_t"
+ sql """
+ CREATE TABLE test_gb_const_t (a INT, b INT) ENGINE=OLAP
+ DUPLICATE KEY(a)
+ DISTRIBUTED BY HASH(a) BUCKETS 1
+ PROPERTIES ('replication_num' = '1')
+ """
+ test {
+ sql "SELECT a as b, b as c FROM test_gb_const_t GROUP BY b, c"
+ exception "not in aggregate's output"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
