This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 582de4eb548 branch-3.1: [fix](nereids)prune assert_true slots in
subquery unnesting #50256 (#51995)
582de4eb548 is described below
commit 582de4eb5489bd410ede07e523592e2548fa8d2a
Author: starocean999 <[email protected]>
AuthorDate: Fri Jun 20 13:28:01 2025 +0800
branch-3.1: [fix](nereids)prune assert_true slots in subquery unnesting
#50256 (#51995)
pick from master #50256
---
.../doris/catalog/BuiltinScalarFunctions.java | 2 -
.../doris/nereids/jobs/executor/Analyzer.java | 12 +++-
.../doris/nereids/jobs/executor/Rewriter.java | 4 ++
.../nereids/rules/analysis/SubExprAnalyzer.java | 9 ++-
.../nereids/rules/analysis/SubqueryToApply.java | 47 ++++++++----
.../nereids/trees/copier/ExpressionDeepCopier.java | 2 +-
.../nereids/trees/expressions/ScalarSubquery.java | 25 +++++--
.../rules/analysis/AnalyzeWhereSubqueryTest.java | 1 +
.../rules/analysis/FillUpMissingSlotsTest.java | 7 ++
.../subquery/correlated_scalar_subquery.out | Bin 693 -> 736 bytes
.../conditional_functions/test_assert_true.out | Bin 204 -> 0 bytes
.../subquery/correlated_scalar_subquery.groovy | 18 ++++-
.../conditional_functions/test_assert_true.groovy | 79 ---------------------
13 files changed, 100 insertions(+), 106 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index 1c1a820952f..1e40290aeb4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -73,7 +73,6 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayZip;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArraysOverlap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Ascii;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Asin;
-import org.apache.doris.nereids.trees.expressions.functions.scalar.AssertTrue;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Atan;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Atan2;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.AutoPartitionName;
@@ -549,7 +548,6 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(ArraysOverlap.class, "arrays_overlap"),
scalar(Ascii.class, "ascii"),
scalar(Asin.class, "asin"),
- scalar(AssertTrue.class, "assert_true"),
scalar(Atan.class, "atan"),
scalar(Atan2.class, "atan2"),
scalar(AutoPartitionName.class, "auto_partition_name"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java
index 06e55bb2501..41684e8d63d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java
@@ -44,7 +44,6 @@ import
org.apache.doris.nereids.rules.analysis.ProjectWithDistinctToAggregate;
import org.apache.doris.nereids.rules.analysis.ReplaceExpressionByChildOutput;
import org.apache.doris.nereids.rules.analysis.SubqueryToApply;
import org.apache.doris.nereids.rules.analysis.VariableToLiteral;
-import org.apache.doris.nereids.rules.rewrite.MergeProjects;
import org.apache.doris.nereids.rules.rewrite.SemiJoinCommute;
import org.apache.doris.nereids.rules.rewrite.SimplifyAggGroupBy;
import org.apache.doris.nereids.trees.plans.logical.LogicalCTEAnchor;
@@ -148,8 +147,15 @@ public class Analyzer extends AbstractBatchJobExecutor {
),
topDown(new LeadingJoin()),
bottomUp(new NormalizeGenerate()),
- bottomUp(new SubqueryToApply()),
- topDown(new MergeProjects())
+ bottomUp(new SubqueryToApply())
+ /*
+ * Notice, MergeProjects rule should NOT be placed after
SubqueryToApply in analyze phase.
+ * because in SubqueryToApply, we may add assert_true function with
subquery output slot in projects list.
+ * on the other hand, the assert_true function should be not be in
final output.
+ * in order to keep the plan unchanged, we add a new project node to
prune the extra assert_true slot.
+ * but MergeProjects rule will merge the two projects and keep
assert_true anyway.
+ * so we move MergeProjects from analyze to rewrite phase.
+ */
);
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index 619ca37e77d..a7f40c25586 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -164,6 +164,10 @@ public class Rewriter extends AbstractBatchJobExecutor {
ImmutableSet.of(LogicalCTEAnchor.class),
() -> jobs(
topic("Plan Normalization",
+ // move MergeProjects rule from analyze phase
+ // because SubqueryToApply and BindSink rule may
create extra project node
+ // we need merge them at the beginning of rewrite
phase to let later rules happy
+ topDown(new MergeProjects()),
topDown(
new EliminateOrderByConstant(),
new EliminateSortUnderSubqueryOrView(),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
index 9a70ce24afb..e0d809c9f1a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
@@ -124,6 +124,11 @@ class SubExprAnalyzer<T> extends
DefaultExpressionRewriter<T> {
boolean isCorrelated = analyzedResult.isCorrelated();
LogicalPlan analyzedSubqueryPlan = analyzedResult.logicalPlan;
checkOutputColumn(analyzedSubqueryPlan);
+ // use limitOneIsEliminated to indicate if subquery has limit 1 clause
+ // because limit 1 clause will ensure subquery output at most 1 row
+ // we eliminate limit 1 clause and pass this info to later
SubqueryToApply rule
+ // so when creating LogicalApply node, we don't need to add AssertTrue
function
+ boolean limitOneIsEliminated = false;
if (isCorrelated) {
if (analyzedSubqueryPlan instanceof LogicalLimit) {
LogicalLimit limit = (LogicalLimit) analyzedSubqueryPlan;
@@ -131,6 +136,7 @@ class SubExprAnalyzer<T> extends
DefaultExpressionRewriter<T> {
// skip useless limit node
analyzedResult = new AnalyzedResult((LogicalPlan)
analyzedSubqueryPlan.child(0),
analyzedResult.correlatedSlots);
+ limitOneIsEliminated = true;
} else {
throw new AnalysisException("limit is not supported in
correlated subquery "
+ analyzedResult.getLogicalPlan());
@@ -170,7 +176,8 @@ class SubExprAnalyzer<T> extends
DefaultExpressionRewriter<T> {
}
}
- return new ScalarSubquery(analyzedResult.getLogicalPlan(),
analyzedResult.getCorrelatedSlots());
+ return new ScalarSubquery(analyzedResult.getLogicalPlan(),
analyzedResult.getCorrelatedSlots(),
+ limitOneIsEliminated);
}
private void checkOutputColumn(LogicalPlan plan) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
index 27e6f446682..d12282b9769 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java
@@ -68,6 +68,7 @@ import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
+import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
@@ -407,7 +408,11 @@ public class SubqueryToApply implements
AnalysisRuleFactory {
Optional<MarkJoinSlotReference> markJoinSlot =
subqueryToMarkJoinSlot.get(subquery);
boolean needAddScalarSubqueryOutputToProjects =
isConjunctContainsScalarSubqueryOutput(
subquery, conjunct, isProject, singleSubquery);
- boolean needRuntimeAssertCount = false;
+ // for scalar subquery, we need ensure it output at most 1 row
+ // by doing that, we add an aggregate function any_value() to the
project list
+ // we use needRuntimeAnyValue to indicate if any_value() is needed
+ // if needRuntimeAnyValue is true, we will add it to the project list
+ boolean needRuntimeAnyValue = false;
NamedExpression oldSubqueryOutput =
subquery.getQueryPlan().getOutput().get(0);
Slot countSlot = null;
Slot anyValueSlot = null;
@@ -457,11 +462,17 @@ public class SubqueryToApply implements
AnalysisRuleFactory {
// select (select t2.c1 from t2 where t2.c2 = t1.c2) from t1;
// the original output of the correlate subquery is t2.c1,
after adding a scalar agg, it will be
// select (select count(*), any_value(t2.c1) from t2 where
t2.c2 = t1.c2) from t1;
- Alias countAlias = new Alias(new Count());
Alias anyValueAlias = new Alias(new
AnyValue(oldSubqueryOutput));
- LogicalAggregate<Plan> aggregate = new
LogicalAggregate<>(ImmutableList.of(),
- ImmutableList.of(countAlias, anyValueAlias),
subquery.getQueryPlan());
- countSlot = countAlias.toSlot();
+ LogicalAggregate<Plan> aggregate;
+ if (((ScalarSubquery) subquery).limitOneIsEliminated()) {
+ aggregate = new LogicalAggregate<>(ImmutableList.of(),
+ ImmutableList.of(anyValueAlias),
subquery.getQueryPlan());
+ } else {
+ Alias countAlias = new Alias(new Count());
+ countSlot = countAlias.toSlot();
+ aggregate = new LogicalAggregate<>(ImmutableList.of(),
+ ImmutableList.of(countAlias, anyValueAlias),
subquery.getQueryPlan());
+ }
anyValueSlot = anyValueAlias.toSlot();
subquery = subquery.withSubquery(aggregate);
if (conjunct.isPresent()) {
@@ -469,7 +480,7 @@ public class SubqueryToApply implements AnalysisRuleFactory
{
replaceMap.put(oldSubqueryOutput, anyValueSlot);
newConjunct =
Optional.of(ExpressionUtils.replace(conjunct.get(), replaceMap));
}
- needRuntimeAssertCount = true;
+ needRuntimeAnyValue = true;
}
}
LogicalApply.SubQueryType subQueryType;
@@ -500,21 +511,33 @@ public class SubqueryToApply implements
AnalysisRuleFactory {
projects.addAll(childPlan.getOutput());
// markJoinSlotReference
markJoinSlot.map(projects::add);
+ LogicalProject logicalProject;
if (needAddScalarSubqueryOutputToProjects) {
- if (needRuntimeAssertCount) {
+ if (needRuntimeAnyValue) {
// if we create a new subquery in previous step, we need add
the any_value() and assert_true()
// into the project list. So BE will use assert_true to check
if the subquery return only 1 row
projects.add(anyValueSlot);
- projects.add(new Alias(new AssertTrue(
- ExpressionUtils.or(new IsNull(countSlot),
- new LessThanEqual(countSlot, new
IntegerLiteral(1))),
- new VarcharLiteral("correlate scalar subquery must
return only 1 row"))));
+ if (countSlot != null) {
+ List<NamedExpression> upperProjects = new ArrayList<>();
+ upperProjects.addAll(projects.build());
+ projects.add(new Alias(new AssertTrue(
+ ExpressionUtils.or(new IsNull(countSlot),
+ new LessThanEqual(countSlot, new
IntegerLiteral(1))),
+ new VarcharLiteral("correlate scalar subquery must
return only 1 row"))));
+ logicalProject = new LogicalProject(projects.build(),
newApply);
+ logicalProject = new LogicalProject(upperProjects,
logicalProject);
+ } else {
+ logicalProject = new LogicalProject(projects.build(),
newApply);
+ }
} else {
projects.add(oldSubqueryOutput);
+ logicalProject = new LogicalProject(projects.build(),
newApply);
}
+ } else {
+ logicalProject = new LogicalProject(projects.build(), newApply);
}
- return Pair.of(new LogicalProject(projects.build(), newApply),
newConjunct);
+ return Pair.of(logicalProject, newConjunct);
}
private boolean isConjunctContainsScalarSubqueryOutput(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/ExpressionDeepCopier.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/ExpressionDeepCopier.java
index ad7c44f369b..60a5603f9a2 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/ExpressionDeepCopier.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/copier/ExpressionDeepCopier.java
@@ -162,6 +162,6 @@ public class ExpressionDeepCopier extends
DefaultExpressionRewriter<DeepCopierCo
.collect(Collectors.toList());
Optional<Expression> typeCoercionExpr = scalar.getTypeCoercionExpr()
.map(c -> c.accept(this, context));
- return new ScalarSubquery(logicalPlan, correlateSlots,
typeCoercionExpr);
+ return new ScalarSubquery(logicalPlan, correlateSlots,
typeCoercionExpr, scalar.limitOneIsEliminated());
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
index 25a7052a4ac..a1bfbf49029 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ScalarSubquery.java
@@ -18,6 +18,7 @@
package org.apache.doris.nereids.trees.expressions;
import org.apache.doris.nereids.exceptions.UnboundException;
+import org.apache.doris.nereids.trees.expressions.shape.LeafExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
@@ -38,29 +39,39 @@ import java.util.Optional;
/**
* A subquery that will return only one row and one column.
*/
-public class ScalarSubquery extends SubqueryExpr {
+public class ScalarSubquery extends SubqueryExpr implements LeafExpression {
+ // indicate if the subquery's root plan node is LogicalAggregate
private final boolean hasTopLevelScalarAgg;
+ // indicate if the subquery has limit 1 clause but it's been eliminated in
previous process step
+ private final boolean limitOneIsEliminated;
+
public ScalarSubquery(LogicalPlan subquery) {
- this(subquery, ImmutableList.of());
+ this(subquery, ImmutableList.of(), false);
}
- public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots) {
- this(subquery, correlateSlots, Optional.empty());
+ public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots,
boolean limitOneIsEliminated) {
+ this(subquery, correlateSlots, Optional.empty(), limitOneIsEliminated);
}
- public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots,
Optional<Expression> typeCoercionExpr) {
+ public ScalarSubquery(LogicalPlan subquery, List<Slot> correlateSlots,
Optional<Expression> typeCoercionExpr,
+ boolean limitOneIsEliminated) {
super(Objects.requireNonNull(subquery, "subquery can not be null"),
Objects.requireNonNull(correlateSlots, "correlateSlots can not
be null"),
typeCoercionExpr);
hasTopLevelScalarAgg = findTopLevelScalarAgg(subquery,
ImmutableSet.copyOf(correlateSlots)) != null;
+ this.limitOneIsEliminated = limitOneIsEliminated;
}
public boolean hasTopLevelScalarAgg() {
return hasTopLevelScalarAgg;
}
+ public boolean limitOneIsEliminated() {
+ return limitOneIsEliminated;
+ }
+
/**
* getTopLevelScalarAggFunction
*/
@@ -102,12 +113,12 @@ public class ScalarSubquery extends SubqueryExpr {
return new ScalarSubquery(queryPlan, correlateSlots,
dataType == queryPlan.getOutput().get(0).getDataType()
? Optional.of(queryPlan.getOutput().get(0))
- : Optional.of(new Cast(queryPlan.getOutput().get(0),
dataType)));
+ : Optional.of(new Cast(queryPlan.getOutput().get(0),
dataType)), limitOneIsEliminated);
}
@Override
public ScalarSubquery withSubquery(LogicalPlan subquery) {
- return new ScalarSubquery(subquery, correlateSlots, typeCoercionExpr);
+ return new ScalarSubquery(subquery, correlateSlots, typeCoercionExpr,
limitOneIsEliminated);
}
/**
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeWhereSubqueryTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeWhereSubqueryTest.java
index 258f9ace453..e551a1e0bf6 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeWhereSubqueryTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeWhereSubqueryTest.java
@@ -379,6 +379,7 @@ public class AnalyzeWhereSubqueryTest extends
TestWithFeService implements MemoP
// select * from t6 where t6.k1 < (select max(aa) from (select v1 as
aa from t7 where t6.k2=t7.v2) t2 )
PlanChecker.from(connectContext)
.analyze(sql10)
+ .applyTopDown(new MergeProjects())
.matchesFromRoot(
logicalResultSink(
logicalProject(
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlotsTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlotsTest.java
index cb01fc17e30..62a9fada742 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlotsTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlotsTest.java
@@ -22,6 +22,7 @@ import
org.apache.doris.nereids.datasets.tpch.AnalyzeCheckTestBase;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.properties.OrderKey;
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
+import org.apache.doris.nereids.rules.rewrite.MergeProjects;
import org.apache.doris.nereids.trees.expressions.Add;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.Cast;
@@ -128,6 +129,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
);
Alias sumA2 = new Alias(new ExprId(3), new Sum(a2), "sum(a2)");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.applyBottomUp(new
ExpressionRewrite(ExpressionAnalyzer.FUNCTION_ANALYZER_RULE))
.matches(
logicalProject(
@@ -152,6 +154,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
);
Alias sumA2 = new Alias(new ExprId(3), new Sum(a2), "sum(a2)");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.matches(
logicalProject(
logicalFilter(
@@ -220,6 +223,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
);
Alias minPK = new Alias(new ExprId(4), new Min(pk), "min(pk)");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.matches(
logicalProject(
logicalFilter(
@@ -244,6 +248,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
Alias sumA1A23 = new Alias(new ExprId(4), new Sum(new Add(new Add(a1,
a2), new TinyIntLiteral((byte) 3))),
"sum(((a1 + a2) + 3))");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.matches(
logicalProject(
logicalFilter(
@@ -256,6 +261,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
sql = "SELECT a1 FROM t1 GROUP BY a1 HAVING count(*) > 0";
Alias countStar = new Alias(new ExprId(3), new Count(), "count(*)");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.matches(
logicalProject(
logicalFilter(
@@ -284,6 +290,7 @@ public class FillUpMissingSlotsTest extends
AnalyzeCheckTestBase implements Memo
Alias sumA2 = new Alias(new ExprId(6), new Sum(a2), "sum(a2)");
Alias sumB1 = new Alias(new ExprId(7), new Sum(b1), "sum(b1)");
PlanChecker.from(connectContext).analyze(sql)
+ .applyTopDown(new MergeProjects())
.matches(
logicalProject(
logicalFilter(
diff --git
a/regression-test/data/nereids_p0/subquery/correlated_scalar_subquery.out
b/regression-test/data/nereids_p0/subquery/correlated_scalar_subquery.out
index 9414a5c9f61..6811af2c187 100644
Binary files
a/regression-test/data/nereids_p0/subquery/correlated_scalar_subquery.out and
b/regression-test/data/nereids_p0/subquery/correlated_scalar_subquery.out differ
diff --git
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_assert_true.out
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_assert_true.out
deleted file mode 100644
index 6066a2fb852..00000000000
Binary files
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_assert_true.out
and /dev/null differ
diff --git
a/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
b/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
index fb01fe62e1a..d77ac769122 100644
---
a/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
+++
b/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
@@ -88,13 +88,29 @@ suite("correlated_scalar_subquery") {
qt_select_project1 """select c1, sum((select c1 from correlated_scalar_t2
where correlated_scalar_t1.c1 = correlated_scalar_t2.c1 and
correlated_scalar_t2.c2 > 7)) from correlated_scalar_t1 group by c1 order by
c1;"""
qt_select_project2 """select c1, sum((select any_value(c1) from
correlated_scalar_t2 where correlated_scalar_t1.c1 = correlated_scalar_t2.c1
and correlated_scalar_t2.c2 > 7)) from correlated_scalar_t1 group by c1 order
by c1;"""
-
+ qt_select_project3 """WITH base AS
+ (SELECT c1
+ FROM correlated_scalar_t1
+ WHERE c1 IN
+ (SELECT DISTINCT c2
+ FROM correlated_scalar_t2 ) )
+ SELECT
+ (SELECT c1 + (0)
+ FROM correlated_scalar_t1 t1
+ WHERE t1.c1 = base.c1 and t1.c2 > 4) AS
final_value
+ FROM base order by 1;
+ """
qt_select_join1 """select correlated_scalar_t1.* from correlated_scalar_t1
join correlated_scalar_t2 on correlated_scalar_t1.c1 = correlated_scalar_t2.c2
and correlated_scalar_t1.c2 > (select c1 from correlated_scalar_t2 where
correlated_scalar_t1.c1 = correlated_scalar_t2.c1 and correlated_scalar_t2.c2 >
7);"""
qt_select_join2 """select correlated_scalar_t1.* from correlated_scalar_t1
join correlated_scalar_t2 on correlated_scalar_t1.c1 = correlated_scalar_t2.c2
and correlated_scalar_t1.c2 > (select any_value(c1) from correlated_scalar_t2
where correlated_scalar_t1.c1 = correlated_scalar_t2.c1 and
correlated_scalar_t2.c2 > 7);"""
qt_select_having1 """select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select correlated_scalar_t2.c1 from
correlated_scalar_t2 where correlated_scalar_t2.c2 < 4 having
correlated_scalar_t1.c1 = correlated_scalar_t2.c1);"""
qt_select_having2 """select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select any_value(correlated_scalar_t2.c1) from
correlated_scalar_t2 where correlated_scalar_t2.c2 < 4 having
correlated_scalar_t1.c1 = any_value(correlated_scalar_t2.c1));"""
+ explain {
+ sql("""select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select c1 from correlated_scalar_t2 where
correlated_scalar_t1.c1 = correlated_scalar_t2.c1 limit 1);""")
+ notContains("assert_true");
+ }
+
test {
sql """
select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select c1 from correlated_scalar_t2 where
correlated_scalar_t1.c1 = correlated_scalar_t2.c1);
diff --git
a/regression-test/suites/query_p0/sql_functions/conditional_functions/test_assert_true.groovy
b/regression-test/suites/query_p0/sql_functions/conditional_functions/test_assert_true.groovy
deleted file mode 100644
index f84044ee507..00000000000
---
a/regression-test/suites/query_p0/sql_functions/conditional_functions/test_assert_true.groovy
+++ /dev/null
@@ -1,79 +0,0 @@
-// 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_assert_true") {
- sql " drop table if exists assert_true_not_null "
- sql """
- create table assert_true_not_null(
- k0 boolean not null,
- k1 int
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS auto
- properties("replication_num" = "1");
- """
- sql " insert into assert_true_not_null values (true, 1);"
- qt_sql1 """ select assert_true(k0, cast(123 as varchar)) from
assert_true_not_null; """
- qt_sql2 """ select assert_true(k0, "nn123") from assert_true_not_null; """
-
- sql " insert into assert_true_not_null values (false, 2);"
- test {
- sql """ select assert_true(k0, cast(123 as varchar)) from
assert_true_not_null; """
- exception "123"
- }
- test {
- sql """ select assert_true(k0, "nn123") from assert_true_not_null; """
- exception "nn123"
- }
- test {
- sql """ select assert_true(1, k1) from assert_true_not_null; """
- exception "assert_true only accept constant for 2nd argument"
- }
-
-
-
- sql " drop table if exists assert_true_null "
- sql """
- create table assert_true_null(
- k0 boolean null
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS auto
- properties("replication_num" = "1");
- """
- sql " insert into assert_true_null values (true), (true), (true);"
- qt_sql3 """ select assert_true(k0, cast(123 as varchar)) from
assert_true_null; """
- qt_sql4 """ select assert_true(k0, "nn123") from assert_true_null; """
-
- sql " insert into assert_true_null values (null);"
- test {
- sql """ select assert_true(k0, cast(123 as varchar)) from
assert_true_null; """
- exception "123"
- }
- test {
- sql """ select assert_true(k0, "nn123") from assert_true_null; """
- exception "nn123"
- }
-
- qt_sql """ select assert_true(1, "wrong"); """
- test {
- sql """ select assert_true(0, nullable("wrong")); """
- exception "assert_true only accept constant for 2nd argument"
- }
- test {
- sql """ select assert_true(0, "wrong"); """
- exception "wrong"
- }
-}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
