This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1cac2c502a4 [fix](fe) Fix duplicate RelationId bug caused by subquery
in simple case when (#61813)
1cac2c502a4 is described below
commit 1cac2c502a412de104b4e3c92cbecc1c8f8a2126
Author: morrySnow <[email protected]>
AuthorDate: Fri Mar 27 21:21:45 2026 +0800
[fix](fe) Fix duplicate RelationId bug caused by subquery in simple case
when (#61813)
### What problem does this PR solve?
Problem Summary:
When parsing a simple CASE expression with a subquery as the value
(e.g., `CASE (SELECT sum(col) FROM t) WHEN 1 THEN 2 WHEN 3 THEN 4 END`),
the parser (`LogicalPlanBuilder.visitSimpleCase`) duplicated the same
subquery
expression into multiple `EqualTo` nodes across WhenClauses. Since the
subquery
carried a fixed `RelationId`, this created duplicate RelationIds in the
plan tree.
Later, different SlotIds were assigned to the same `SlotReference` in
different
copies, preventing them from being merged back into one subquery. This
caused
the **"groupExpression already exists in memo"** error.
**How to reproduce:**
```sql
CREATE TABLE test (
big_key int,
int_1 int
) PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
select case (select sum(int_1) from test)
when 1 then 2
when 3 then 4
else 5
end a3 from test;
-- Error: groupExpression already exists in memo, maybe a bug
```
**Solution:**
The fix defers `EqualTo` construction from parse time to analysis time:
1. Added a `value` field to `CaseWhen` expression, stored as
`children[0]` when present. Children layout: `[value?, WhenClause+,
defaultValue?]`
2. `LogicalPlanBuilder.visitSimpleCase` now passes the value to
`CaseWhen` directly instead of wrapping each WhenClause operand in
`EqualTo(value, operand)`
3. `ExpressionAnalyzer.visitCaseWhen` handles simple case: analyzes the
value once, then constructs `EqualTo(analyzedValue, analyzedOperand)`
per WhenClause with proper type coercion via
`TypeCoercionUtils.processComparisonPredicate`, producing a standard
searched-case `CaseWhen` (no value field). This ensures the subquery is
analyzed exactly once with a single `RelationId`.
After analysis, all downstream rules see a standard searched-case
`CaseWhen` — no changes needed elsewhere.
### Release note
Fixed a bug where using a scalar subquery as the value in a simple CASE
expression (e.g., `CASE (SELECT ...) WHEN val THEN ...`) caused a
"groupExpression already exists in memo" planning error.
Co-authored-by: Copilot <[email protected]>
---
.../doris/nereids/parser/LogicalPlanBuilder.java | 6 +-
.../nereids/rules/analysis/ExpressionAnalyzer.java | 26 +++
.../doris/nereids/trees/expressions/CaseWhen.java | 114 +++++++++----
.../nereids/trees/expressions/CaseWhenTest.java | 184 +++++++++++++++++++++
.../subquery/test_subquery_in_simple_case.out | 36 ++++
.../subquery/test_subquery_in_simple_case.groovy | 82 +++++++++
6 files changed, 410 insertions(+), 38 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index 2a4c7fa7fea..b7af838ee5b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -3278,12 +3278,12 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
public Expression visitSimpleCase(DorisParser.SimpleCaseContext context) {
Expression e = getExpression(context.value);
List<WhenClause> whenClauses = context.whenClause().stream()
- .map(w -> new WhenClause(new EqualTo(e,
getExpression(w.condition)), getExpression(w.result)))
+ .map(w -> new WhenClause(getExpression(w.condition),
getExpression(w.result)))
.collect(ImmutableList.toImmutableList());
if (context.elseExpression == null) {
- return new CaseWhen(whenClauses);
+ return new CaseWhen(e, whenClauses);
}
- return new CaseWhen(whenClauses,
getExpression(context.elseExpression));
+ return new CaseWhen(e, whenClauses,
getExpression(context.elseExpression));
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
index 3d1faf318e6..e2566c8427f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
@@ -875,6 +875,32 @@ public class ExpressionAnalyzer extends
SubExprAnalyzer<ExpressionRewriteContext
@Override
public Expression visitCaseWhen(CaseWhen caseWhen,
ExpressionRewriteContext context) {
+ if (caseWhen.getValue().isPresent()) {
+ // Simple case: CASE value WHEN cond THEN result ...
+ // Analyze value once, then construct EqualTo(analyzedValue,
analyzedCond) per WhenClause
+ Expression analyzedValue = caseWhen.getValue().get().accept(this,
context);
+
+ List<WhenClause> newWhenClauses = new ArrayList<>();
+ for (WhenClause whenClause : caseWhen.getWhenClauses()) {
+ Expression operand = whenClause.getOperand().accept(this,
context);
+ Expression result = whenClause.getResult().accept(this,
context);
+ Expression equalTo =
TypeCoercionUtils.processComparisonPredicate(
+ new EqualTo(analyzedValue, operand));
+ newWhenClauses.add(new WhenClause(equalTo, result));
+ }
+
+ CaseWhen newCaseWhen;
+ if (caseWhen.getDefaultValue().isPresent()) {
+ Expression analyzedDefault =
caseWhen.getDefaultValue().get().accept(this, context);
+ newCaseWhen = new CaseWhen(newWhenClauses, analyzedDefault);
+ } else {
+ newCaseWhen = new CaseWhen(newWhenClauses);
+ }
+ newCaseWhen.checkLegalityBeforeTypeCoercion();
+ return TypeCoercionUtils.processCaseWhen(newCaseWhen);
+ }
+
+ // Searched case: standard handling
Builder<Expression> rewrittenChildren =
ImmutableList.builderWithExpectedSize(caseWhen.arity());
for (Expression child : caseWhen.children()) {
rewrittenChildren.add(child.accept(this, context));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CaseWhen.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CaseWhen.java
index ddb3491e244..4c81393e6b0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CaseWhen.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/CaseWhen.java
@@ -17,7 +17,6 @@
package org.apache.doris.nereids.trees.expressions;
-import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.exceptions.UnboundException;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DataType;
@@ -37,33 +36,69 @@ import java.util.function.Supplier;
* The internal representation of
* CASE [expr] WHEN expr THEN expr [WHEN expr THEN expr ...] [ELSE expr] END
* Each When/Then is stored as two consecutive children (whenExpr, thenExpr).
- * If a case expr is given, convert it to equalTo(caseExpr, whenExpr) and set
it to whenExpr.
+ * If a case expr is given (simple case), the value is stored as the first
child.
+ * During analysis, the value is consumed: EqualTo(value, whenCondition) is
constructed
+ * for each WhenClause, producing a standard searched case form.
* If an else expr is given then it is the last child.
+ *
+ * Children layout: [value?, WhenClause+, defaultValue?]
*/
public class CaseWhen extends Expression implements NeedSessionVarGuard {
+ private final Optional<Expression> value;
private final List<WhenClause> whenClauses;
private final Optional<Expression> defaultValue;
- private Supplier<List<DataType>> dataTypesForCoercion;
+ private final Supplier<List<DataType>> dataTypesForCoercion;
public CaseWhen(List<WhenClause> whenClauses) {
super((List) whenClauses);
+ this.value = Optional.empty();
this.whenClauses =
ImmutableList.copyOf(Objects.requireNonNull(whenClauses));
- defaultValue = Optional.empty();
+ this.defaultValue = Optional.empty();
this.dataTypesForCoercion = computeDataTypesForCoercion();
}
- /** CaseWhen */
+ /** CaseWhen with default value (searched case) */
public CaseWhen(List<WhenClause> whenClauses, Expression defaultValue) {
super(ImmutableList.<Expression>builderWithExpectedSize(whenClauses.size() + 1)
.addAll(whenClauses)
.add(defaultValue)
.build());
+ this.value = Optional.empty();
this.whenClauses =
ImmutableList.copyOf(Objects.requireNonNull(whenClauses));
this.defaultValue = Optional.of(Objects.requireNonNull(defaultValue));
this.dataTypesForCoercion = computeDataTypesForCoercion();
}
+ /** Simple case: CASE value WHEN ... */
+ public CaseWhen(Expression value, List<WhenClause> whenClauses) {
+
super(ImmutableList.<Expression>builderWithExpectedSize(whenClauses.size() + 1)
+ .add(Objects.requireNonNull(value))
+ .addAll(whenClauses)
+ .build());
+ this.value = Optional.of(value);
+ this.whenClauses =
ImmutableList.copyOf(Objects.requireNonNull(whenClauses));
+ this.defaultValue = Optional.empty();
+ this.dataTypesForCoercion = computeDataTypesForCoercion();
+ }
+
+ /** Simple case with default value: CASE value WHEN ... ELSE ... END */
+ public CaseWhen(Expression value, List<WhenClause> whenClauses, Expression
defaultValue) {
+
super(ImmutableList.<Expression>builderWithExpectedSize(whenClauses.size() + 2)
+ .add(Objects.requireNonNull(value))
+ .addAll(whenClauses)
+ .add(defaultValue)
+ .build());
+ this.value = Optional.of(value);
+ this.whenClauses =
ImmutableList.copyOf(Objects.requireNonNull(whenClauses));
+ this.defaultValue = Optional.of(Objects.requireNonNull(defaultValue));
+ this.dataTypesForCoercion = computeDataTypesForCoercion();
+ }
+
+ public Optional<Expression> getValue() {
+ return value;
+ }
+
public List<WhenClause> getWhenClauses() {
return whenClauses;
}
@@ -83,7 +118,7 @@ public class CaseWhen extends Expression implements
NeedSessionVarGuard {
@Override
public DataType getDataType() {
- return child(0).getDataType();
+ return whenClauses.get(0).getDataType();
}
@Override
@@ -99,13 +134,11 @@ public class CaseWhen extends Expression implements
NeedSessionVarGuard {
@Override
public String toString() {
StringBuilder output = new StringBuilder("CASE");
- for (Expression child : children()) {
- if (child instanceof WhenClause) {
- output.append(child.toString());
- } else {
- output.append(" ELSE ").append(child.toString());
- }
+ value.ifPresent(v -> output.append(" ").append(v.toString()));
+ for (WhenClause whenClause : whenClauses) {
+ output.append(whenClause.toString());
}
+ defaultValue.ifPresent(dv -> output.append(" ELSE
").append(dv.toString()));
output.append(" END");
return output.toString();
}
@@ -113,13 +146,11 @@ public class CaseWhen extends Expression implements
NeedSessionVarGuard {
@Override
public String toDigest() {
StringBuilder sb = new StringBuilder("CASE");
- for (Expression child : children()) {
- if (child instanceof WhenClause) {
- sb.append(child.toDigest());
- } else {
- sb.append(" ELSE ").append(child.toDigest());
- }
+ value.ifPresent(v -> sb.append(" ").append(v.toDigest()));
+ for (WhenClause whenClause : whenClauses) {
+ sb.append(whenClause.toDigest());
}
+ defaultValue.ifPresent(dv -> sb.append(" ELSE
").append(dv.toDigest()));
sb.append(" END");
return sb.toString();
}
@@ -127,13 +158,11 @@ public class CaseWhen extends Expression implements
NeedSessionVarGuard {
@Override
public String computeToSql() throws UnboundException {
StringBuilder output = new StringBuilder("CASE");
- for (Expression child : children()) {
- if (child instanceof WhenClause) {
- output.append(child.toSql());
- } else {
- output.append(" ELSE ").append(child.toSql());
- }
+ value.ifPresent(v -> output.append(" ").append(v.toSql()));
+ for (WhenClause whenClause : whenClauses) {
+ output.append(whenClause.toSql());
}
+ defaultValue.ifPresent(dv -> output.append(" ELSE
").append(dv.toSql()));
output.append(" END");
return output.toString();
}
@@ -141,21 +170,36 @@ public class CaseWhen extends Expression implements
NeedSessionVarGuard {
@Override
public CaseWhen withChildren(List<Expression> children) {
Preconditions.checkArgument(!children.isEmpty(), "case when should has
at least 1 child");
+ int i = 0;
+ Expression value = null;
+ // First non-WhenClause child before any WhenClause is the simple case
value.
+ // Note: value is always consumed during analysis phase; post-analysis,
+ // the first child is always a WhenClause, so this branch is only
taken pre-analysis.
+ if (i < children.size() && !(children.get(i) instanceof WhenClause)) {
+ value = children.get(i);
+ i++;
+ }
List<WhenClause> whenClauseList = new ArrayList<>();
+ while (i < children.size() && children.get(i) instanceof WhenClause) {
+ whenClauseList.add((WhenClause) children.get(i));
+ i++;
+ }
+ Preconditions.checkArgument(!whenClauseList.isEmpty(), "case when
should has at least 1 when clause");
Expression defaultValue = null;
- for (int i = 0; i < children.size(); i++) {
- if (children.get(i) instanceof WhenClause) {
- whenClauseList.add((WhenClause) children.get(i));
- } else if (children.size() - 1 == i) {
- defaultValue = children.get(i);
- } else {
- throw new AnalysisException("The children format needs to be
[WhenClause+, DefaultValue?]");
- }
+ if (i < children.size()) {
+ defaultValue = children.get(i);
+ i++;
}
- if (defaultValue == null) {
- return new CaseWhen(whenClauseList);
+ Preconditions.checkArgument(i == children.size(),
+ "The children format needs to be [Value?, WhenClause+,
DefaultValue?]");
+ if (value != null) {
+ return defaultValue != null
+ ? new CaseWhen(value, whenClauseList, defaultValue)
+ : new CaseWhen(value, whenClauseList);
}
- return new CaseWhen(whenClauseList, defaultValue);
+ return defaultValue != null
+ ? new CaseWhen(whenClauseList, defaultValue)
+ : new CaseWhen(whenClauseList);
}
private Supplier<List<DataType>> computeDataTypesForCoercion() {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/CaseWhenTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/CaseWhenTest.java
new file mode 100644
index 00000000000..5f71ddf8345
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/CaseWhenTest.java
@@ -0,0 +1,184 @@
+// 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.trees.expressions;
+
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.utframe.TestWithFeService;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.List;
+
+public class CaseWhenTest extends TestWithFeService {
+
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("test");
+ createTable("create table t1 (\n"
+ + " a int,\n"
+ + " b int\n"
+ + ")\n"
+ + "distributed by hash(a) buckets 4\n"
+ + "properties(\n"
+ + " \"replication_num\"=\"1\"\n"
+ + ")");
+ }
+
+ @Test
+ void testSimpleCaseValueField() {
+ IntegerLiteral value = new IntegerLiteral(1);
+ WhenClause wc1 = new WhenClause(new IntegerLiteral(1), new
IntegerLiteral(10));
+ WhenClause wc2 = new WhenClause(new IntegerLiteral(2), new
IntegerLiteral(20));
+ IntegerLiteral defaultVal = new IntegerLiteral(99);
+
+ // Simple case with value and default
+ CaseWhen withValue = new CaseWhen(value, ImmutableList.of(wc1, wc2),
defaultVal);
+ Assertions.assertTrue(withValue.getValue().isPresent());
+ Assertions.assertEquals(value, withValue.getValue().get());
+ Assertions.assertEquals(2, withValue.getWhenClauses().size());
+ Assertions.assertTrue(withValue.getDefaultValue().isPresent());
+ Assertions.assertEquals(defaultVal, withValue.getDefaultValue().get());
+
+ // Children layout: [value, WhenClause1, WhenClause2, defaultValue]
+ List<Expression> children = withValue.children();
+ Assertions.assertEquals(4, children.size());
+ Assertions.assertEquals(value, children.get(0));
+ Assertions.assertInstanceOf(WhenClause.class, children.get(1));
+ Assertions.assertInstanceOf(WhenClause.class, children.get(2));
+ Assertions.assertEquals(defaultVal, children.get(3));
+
+ // Simple case without default
+ CaseWhen withValueNoDefault = new CaseWhen(value,
ImmutableList.of(wc1, wc2));
+ Assertions.assertTrue(withValueNoDefault.getValue().isPresent());
+
Assertions.assertFalse(withValueNoDefault.getDefaultValue().isPresent());
+ Assertions.assertEquals(3, withValueNoDefault.children().size());
+
+ // Searched case (no value)
+ CaseWhen searchedCase = new CaseWhen(ImmutableList.of(wc1, wc2));
+ Assertions.assertFalse(searchedCase.getValue().isPresent());
+ Assertions.assertEquals(2, searchedCase.children().size());
+
+ // Searched case with default
+ CaseWhen searchedWithDefault = new CaseWhen(ImmutableList.of(wc1,
wc2), defaultVal);
+ Assertions.assertFalse(searchedWithDefault.getValue().isPresent());
+
Assertions.assertTrue(searchedWithDefault.getDefaultValue().isPresent());
+ Assertions.assertEquals(3, searchedWithDefault.children().size());
+ }
+
+ @Test
+ void testSimpleCaseWithChildren() {
+ IntegerLiteral value = new IntegerLiteral(1);
+ WhenClause wc1 = new WhenClause(new IntegerLiteral(1), new
IntegerLiteral(10));
+ WhenClause wc2 = new WhenClause(new IntegerLiteral(2), new
IntegerLiteral(20));
+ IntegerLiteral defaultVal = new IntegerLiteral(99);
+
+ // withChildren roundtrip for simple case with default
+ CaseWhen original = new CaseWhen(value, ImmutableList.of(wc1, wc2),
defaultVal);
+ CaseWhen rebuilt = original.withChildren(original.children());
+ Assertions.assertTrue(rebuilt.getValue().isPresent());
+ Assertions.assertEquals(2, rebuilt.getWhenClauses().size());
+ Assertions.assertTrue(rebuilt.getDefaultValue().isPresent());
+ Assertions.assertEquals(original.children().size(),
rebuilt.children().size());
+
+ // withChildren roundtrip for simple case without default
+ CaseWhen noDefault = new CaseWhen(value, ImmutableList.of(wc1, wc2));
+ CaseWhen rebuiltNoDefault =
noDefault.withChildren(noDefault.children());
+ Assertions.assertTrue(rebuiltNoDefault.getValue().isPresent());
+ Assertions.assertFalse(rebuiltNoDefault.getDefaultValue().isPresent());
+
+ // withChildren roundtrip for searched case
+ CaseWhen searched = new CaseWhen(ImmutableList.of(wc1, wc2),
defaultVal);
+ CaseWhen rebuiltSearched = searched.withChildren(searched.children());
+ Assertions.assertFalse(rebuiltSearched.getValue().isPresent());
+ Assertions.assertTrue(rebuiltSearched.getDefaultValue().isPresent());
+ Assertions.assertEquals(2, rebuiltSearched.getWhenClauses().size());
+ }
+
+ @Test
+ void testSimpleCaseToSql() {
+ IntegerLiteral value = new IntegerLiteral(1);
+ WhenClause wc1 = new WhenClause(new IntegerLiteral(1), new
IntegerLiteral(10));
+ WhenClause wc2 = new WhenClause(new IntegerLiteral(2), new
IntegerLiteral(20));
+ IntegerLiteral defaultVal = new IntegerLiteral(99);
+
+ // Simple case: CASE value WHEN cond THEN result ... ELSE default END
+ CaseWhen simpleCaseWhen = new CaseWhen(value, ImmutableList.of(wc1,
wc2), defaultVal);
+ String sql = simpleCaseWhen.toSql();
+ Assertions.assertTrue(sql.startsWith("CASE 1"), "Simple case SQL
should start with 'CASE 1', got: " + sql);
+ Assertions.assertTrue(sql.contains("WHEN"), "SQL should contain WHEN:
" + sql);
+ Assertions.assertTrue(sql.contains("THEN"), "SQL should contain THEN:
" + sql);
+ Assertions.assertTrue(sql.contains("ELSE"), "SQL should contain ELSE:
" + sql);
+ Assertions.assertTrue(sql.endsWith("END"), "SQL should end with END: "
+ sql);
+
+ // Searched case: CASE WHEN cond THEN result ... END
+ CaseWhen searchedCaseWhen = new CaseWhen(ImmutableList.of(wc1, wc2));
+ String searchedSql = searchedCaseWhen.toSql();
+ Assertions.assertTrue(searchedSql.startsWith("CASE WHEN"),
+ "Searched case SQL should start with 'CASE WHEN', got: " +
searchedSql);
+ Assertions.assertFalse(searchedSql.contains("ELSE"),
+ "Searched case without default should not contain ELSE: " +
searchedSql);
+
+ // toString should behave similarly
+ String str = simpleCaseWhen.toString();
+ Assertions.assertTrue(str.startsWith("CASE 1"), "toString should start
with 'CASE 1', got: " + str);
+ Assertions.assertTrue(str.endsWith("END"), "toString should end with
END: " + str);
+ }
+
+ @Test
+ void testParseSimpleCase() {
+ // Parse a simple case expression and verify it produces a CaseWhen
with value
+ String sql = "select case a when 1 then 2 else 3 end from t1";
+ // Nereids planner is enabled by default; verify the SQL can be parsed
and analyzed
+ Assertions.assertDoesNotThrow(() -> getSQLPlanner(sql));
+ }
+
+ @Test
+ void testSimpleCaseWithSubqueryPlanning() {
+ // This is the key bug test: simple case with a subquery as value.
+ // Previously, the parser would inline the value into each WhenClause
+ // as EqualTo(subquery, literal), causing the same subquery RelationId
+ // to appear multiple times, leading to "groupExpression already exists
+ // in memo" errors during planning.
+ String sql = "select case (select sum(b) from t1) "
+ + "when 1 then 2 "
+ + "when 3 then 4 "
+ + "else 5 end from t1";
+ // This should not throw - verifying it can be planned
+ Assertions.assertDoesNotThrow(() -> getSQLPlanner(sql),
+ "Simple case with subquery value should plan successfully
without duplicate RelationId error");
+ }
+
+ @Test
+ void testSearchedCaseStillWorks() {
+ // Regression check: searched case (no value) should still work
correctly
+ String sql = "select case when a = 1 then 2 when a = 3 then 4 else 5
end from t1";
+ Assertions.assertDoesNotThrow(() -> getSQLPlanner(sql),
+ "Searched case should still work correctly");
+ }
+
+ @Test
+ void testSimpleCaseWithColumnRef() {
+ // Non-subquery simple case should also work
+ String sql = "select case a when 1 then 'one' when 2 then 'two' else
'other' end from t1";
+ Assertions.assertDoesNotThrow(() -> getSQLPlanner(sql),
+ "Simple case with column reference should plan successfully");
+ }
+}
diff --git
a/regression-test/data/nereids_p0/subquery/test_subquery_in_simple_case.out
b/regression-test/data/nereids_p0/subquery/test_subquery_in_simple_case.out
new file mode 100644
index 00000000000..8329dfe332c
--- /dev/null
+++ b/regression-test/data/nereids_p0/subquery/test_subquery_in_simple_case.out
@@ -0,0 +1,36 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !case1 --
+5
+5
+5
+
+-- !case2 --
+match
+match
+match
+
+-- !case3 --
+one
+other
+three
+
+-- !case4 --
+5
+5
+5
+
+-- !case5 --
+yes
+yes
+yes
+
+-- !case6 --
+nested_match
+nested_match
+nested_match
+
+-- !case7 --
+big
+big
+big
+
diff --git
a/regression-test/suites/nereids_p0/subquery/test_subquery_in_simple_case.groovy
b/regression-test/suites/nereids_p0/subquery/test_subquery_in_simple_case.groovy
new file mode 100644
index 00000000000..a951bd80f99
--- /dev/null
+++
b/regression-test/suites/nereids_p0/subquery/test_subquery_in_simple_case.groovy
@@ -0,0 +1,82 @@
+// 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.
+
+// Regression test for duplicate RelationId bug in simple CASE WHEN with
subqueries.
+// The bug caused "groupExpression already exists in memo" error because the
simple
+// case value (a subquery) was duplicated into multiple EqualTo nodes at parse
time.
+
+suite("test_subquery_in_simple_case") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql "DROP TABLE IF EXISTS test_simple_case_subquery"
+ sql """
+ CREATE TABLE test_simple_case_subquery (
+ big_key int,
+ int_1 int
+ )
+ DISTRIBUTED BY HASH(big_key) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ )
+ """
+ sql "INSERT INTO test_simple_case_subquery VALUES (1, 1), (2, 3), (3, 5)"
+
+ // Case 1: Exact reproduce case from bug report.
+ // sum(int_1)=9, CASE 9 WHEN 1 THEN 2 WHEN 3 THEN 4 ELSE 5 END → 5 for all
3 rows.
+ order_qt_case1 """
+ select case (select sum(int_1) from test_simple_case_subquery) when 1
then 2 when 3 then 4 else 5 end a3 from test_simple_case_subquery
+ """
+
+ // Case 2: Simple case with scalar subquery, multiple WHEN clauses, no
ELSE.
+ // sum(int_1)=9, CASE 9 WHEN 9 THEN 'match' WHEN 0 THEN 'zero' END →
'match' for all 3 rows.
+ order_qt_case2 """
+ select case (select sum(int_1) from test_simple_case_subquery) when 9
then 'match' when 0 then 'zero' end from test_simple_case_subquery
+ """
+
+ // Case 3: Simple case with column reference (non-subquery baseline).
+ // Row-by-row: int_1=1→'one', int_1=3→'three', int_1=5→'other'.
+ order_qt_case3 """
+ select case int_1 when 1 then 'one' when 3 then 'three' else 'other'
end from test_simple_case_subquery
+ """
+
+ // Case 4: Simple case with subquery in value AND subquery in result.
+ // sum(int_1)=9, CASE 9 WHEN 9 THEN max(int_1)=5 ELSE 0 END → 5 for all 3
rows.
+ order_qt_case4 """
+ select case (select sum(int_1) from test_simple_case_subquery) when 9
then (select max(int_1) from test_simple_case_subquery) else 0 end from
test_simple_case_subquery
+ """
+
+ // Case 5: Single WHEN clause with subquery value (edge case).
+ // sum(int_1)=9, CASE 9 WHEN 9 THEN 'yes' ELSE 'no' END → 'yes' for all 3
rows.
+ order_qt_case5 """
+ select case (select sum(int_1) from test_simple_case_subquery) when 9
then 'yes' else 'no' end from test_simple_case_subquery
+ """
+
+ // Case 6: Nested case with subquery.
+ // count(*)=3, sum(int_1)=9.
+ // CASE 3 WHEN 3 THEN (CASE 9 WHEN 9 THEN 'nested_match' ELSE
'nested_other' END) ELSE 'outer_other' END
+ // → 'nested_match' for all 3 rows.
+ order_qt_case6 """
+ select case (select count(*) from test_simple_case_subquery) when 3
then case (select sum(int_1) from test_simple_case_subquery) when 9 then
'nested_match' else 'nested_other' end else 'outer_other' end from
test_simple_case_subquery
+ """
+
+ // Case 7: Searched case with subquery (should still work, not affected by
change).
+ // sum(int_1)=9, CASE WHEN 9 > 5 THEN 'big' ELSE 'small' END → 'big' for
all 3 rows.
+ order_qt_case7 """
+ select case when (select sum(int_1) from test_simple_case_subquery) >
5 then 'big' else 'small' end from test_simple_case_subquery
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
