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 0b7cbdab96d branch-3.1: [feature](Nereids) support turn off
ONLY_FULL_GROUP_BY sql mode #49341 (#53535)
0b7cbdab96d is described below
commit 0b7cbdab96dbfa1ac2df27ebec46b1624c0f4b0c
Author: morrySnow <[email protected]>
AuthorDate: Tue Jul 22 13:59:07 2025 +0800
branch-3.1: [feature](Nereids) support turn off ONLY_FULL_GROUP_BY sql mode
#49341 (#53535)
cherry-picked from #49341
---
.../nereids/rules/analysis/BindExpression.java | 104 ++++++++++--
.../nereids/rules/analysis/CheckAfterBind.java | 4 -
.../nereids/rules/analysis/FillUpMissingSlots.java | 14 +-
.../nereids/rules/analysis/NormalizeAggregate.java | 75 +++++----
.../nereids/rules/analysis/NormalizeRepeat.java | 16 +-
.../nereids/trees/plans/logical/LogicalHaving.java | 10 +-
.../java/org/apache/doris/qe/GlobalVariable.java | 3 +-
.../java/org/apache/doris/qe/SessionVariable.java | 2 +-
.../java/org/apache/doris/qe/SqlModeHelper.java | 8 +
.../main/java/org/apache/doris/qe/VariableMgr.java | 33 ++--
.../org/apache/doris/analysis/SetVariableTest.java | 8 +-
.../java/org/apache/doris/qe/VariableMgrTest.java | 2 +-
.../aggregate/non_standard_aggregate.groovy | 187 +++++++++++++++++++++
13 files changed, 396 insertions(+), 70 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
index b2d7779f902..24772ed5f14 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
@@ -55,6 +55,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.BoundFunction;
import org.apache.doris.nereids.trees.expressions.functions.Function;
import org.apache.doris.nereids.trees.expressions.functions.FunctionBuilder;
import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.AnyValue;
import
org.apache.doris.nereids.trees.expressions.functions.agg.NullableAggregateFunction;
import
org.apache.doris.nereids.trees.expressions.functions.generator.TableGeneratingFunction;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.GroupingScalarFunction;
@@ -96,8 +97,10 @@ import org.apache.doris.nereids.types.StructField;
import org.apache.doris.nereids.types.StructType;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.PlanUtils;
+import org.apache.doris.nereids.util.PlanUtils.CollectNonWindowedAggFuncs;
import org.apache.doris.nereids.util.TypeCoercionUtils;
import org.apache.doris.nereids.util.Utils;
+import org.apache.doris.qe.SqlModeHelper;
import com.google.common.base.Preconditions;
import com.google.common.base.Suppliers;
@@ -106,6 +109,7 @@ import com.google.common.collect.ImmutableList.Builder;
import com.google.common.collect.ImmutableListMultimap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
@@ -116,6 +120,7 @@ import org.jetbrains.annotations.NotNull;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
+import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.function.Consumer;
@@ -367,7 +372,41 @@ public class BindExpression implements AnalysisRuleFactory
{
Supplier<Scope> childChildrenOutput = Suppliers.memoize(() ->
toScope(cascadesContext,
PlanUtils.fastGetChildrenOutputs(childPlan.children()))
);
- return bindHavingByScopes(having, cascadesContext, childOutput,
childChildrenOutput);
+ LogicalHaving<Plan> boundHaving = bindHavingByScopes(having,
having.child(),
+ cascadesContext, childOutput, childChildrenOutput);
+ if (!SqlModeHelper.hasOnlyFullGroupBy() && childPlan instanceof
LogicalProject) {
+ // ATTN: process having(project) that have aggregate function in
having
+ LogicalProject<?> project = (LogicalProject<?>) childPlan;
+ List<AggregateFunction> aggFuncs =
CollectNonWindowedAggFuncs.collect(boundHaving.getConjuncts());
+ if (!aggFuncs.isEmpty()) {
+ Map<Expression, Expression> replaceMap = Maps.newHashMap();
+ for (AggregateFunction aggFunc : aggFuncs) {
+ // ATTN: this is a little trick here. since replace check
replace successful with equal operator
+ // see:
org.apache.doris.nereids.trees.TreeNode.rewriteDownShortCircuit
+ // here, we generate a new aggFunc to replace to avoid
rewrite its child.
+ // because we do not want to replace agg func agg(child)
to agg(any_value(child))
+ replaceMap.put(aggFunc,
aggFunc.withChildren(aggFunc.children()));
+ }
+ Builder<NamedExpression> boundProjectionsBuilder
+ =
ImmutableList.builderWithExpectedSize(project.getProjects().size());
+ for (NamedExpression expr : project.getProjects()) {
+ if (expr instanceof SlotReference) {
+ Alias alias = new Alias(new AnyValue(expr),
expr.getName());
+ boundProjectionsBuilder.add(alias);
+ replaceMap.put(expr, alias);
+ } else {
+ boundProjectionsBuilder.add(expr);
+ }
+ }
+ Plan newChildPlan =
project.withProjects(boundProjectionsBuilder.build());
+ ImmutableSet.Builder<Expression> newConjunctsBuilder =
ImmutableSet.builder();
+ for (Expression conjunct : boundHaving.getConjuncts()) {
+ newConjunctsBuilder.add(ExpressionUtils.replace(conjunct,
replaceMap));
+ }
+ boundHaving =
boundHaving.withConjunctsAndChild(newConjunctsBuilder.build(), newChildPlan);
+ }
+ }
+ return boundHaving;
}
private LogicalHaving<Plan> bindHavingAggregate(
@@ -466,10 +505,8 @@ public class BindExpression implements AnalysisRuleFactory
{
}
private LogicalHaving<Plan> bindHavingByScopes(
- LogicalHaving<? extends Plan> having,
+ LogicalHaving<? extends Plan> having, Plan child,
CascadesContext cascadesContext, Scope defaultScope,
Supplier<Scope> backupScope) {
- Plan child = having.child();
-
SimpleExprAnalyzer analyzer = buildCustomSlotBinderAnalyzer(
having, cascadesContext, defaultScope, false, true,
(self, unboundSlot) -> {
@@ -488,7 +525,7 @@ public class BindExpression implements AnalysisRuleFactory {
}
checkIfOutputAliasNameDuplicatedForGroupBy(boundConjuncts.build(),
child instanceof LogicalProject ? ((LogicalProject<?>)
child).getOutputs() : child.getOutput());
- return new LogicalHaving<>(boundConjuncts.build(), having.child());
+ return new LogicalHaving<>(boundConjuncts.build(), child);
}
private LogicalSort<LogicalSetOperation> bindSortWithSetOperation(
@@ -621,19 +658,20 @@ public class BindExpression implements
AnalysisRuleFactory {
Supplier<Set<NamedExpression>> boundExcepts = Suppliers.memoize(
() -> analyzer.analyzeToSet(project.getExcepts()));
- Builder<NamedExpression> boundProjections =
ImmutableList.builderWithExpectedSize(project.getProjects().size());
+ Builder<NamedExpression> boundProjectionsBuilder
+ =
ImmutableList.builderWithExpectedSize(project.getProjects().size());
StatementContext statementContext = ctx.statementContext;
for (Expression expression : project.getProjects()) {
Expression expr = analyzer.analyze(expression);
if (!(expr instanceof BoundStar)) {
- boundProjections.add((NamedExpression) expr);
+ boundProjectionsBuilder.add((NamedExpression) expr);
} else {
BoundStar boundStar = (BoundStar) expr;
List<Slot> slots = boundStar.getSlots();
if (!excepts.isEmpty()) {
slots = Utils.filterImmutableList(slots, slot ->
!boundExcepts.get().contains(slot));
}
- boundProjections.addAll(slots);
+ boundProjectionsBuilder.addAll(slots);
// for create view stmt expand star
List<Slot> slotsForLambda = slots;
@@ -643,7 +681,7 @@ public class BindExpression implements AnalysisRuleFactory {
});
}
}
- List<NamedExpression> projects =
adjustProjectionAggNullable(boundProjections.build());
+ List<NamedExpression> projects =
adjustProjectionAggNullable(boundProjectionsBuilder.build());
return project.withProjects(projects);
}
@@ -653,6 +691,7 @@ public class BindExpression implements AnalysisRuleFactory {
if (!hasAggregation) {
return expressions;
}
+ boolean hasOnlyFullGroupBy = SqlModeHelper.hasOnlyFullGroupBy();
Builder<NamedExpression> newExpressionsBuilder =
ImmutableList.builderWithExpectedSize(expressions.size());
for (NamedExpression expr : expressions) {
expr = (NamedExpression) expr.rewriteDownShortCircuit(e -> {
@@ -662,6 +701,9 @@ public class BindExpression implements AnalysisRuleFactory {
}
return e;
});
+ if (!hasOnlyFullGroupBy && expr instanceof SlotReference) {
+ expr = new Alias(expr, expr.getName());
+ }
newExpressionsBuilder.add(expr);
}
return newExpressionsBuilder.build();
@@ -702,7 +744,7 @@ public class BindExpression implements AnalysisRuleFactory {
buildAggOutputScopeWithoutAggFun(boundProjections,
cascadesContext);
List<Expression> boundGroupBy = bindGroupBy(
agg, agg.getGroupByExpressions(), boundProjections,
aggOutputScopeWithoutAggFun, cascadesContext);
- return agg.withGroupByAndOutput(boundGroupBy, boundProjections);
+ return agg.withGroupByAndOutput(boundGroupBy,
processNonStandardAggregate(boundProjections, boundGroupBy));
}
private Plan bindRepeat(MatchingContext<LogicalRepeat<Plan>> ctx) {
@@ -717,10 +759,12 @@ public class BindExpression implements
AnalysisRuleFactory {
Builder<List<Expression>> boundGroupingSetsBuilder =
ImmutableList.builderWithExpectedSize(repeat.getGroupingSets().size());
+ Set<Expression> flatBoundGroupingSet = Sets.newHashSet();
for (List<Expression> groupingSet : repeat.getGroupingSets()) {
List<Expression> boundGroupingSet = bindGroupBy(
repeat, groupingSet, boundRepeatOutput,
aggOutputScopeWithoutAggFun, cascadesContext);
boundGroupingSetsBuilder.add(boundGroupingSet);
+ flatBoundGroupingSet.addAll(boundGroupingSet);
}
List<List<Expression>> boundGroupingSets =
boundGroupingSetsBuilder.build();
List<NamedExpression> nullableOutput =
PlanUtils.adjustNullableForRepeat(boundGroupingSets, boundRepeatOutput);
@@ -730,7 +774,7 @@ public class BindExpression implements AnalysisRuleFactory {
// check all GroupingScalarFunction inputSlots must be from
groupingExprs
Set<Slot> groupingExprs = boundGroupingSets.stream()
- .flatMap(Collection::stream).map(expr -> expr.getInputSlots())
+ .flatMap(Collection::stream).map(Expression::getInputSlots)
.flatMap(Collection::stream).collect(Collectors.toSet());
Set<GroupingScalarFunction> groupingScalarFunctions = ExpressionUtils
.collect(nullableOutput,
GroupingScalarFunction.class::isInstance);
@@ -740,7 +784,43 @@ public class BindExpression implements AnalysisRuleFactory
{
+ " does not exist in GROUP BY clause.");
}
}
- return repeat.withGroupSetsAndOutput(boundGroupingSets,
nullableOutput);
+ return repeat.withGroupSetsAndOutput(boundGroupingSets,
+ processNonStandardAggregate(nullableOutput,
flatBoundGroupingSet));
+ }
+
+ /**
+ * for support non-standard aggregate, such as SELECT c1, c2 FROM t GROUP
BY c1.
+ * we around an extra Alias on SlotReference in output expression list
+ * to avoid ExprId conflict. Because we will do a transform later like:
+ * <p>
+ * Repeat([c1#1, c2#2 as c2#3], [[c1#1], [c3#4]])
+ * ---
+ * Project(c1#1, c2#3)
+ * +-- Aggregate([c1#1, any_value(c2#2) as c2#3], [c1#1])
+ * +-- Project(c1#1, c2#2)
+ *
+ * @param originalProjections original projections in aggregation
+ * @param groupingExprs all expressions for group by key
+ *
+ * @return output with wrapped scalar slots
+ */
+ private List<NamedExpression> processNonStandardAggregate(
+ List<NamedExpression> originalProjections, Collection<Expression>
groupingExprs) {
+ if (SqlModeHelper.hasOnlyFullGroupBy()) {
+ return originalProjections;
+ } else {
+
+ ImmutableList.Builder<NamedExpression> finalProjectionsBuilder =
ImmutableList.builder();
+ for (NamedExpression projection : originalProjections) {
+ // we do a trick here
+ if (projection instanceof SlotReference &&
!groupingExprs.contains(projection)) {
+ finalProjectionsBuilder.add(new Alias(projection,
projection.getName()));
+ } else {
+ finalProjectionsBuilder.add(projection);
+ }
+ }
+ return finalProjectionsBuilder.build();
+ }
}
private List<Expression> bindGroupBy(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CheckAfterBind.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CheckAfterBind.java
index 9658bfef20c..e90e868a510 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CheckAfterBind.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CheckAfterBind.java
@@ -25,7 +25,6 @@ import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.InSubquery;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalHaving;
-import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.ImmutableList;
@@ -61,9 +60,6 @@ public class CheckAfterBind implements AnalysisRuleFactory {
throw new AnalysisException(Type.OnlyMetricTypeErrorMsg);
}
}
- if (ExpressionUtils.hasOnlyMetricType(predicate.getArguments())) {
- throw new AnalysisException(Type.OnlyMetricTypeErrorMsg);
- }
}
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlots.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlots.java
index 24eeaef6dfa..05784b011da 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlots.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/FillUpMissingSlots.java
@@ -29,6 +29,7 @@ import
org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.AnyValue;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.algebra.Aggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
@@ -36,6 +37,7 @@ import
org.apache.doris.nereids.trees.plans.logical.LogicalHaving;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
import org.apache.doris.nereids.util.ExpressionUtils;
+import org.apache.doris.qe.SqlModeHelper;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Lists;
@@ -310,9 +312,17 @@ public class FillUpMissingSlots implements
AnalysisRuleFactory {
// We couldn't find the equivalent expression in output
expressions and group-by expressions,
// so we should check whether the expression is valid.
if (expression instanceof SlotReference) {
- if (checkSlot && (!outerScope.isPresent()
+ if ((!outerScope.isPresent()
||
!outerScope.get().getCorrelatedSlots().contains(expression))) {
- throw new AnalysisException(expression.toSql() + "
should be grouped by.");
+ if (!SqlModeHelper.hasOnlyFullGroupBy()) {
+ // ATTN: we should add any_value to agg's output
here, but not add slot directly.
+ // because normalize agg cannot replace upper
slot with new output.
+ Alias alias = new Alias(new AnyValue(expression));
+ newOutputSlots.add(alias);
+ substitution.put(expression, alias.toSlot());
+ } else if (checkSlot) {
+ throw new AnalysisException(expression.toSql() + "
should be grouped by.");
+ }
}
} else if (expression instanceof AggregateFunction) {
if
(checkWhetherNestedAggregateFunctionsExist((AggregateFunction) expression)) {
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 4a2e226caae..ad13f19d69b 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
@@ -36,6 +36,7 @@ import
org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.expressions.WindowExpression;
import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.AnyValue;
import
org.apache.doris.nereids.trees.expressions.functions.agg.MultiDistinction;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
@@ -47,10 +48,12 @@ import
org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.PlanUtils.CollectNonWindowedAggFuncs;
import org.apache.doris.nereids.util.Utils;
+import org.apache.doris.qe.SqlModeHelper;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableList.Builder;
import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import java.util.ArrayList;
@@ -218,14 +221,6 @@ public class NormalizeAggregate implements
RewriteRuleFactory, NormalizeToSlot {
Set<NamedExpression> bottomProjects = Sets.union(pushedGroupByExprs,
Sets.union(pushedTrivialAggChildren,
pushedTrivialAggInputSlots));
- // create bottom project
- Plan bottomPlan;
- if (!bottomProjects.isEmpty()) {
- bottomPlan = new
LogicalProject<>(ImmutableList.copyOf(bottomProjects), aggregate.child());
- } else {
- bottomPlan = aggregate.child();
- }
-
// use group by context to normalize agg functions to process
// sql like: select sum(a + 1) from t group by a + 1
//
@@ -275,8 +270,6 @@ public class NormalizeAggregate implements
RewriteRuleFactory, NormalizeToSlot {
newAggOutputBuilder.add((NamedExpression) rewrittenExpr);
}
ImmutableList<NamedExpression> normalizedAggOutput =
newAggOutputBuilder.build();
- LogicalAggregate<?> newAggregate =
- aggregate.withNormalized(normalizedGroupExprs,
normalizedAggOutput, bottomPlan);
// create upper projects by normalize all output exprs in old
LogicalAggregate
// In aggregateOutput, the expressions inside the agg function can be
rewritten
@@ -286,36 +279,58 @@ public class NormalizeAggregate implements
RewriteRuleFactory, NormalizeToSlot {
List<NamedExpression> upperProjects = normalizeOutput(aggregateOutput,
groupByExprContext, argsOfAggFuncNeedPushDownContext,
normalizedAggFuncsToSlotContext);
- ExpressionRewriteContext rewriteContext = new
ExpressionRewriteContext(ctx);
- LogicalProject<Plan> project =
eliminateGroupByConstant(groupByExprContext, rewriteContext,
- normalizedGroupExprs, normalizedAggOutput, bottomProjects,
aggregate, upperProjects, newAggregate);
-
// verify project used slots are all coming from agg's output
- List<Slot> slots = collectAllUsedSlots(upperProjects);
- if (!slots.isEmpty()) {
- Set<ExprId> aggOutputExprIds = new HashSet<>(slots.size());
+ List<Slot> slotsUsedInUpperProject =
collectAllUsedSlots(upperProjects);
+ if (!slotsUsedInUpperProject.isEmpty()) {
+ Set<ExprId> aggOutputExprIds = new
HashSet<>(slotsUsedInUpperProject.size());
for (NamedExpression expression : normalizedAggOutput) {
aggOutputExprIds.add(expression.getExprId());
}
- List<Slot> errorSlots = new ArrayList<>(slots.size());
- for (Slot slot : slots) {
+ Set<Slot> missingSlotsInAggregate = new
HashSet<>(slotsUsedInUpperProject.size());
+ for (Slot slot : slotsUsedInUpperProject) {
if (!aggOutputExprIds.contains(slot.getExprId()) && !(slot
instanceof SlotNotFromChildren)) {
- errorSlots.add(slot);
+ missingSlotsInAggregate.add(slot);
}
}
- if (!errorSlots.isEmpty()) {
- throw new AnalysisException(String.format("%s not in
aggregate's output", errorSlots
-
.stream().map(NamedExpression::getName).collect(Collectors.joining(", "))));
+ 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 {
+ // 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);
+ Map<Expression, Expression> replaceMap = Maps.newHashMap();
+ for (Slot slot : missingSlotsInAggregate) {
+ Alias anyValue = new Alias(new AnyValue(slot),
slot.getName());
+ replaceMap.put(slot, anyValue.toSlot());
+ newAggOutputBuilder.add(anyValue);
+ }
+ upperProjects = upperProjects.stream()
+ .map(e -> (NamedExpression)
ExpressionUtils.replace(e, replaceMap))
+ .collect(ImmutableList.toImmutableList());
+ }
}
}
+ // create normalized plan
+ Plan bottomPlan;
+ if (!bottomProjects.isEmpty()) {
+ bottomPlan = new
LogicalProject<>(ImmutableList.copyOf(bottomProjects), aggregate.child());
+ } else {
+ bottomPlan = aggregate.child();
+ }
+ // NOTICE: we must call newAggOutputBuilder.build() here,
newAggOutputBuilder could be updated if we need
+ // to process non-standard aggregate: SELECT c1, c2 FROM t GROUP BY c1
+ LogicalAggregate<?> newAggregate =
+ aggregate.withNormalized(normalizedGroupExprs,
newAggOutputBuilder.build(), bottomPlan);
+ ExpressionRewriteContext rewriteContext = new
ExpressionRewriteContext(ctx);
+ LogicalProject<Plan> project =
eliminateGroupByConstant(groupByExprContext, rewriteContext,
+ normalizedGroupExprs, normalizedAggOutput, bottomProjects,
aggregate, upperProjects, newAggregate);
+
if (having.isPresent()) {
- Set<Slot> havingUsedSlots =
ExpressionUtils.getInputSlotSet(having.get().getExpressions());
- Set<ExprId> havingUsedExprIds = new
HashSet<>(havingUsedSlots.size());
- for (Slot slot : havingUsedSlots) {
- havingUsedExprIds.add(slot.getExprId());
- }
- Set<ExprId> aggOutputExprIds = newAggregate.getOutputExprIdSet();
- if (aggOutputExprIds.containsAll(havingUsedExprIds)) {
+ Set<Slot> havingUsedSlots = having.get().getInputSlots();
+ Set<Slot> aggOutputExprIds = newAggregate.getOutputSet();
+ if (aggOutputExprIds.containsAll(havingUsedSlots)) {
// when having just use output slots from agg, we push down
having as parent of agg
return project.withChildren(ImmutableList.of(
new LogicalHaving<>(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeRepeat.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeRepeat.java
index 96ea874f259..36bcbea1f12 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeRepeat.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/NormalizeRepeat.java
@@ -40,6 +40,7 @@ import
org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalRepeat;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.PlanUtils.CollectNonWindowedAggFuncs;
+import org.apache.doris.qe.SqlModeHelper;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableList.Builder;
@@ -58,6 +59,7 @@ import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
+import java.util.stream.Collectors;
import javax.annotation.Nullable;
/** NormalizeRepeat
@@ -177,12 +179,12 @@ public class NormalizeRepeat extends
OneAnalysisRuleFactory {
Set<Slot> groupingSetsUsedSlot = ImmutableSet.copyOf(
ExpressionUtils.flatExpressions(normalizedGroupingSets));
- SetView<SlotReference> aggUsedSlotInAggFunction
+ SetView<SlotReference> aggUsedSlotNotInGroupBy
= Sets.difference(aggUsedNonVirtualSlots,
groupingSetsUsedSlot);
List<Slot> normalizedRepeatOutput = ImmutableList.<Slot>builder()
.addAll(groupingSetsUsedSlot)
- .addAll(aggUsedSlotInAggFunction)
+ .addAll(aggUsedSlotNotInGroupBy)
.addAll(allVirtualSlots)
.build();
@@ -192,6 +194,16 @@ public class NormalizeRepeat extends
OneAnalysisRuleFactory {
NormalizeToSlotContext fullContext =
argsContext.mergeContext(groupingExprContext);
Set<NamedExpression> pushedProject =
fullContext.pushDownToNamedExpression(needToSlots);
+ if (!SqlModeHelper.hasOnlyFullGroupBy()) {
+ // in non-standard aggregate, we need to add all missing slot into
pushed project
+ // we should not use aggUsedSlotNotInGroupBy directly to avoid
duplicate materialization
+ // TODO: refactor NormalizeRepeat and NormalizeAggregate for
reading friendly
+ SetView<SlotReference> missingSlots
+ = Sets.difference(aggUsedSlotNotInGroupBy,
+
pushedProject.stream().map(NamedExpression::toSlot).collect(Collectors.toSet()));
+ pushedProject = Sets.union(pushedProject, missingSlots);
+ }
+
Plan normalizedChild = pushDownProject(pushedProject, repeat.child());
LogicalRepeat<Plan> normalizedRepeat = repeat.withNormalizedExpr(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalHaving.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalHaving.java
index 7dd227c6d6c..fedd2b9c62e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalHaving.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalHaving.java
@@ -69,7 +69,7 @@ public class LogicalHaving<CHILD_TYPE extends Plan> extends
LogicalUnary<CHILD_T
}
@Override
- public Plan withChildren(List<Plan> children) {
+ public LogicalHaving<Plan> withChildren(List<Plan> children) {
Preconditions.checkArgument(children.size() == 1);
return new LogicalHaving<>(conjuncts, children.get(0));
}
@@ -91,11 +91,15 @@ public class LogicalHaving<CHILD_TYPE extends Plan> extends
LogicalUnary<CHILD_T
return new LogicalHaving<>(conjuncts, groupExpression,
logicalProperties, children.get(0));
}
- public Plan withConjuncts(Set<Expression> expressions) {
- return new LogicalHaving<Plan>(expressions, Optional.empty(),
+ public LogicalHaving<Plan> withConjuncts(Set<Expression> conjuncts) {
+ return new LogicalHaving<>(conjuncts, Optional.empty(),
Optional.of(getLogicalProperties()), child());
}
+ public LogicalHaving<Plan> withConjunctsAndChild(Set<Expression>
conjuncts, Plan child) {
+ return new LogicalHaving<>(conjuncts, child);
+ }
+
@Override
public List<Slot> computeOutput() {
return child().getOutput();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/GlobalVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/GlobalVariable.java
index b942ae3f657..9523e0a822c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/GlobalVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/GlobalVariable.java
@@ -37,7 +37,8 @@ public final class GlobalVariable {
public static final int VARIABLE_VERSION_100 = 100;
public static final int VARIABLE_VERSION_101 = 101;
public static final int VARIABLE_VERSION_200 = 200;
- public static final int CURRENT_VARIABLE_VERSION = VARIABLE_VERSION_200;
+ public static final int VARIABLE_VERSION_300 = 300;
+ public static final int CURRENT_VARIABLE_VERSION = VARIABLE_VERSION_300;
public static final String VARIABLE_VERSION = "variable_version";
public static final String VERSION_COMMENT = "version_comment";
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index f91ddfd7cf5..92f1ae85f6d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -861,7 +861,7 @@ public class SessionVariable implements Serializable,
Writable {
// Set sqlMode to empty string
@VariableMgr.VarAttr(name = SQL_MODE, needForward = true)
- public long sqlMode = SqlModeHelper.MODE_DEFAULT;
+ public long sqlMode = SqlModeHelper.MODE_ONLY_FULL_GROUP_BY;
@VariableMgr.VarAttr(name = WORKLOAD_VARIABLE, needForward = true)
public String workloadGroup = "";
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SqlModeHelper.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SqlModeHelper.java
index d33800eeb8c..2b66297333b 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SqlModeHelper.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SqlModeHelper.java
@@ -225,4 +225,12 @@ public class SqlModeHelper {
& MODE_PIPES_AS_CONCAT) != 0;
}
+ public static boolean hasOnlyFullGroupBy() {
+ SessionVariable sessionVariable = ConnectContext.get() == null
+ ? VariableMgr.newSessionVariable()
+ : ConnectContext.get().getSessionVariable();
+ return ((sessionVariable.getSqlMode() & MODE_ALLOWED_MASK)
+ & MODE_ONLY_FULL_GROUP_BY) != 0;
+ }
+
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/VariableMgr.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/VariableMgr.java
index f27b8561278..03e09637fad 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/VariableMgr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/VariableMgr.java
@@ -954,53 +954,66 @@ public class VariableMgr {
public static void forceUpdateVariables() {
int currentVariableVersion = GlobalVariable.variableVersion;
+ String updateInfo = currentVariableVersion + "to" +
GlobalVariable.CURRENT_VARIABLE_VERSION;
if (currentVariableVersion == GlobalVariable.VARIABLE_VERSION_0) {
// update from 2.0.15 or below to 2.0.16 or higher
if (VariableMgr.newSessionVariable().nereidsTimeoutSecond == 5) {
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.NEREIDS_TIMEOUT_SECOND, "30");
}
}
if (currentVariableVersion < GlobalVariable.VARIABLE_VERSION_100) {
// update from 2.1.6 or below to 2.1.7 or higher
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_NEREIDS_DML,
String.valueOf(true));
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_NEREIDS_DML_WITH_PIPELINE,
String.valueOf(true));
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_NEREIDS_PLANNER,
String.valueOf(true));
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_FALLBACK_TO_ORIGINAL_PLANNER,
String.valueOf(true));
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_PIPELINE_X_ENGINE,
String.valueOf(true));
}
if (currentVariableVersion < GlobalVariable.VARIABLE_VERSION_101) {
if (StatisticsUtil.getAutoAnalyzeTableWidthThreshold()
< StatisticConstants.AUTO_ANALYZE_TABLE_WIDTH_THRESHOLD) {
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.AUTO_ANALYZE_TABLE_WIDTH_THRESHOLD,
String.valueOf(StatisticConstants.AUTO_ANALYZE_TABLE_WIDTH_THRESHOLD));
}
if (StatisticsUtil.getTableStatsHealthThreshold()
< StatisticConstants.TABLE_STATS_HEALTH_THRESHOLD) {
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.TABLE_STATS_HEALTH_THRESHOLD,
String.valueOf(StatisticConstants.TABLE_STATS_HEALTH_THRESHOLD));
}
}
if (currentVariableVersion < GlobalVariable.VARIABLE_VERSION_200) {
// update from 3.0.2 or below to 3.0.3 or higher
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
SessionVariable.ENABLE_FALLBACK_TO_ORIGINAL_PLANNER,
String.valueOf(false));
}
+ if (currentVariableVersion < GlobalVariable.VARIABLE_VERSION_300) {
+ // update to master
+ long sqlMode = defaultSessionVariable.sqlMode;
+ // remove mode_default flag
+ if ((sqlMode & SqlModeHelper.MODE_DEFAULT) != 0) {
+ sqlMode ^= SqlModeHelper.MODE_DEFAULT;
+ }
+ sqlMode |= SqlModeHelper.MODE_ONLY_FULL_GROUP_BY;
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
+ SessionVariable.SQL_MODE,
+ String.valueOf(sqlMode));
+ }
if (currentVariableVersion < GlobalVariable.CURRENT_VARIABLE_VERSION) {
- VariableMgr.refreshDefaultSessionVariables("update variable
version",
+ VariableMgr.refreshDefaultSessionVariables(updateInfo,
GlobalVariable.VARIABLE_VERSION,
String.valueOf(GlobalVariable.CURRENT_VARIABLE_VERSION));
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/SetVariableTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/SetVariableTest.java
index 1148f64de3f..d23421840d0 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/SetVariableTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/SetVariableTest.java
@@ -45,14 +45,14 @@ public class SetVariableTest {
@Test
public void testSqlMode() throws Exception {
- String setStr = "set sql_mode = concat(@@sql_mode,
'STRICT_TRANS_TABLES');";
+ String setStr = "set sql_mode = concat_ws(',', @@sql_mode,
'STRICT_TRANS_TABLES');";
connectContext.getState().reset();
StmtExecutor stmtExecutor = new StmtExecutor(connectContext, setStr);
stmtExecutor.execute();
- Assert.assertEquals("STRICT_TRANS_TABLES",
-
SqlModeHelper.decode(connectContext.getSessionVariable().getSqlMode()));
+ Assert.assertNotEquals(0,
+ connectContext.getSessionVariable().getSqlMode() &
SqlModeHelper.MODE_STRICT_TRANS_TABLES);
- String selectStr = "explain select /*+
SET_VAR(enable_nereids_planner=false) */ @@sql_mode;";
+ String selectStr = "explain select @@sql_mode;";
connectContext.getState().reset();
stmtExecutor = new StmtExecutor(connectContext, selectStr);
stmtExecutor.execute();
diff --git a/fe/fe-core/src/test/java/org/apache/doris/qe/VariableMgrTest.java
b/fe/fe-core/src/test/java/org/apache/doris/qe/VariableMgrTest.java
index 39f76b86c64..adf6b6d5416 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/qe/VariableMgrTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/qe/VariableMgrTest.java
@@ -82,7 +82,7 @@ public class VariableMgrTest {
} else if (row.get(0).equalsIgnoreCase("query_timeout")) {
Assert.assertEquals(String.valueOf(originQueryTimeOut),
row.get(1));
} else if (row.get(0).equalsIgnoreCase("sql_mode")) {
- Assert.assertEquals("", row.get(1));
+ Assert.assertEquals("ONLY_FULL_GROUP_BY", row.get(1));
} else if (row.get(0).equalsIgnoreCase("insert_timeout")) {
Assert.assertEquals(String.valueOf(originInsertTimeout),
row.get(1));
}
diff --git
a/regression-test/suites/nereids_p0/aggregate/non_standard_aggregate.groovy
b/regression-test/suites/nereids_p0/aggregate/non_standard_aggregate.groovy
new file mode 100644
index 00000000000..d3ed17a8b99
--- /dev/null
+++ b/regression-test/suites/nereids_p0/aggregate/non_standard_aggregate.groovy
@@ -0,0 +1,187 @@
+// 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("non_standard_aggregate") {
+ sql """
+ set sql_mode = "";
+ """
+
+ sql """
+ DROP TABLE IF EXISTS non_standard_aggregate
+ """
+
+ sql """
+ DROP TABLE IF EXISTS non_standard_aggregate_2
+ """
+
+ sql """
+ CREATE TABLE non_standard_aggregate (
+ c1 int,
+ c2 int,
+ c3 string,
+ c4 array<int>
+ )
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ """
+
+ sql """
+ CREATE TABLE non_standard_aggregate_2 (
+ c5 int,
+ c6 int,
+ c7 string,
+ c8 array<int>
+ )
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ """
+
+ sql """
+ INSERT INTO non_standard_aggregate VALUES (1, 2, 'hello,world', [1, 2,
3, 4])
+ """
+
+ sql """
+ INSERT INTO non_standard_aggregate_2 VALUES (1, 2, 'hello,world', [1,
2, 3, 4])
+ """
+
+ // simple case
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1"""
+
+ // not group by key
+ sql """SELECT c1, sum(c2) FROM non_standard_aggregate"""
+
+ // use two scalar column as group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + c2"""
+
+ // both group by key and scalar column in one expression
+ sql """SELECT c1 + c2, c3 FROM non_standard_aggregate GROUP BY c1"""
+
+ // all scalar column not in group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + 1"""
+
+ // scalar column with function
+ sql """SELECT c1, c2 + 1 FROM non_standard_aggregate GROUP BY c1 + 1"""
+
+ // use two scalar column as group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + c2
HAVING c1 < 5"""
+
+ // use function with two scalar column as group by key, function with two
scalar column in having
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + c2
HAVING c1 + c2 < 5"""
+
+ // use function with two scalar column as group by key, function with two
scalar column in having
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + c2
HAVING c1 + c3 < 5"""
+
+ // use function with two scalar column as group by key, function with both
scalar column and group by key in having
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 + c2
HAVING c1 + c2 + c1 < 10"""
+
+ // both group by key and scalar column in one expression and use it in
having
+ sql """SELECT c1 + c2, c3 FROM non_standard_aggregate GROUP BY c1 HAVING
c1 + c2 < 5"""
+
+ // having with aggregate function but project not
+ sql """SELECT c1 FROM non_standard_aggregate HAVING max(c1) = c1"""
+ sql """SELECT c2 FROM non_standard_aggregate HAVING max(c1) = c2"""
+
+ // having with both aggregate function and scalar column
+ sql """SELECT max(c1) FROM non_standard_aggregate HAVING c1 = count(c1)"""
+ sql """SELECT max(c1) FROM non_standard_aggregate HAVING c2 = count(c1)"""
+
+ // lateral view, be do not support any_value(array)
+ // sql """SELECT c1, c4, c5 FROM non_standard_aggregate LATERAL VIEW
explode(c4) tmp as c5 GROUP BY c1 HAVING c5 < 5"""
+ sql """SELECT c1, c5 FROM non_standard_aggregate LATERAL VIEW explode(c4)
tmp as c5 GROUP BY c1 HAVING c5 < 5"""
+
+ // join
+ sql """SELECT c1, c5 FROM non_standard_aggregate JOIN
non_standard_aggregate_2 ON c2 = c6 GROUP BY c1"""
+
+ // filter
+ sql """SELECT c1, c2 FROM non_standard_aggregate WHERE c3 = 'hello,world'
GROUP BY c2"""
+
+ // window as scalar column
+ sql """SELECT c1 + 1, LAG(c2, 0, NULL) OVER(PARTITION BY c1 ORDER BY c3)
FROM non_standard_aggregate GROUP BY c1 + 1"""
+
+ // having to filter group by key
+ sql """SELECT c1, c2 + 1 FROM non_standard_aggregate GROUP BY c1 + 1
HAVING c1 + 1 < 5"""
+
+ // having to filter scalar column
+ sql """SELECT c1, c2 + 1 FROM non_standard_aggregate GROUP BY c1 + 1
HAVING c2 + 1 < 5"""
+
+ // having with neither group by key nor scalar column
+ sql """SELECT c1, c2 + 1 FROM non_standard_aggregate GROUP BY c1 + 1
HAVING c2 + 2 < 5"""
+
+ // having to filter window
+ sql """SELECT c1 + 1, LAG(c2, 0, NULL) OVER(PARTITION BY c1 ORDER BY c3)
AS c3 FROM non_standard_aggregate GROUP BY c1 + 1 HAVING c3 < 10"""
+
+ // order by with group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c1"""
+
+ // order by with scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c2"""
+
+ // order by with function of group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c1 + 1"""
+
+ // order by with function of scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c2 + 1"""
+
+ // order by with aggregate function
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
sum(c2)"""
+
+ // top-n with group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c1 LIMIT 10"""
+
+ // top-n with scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c2 LIMIT 10"""
+
+ // top-n with function of group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c1 + 1 LIMIT 10"""
+
+ // top-n with function of scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
c2 + 1 LIMIT 10"""
+
+ // top-n with aggregate function
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 ORDER BY
sum(c2) LIMIT 10"""
+
+ // having scalar column + order by aggregate function
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 HAVING c2
< 10 ORDER BY sum(c2)"""
+
+ // having scalar column + order by scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 HAVING c2
< 10 ORDER BY c3"""
+
+ // having aggregate function + order by scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY c1 HAVING
sum(c2) < 10 ORDER BY c3"""
+
+
+ // repeat
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ())"""
+
+ // repeat with having on group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ()) HAVING c1 < 5"""
+
+ // repeat with having on function with group by key
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ()) HAVING c1 + 1 < 5"""
+
+ // repeat with having on scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ()) HAVING c3 < 5"""
+
+ // repeat with having on function with scalar column
+ sql """SELECT c1, c2, c3 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ()) HAVING c3 + 1 < 5"""
+
+ // repeat with having with neither group by key nor scalar column
+ sql """SELECT c1, c2, c3 + 1 FROM non_standard_aggregate GROUP BY GROUPING
SETS((c1), (c1, c2), ()) HAVING c3 < 5"""
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
