This is an automated email from the ASF dual-hosted git repository.
huajianlan 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 758059f143c [fix](nereids) stop merge project when generating huge
expression (#55293)
758059f143c is described below
commit 758059f143ccd5cbb629e94f6263ec6d70ef9aa1
Author: yujun <[email protected]>
AuthorDate: Mon Sep 1 11:37:01 2025 +0800
[fix](nereids) stop merge project when generating huge expression (#55293)
when merge projects, it will replace the parent project's slot reference
with the child project's origin expression.
for example:
```
LogicalProject ( k + k * 2 + k * 3)
|
LogicalProject ( a + b as k)
```
after replace `k` with `a + b`, we will got the merged project:
```
LogicalProject( (a + b) + (a + b) * 2 + (a + b) * 3)
```
suppose the origin parent project contains n `k` , then the merged
project will contains 3n (`a + b` contains 3 expression) related
expressions.
then if there are a continous project chain, after merge these projects,
the final merge expression's size may grow exponentially, finally may
exceed expression size limit, then throw exception `Exceeded the maximum
children of an expression tree`.
so when merging projects, if generate a big expression which its size
exceeds the limit, need to stop merging projects.
---
.../nereids/exceptions/AnalysisException.java | 34 ++++++-
.../doris/nereids/exceptions/CastException.java | 2 +-
.../doris/nereids/exceptions/ParseException.java | 2 +-
.../processor/post/MergeProjectPostProcessor.java | 11 ++-
.../rules/rewrite/DeferMaterializeTopNResult.java | 14 ++-
.../doris/nereids/rules/rewrite/MergeProjects.java | 9 +-
.../nereids/trees/expressions/Expression.java | 10 +-
.../doris/nereids/trees/plans/algebra/Project.java | 23 +++--
.../trees/plans/logical/ProjectMergeable.java | 38 ++++----
.../org/apache/doris/nereids/util/PlanUtils.java | 19 +++-
.../merge_project/test_merge_project.out | Bin 0 -> 346 bytes
.../merge_project/test_merge_project.groovy | 105 +++++++++++++++++++++
12 files changed, 221 insertions(+), 46 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/AnalysisException.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/AnalysisException.java
index 18f89725e2f..e8b5d142f8f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/AnalysisException.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/AnalysisException.java
@@ -23,35 +23,48 @@ import java.util.Optional;
/** Nereids's AnalysisException. */
public class AnalysisException extends RuntimeException {
+ private final ErrorCode errorCode;
private final String message;
private final Optional<Integer> line;
private final Optional<Integer> startPosition;
private final Optional<LogicalPlan> plan;
- public AnalysisException(String message, Throwable cause,
Optional<Integer> line,
+ /** Constructor of AnalysisException. */
+ public AnalysisException(ErrorCode errorCode, String message, Throwable
cause, Optional<Integer> line,
Optional<Integer> startPosition, Optional<LogicalPlan> plan) {
super(message, cause);
+ this.errorCode = errorCode;
this.message = message;
this.line = line;
this.startPosition = startPosition;
this.plan = plan;
}
- public AnalysisException(String message, Optional<Integer> line,
+ /** Constructor of AnalysisException. */
+ public AnalysisException(ErrorCode errorCode, String message,
Optional<Integer> line,
Optional<Integer> startPosition, Optional<LogicalPlan> plan) {
super(message);
+ this.errorCode = errorCode;
this.message = message;
this.line = line;
this.startPosition = startPosition;
this.plan = plan;
}
+ public AnalysisException(ErrorCode errorCode, String message, Throwable
cause) {
+ this(errorCode, message, cause, Optional.empty(), Optional.empty(),
Optional.empty());
+ }
+
+ public AnalysisException(ErrorCode errorCode, String message) {
+ this(errorCode, message, Optional.empty(), Optional.empty(),
Optional.empty());
+ }
+
public AnalysisException(String message, Throwable cause) {
- this(message, cause, Optional.empty(), Optional.empty(),
Optional.empty());
+ this(ErrorCode.NONE, message, cause);
}
public AnalysisException(String message) {
- this(message, Optional.empty(), Optional.empty(), Optional.empty());
+ this(ErrorCode.NONE, message);
}
@Override
@@ -70,5 +83,16 @@ public class AnalysisException extends RuntimeException {
}
}
- // TODO: support ErrorCode
+ /** get error code.
+ */
+ public ErrorCode getErrorCode() {
+ return errorCode;
+ }
+
+ /** error code enum.
+ */
+ public enum ErrorCode {
+ NONE,
+ EXPRESSION_EXCEEDS_LIMIT,
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/CastException.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/CastException.java
index f01dcfbef37..5d3ac70206d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/CastException.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/CastException.java
@@ -27,7 +27,7 @@ public class CastException extends AnalysisException {
private final String message;
public CastException(String message) {
- super(message, Optional.of(0), Optional.of(0), Optional.empty());
+ super(ErrorCode.NONE, message, Optional.of(0), Optional.of(0),
Optional.empty());
this.message = message;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/ParseException.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/ParseException.java
index 697af739f89..e3c72f1d6e7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/ParseException.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/exceptions/ParseException.java
@@ -37,7 +37,7 @@ public class ParseException extends AnalysisException {
}
public ParseException(String message, Origin start, Optional<String>
command) {
- super(message, start.line, start.startPosition, Optional.empty());
+ super(ErrorCode.NONE, message, start.line, start.startPosition,
Optional.empty());
this.message = message;
this.start = start;
this.command = command;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/MergeProjectPostProcessor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/MergeProjectPostProcessor.java
index cf63e0c9a77..385f5458474 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/MergeProjectPostProcessor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/MergeProjectPostProcessor.java
@@ -23,6 +23,7 @@ import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.physical.PhysicalProject;
import java.util.List;
+import java.util.Optional;
/**
* merge consecutive projects
@@ -34,10 +35,12 @@ public class MergeProjectPostProcessor extends
PlanPostProcessor {
project = (PhysicalProject<? extends Plan>) super.visit(project, ctx);
Plan child = project.child();
if (child instanceof PhysicalProject &&
project.canMergeChildProjections((PhysicalProject) child)) {
- List<NamedExpression> projections =
project.mergeProjections((PhysicalProject) child);
- return (PhysicalProject) project
- .withProjectionsAndChild(projections, child.child(0))
- .copyStatsAndGroupIdFrom(project);
+ Optional<List<NamedExpression>> projections =
project.mergeProjections((PhysicalProject) child);
+ if (projections.isPresent()) {
+ return (PhysicalProject) project
+ .withProjectionsAndChild(projections.get(),
child.child(0))
+ .copyStatsAndGroupIdFrom(project);
+ }
}
return project;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/DeferMaterializeTopNResult.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/DeferMaterializeTopNResult.java
index c93023a8e1c..48b5ad4c931 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/DeferMaterializeTopNResult.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/DeferMaterializeTopNResult.java
@@ -189,8 +189,11 @@ public class DeferMaterializeTopNResult implements
RewriteRuleFactory {
).when(project ->
project.canMergeChildProjections(project.child().child()))).then(r -> {
LogicalProject<?> upperProject = r.child();
LogicalProject<LogicalOlapScan> bottomProject =
r.child().child().child();
- List<NamedExpression> projections =
upperProject.mergeProjections(bottomProject);
- LogicalProject<?> project =
upperProject.withProjects(projections);
+ Optional<List<NamedExpression>> projections =
upperProject.mergeProjections(bottomProject);
+ if (!projections.isPresent()) {
+ return null;
+ }
+ LogicalProject<?> project =
upperProject.withProjects(projections.get());
return deferMaterialize(r, r.child().child(),
Optional.of(project),
Optional.empty(), bottomProject.child());
})
@@ -248,8 +251,11 @@ public class DeferMaterializeTopNResult implements
RewriteRuleFactory {
).when(project ->
project.canMergeChildProjections(project.child().child()))).then(r -> {
LogicalProject<?> upperProject = r.child();
LogicalProject<LogicalFilter<LogicalOlapScan>>
bottomProject = r.child().child().child();
- List<NamedExpression> projections =
upperProject.mergeProjections(bottomProject);
- LogicalProject<?> project =
upperProject.withProjects(projections);
+ Optional<List<NamedExpression>> projections =
upperProject.mergeProjections(bottomProject);
+ if (!projections.isPresent()) {
+ return null;
+ }
+ LogicalProject<?> project =
upperProject.withProjects(projections.get());
LogicalFilter<LogicalOlapScan> filter =
bottomProject.child();
return deferMaterialize(r, r.child().child(),
Optional.of(project),
Optional.of(filter), filter.child());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MergeProjects.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MergeProjects.java
index 6a5de895ee0..f94af818ef4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MergeProjects.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/MergeProjects.java
@@ -24,6 +24,7 @@ import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import java.util.List;
+import java.util.Optional;
/**
* this rule aims to merge consecutive project. For example:
@@ -42,6 +43,7 @@ public class MergeProjects extends OneRewriteRuleFactory {
// TODO modify ExtractAndNormalizeWindowExpression to handle nested
window functions
// here we just don't merge two projects if there is any window
function
return logicalProject(logicalProject())
+ .when(project ->
project.canMergeChildProjections(project.child()))
.then(MergeProjects::mergeProjects)
.toRule(RuleType.MERGE_PROJECTS);
}
@@ -49,7 +51,10 @@ public class MergeProjects extends OneRewriteRuleFactory {
/** merge projects */
public static Plan mergeProjects(LogicalProject<?> project) {
LogicalProject<? extends Plan> childProject = (LogicalProject<?>)
project.child();
- List<NamedExpression> projectExpressions =
project.mergeProjections(childProject);
- return project.withProjectsAndChild(projectExpressions,
childProject.child(0));
+ Optional<List<NamedExpression>> projectExpressions =
project.mergeProjections(childProject);
+ if (!projectExpressions.isPresent()) {
+ return project;
+ }
+ return project.withProjectsAndChild(projectExpressions.get(),
childProject.child(0));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
index 8bb4e6630be..d6b091b8718 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
@@ -21,6 +21,7 @@ import org.apache.doris.common.Config;
import org.apache.doris.nereids.analyzer.Unbound;
import org.apache.doris.nereids.analyzer.UnboundVariable;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.exceptions.AnalysisException.ErrorCode;
import org.apache.doris.nereids.exceptions.UnboundException;
import org.apache.doris.nereids.trees.AbstractTreeNode;
import
org.apache.doris.nereids.trees.expressions.ArrayItemReference.ArrayItemSlot;
@@ -180,12 +181,13 @@ public abstract class Expression extends
AbstractTreeNode<Expression> implements
private void checkLimit() {
if (depth > Config.expr_depth_limit) {
- throw new AnalysisException(String.format("Exceeded the maximum
depth of an "
- + "expression tree (%s).", Config.expr_depth_limit));
+ throw new AnalysisException(ErrorCode.EXPRESSION_EXCEEDS_LIMIT,
+ String.format("Exceeded the maximum depth of an expression
tree (%s).", Config.expr_depth_limit));
}
if (width > Config.expr_children_limit) {
- throw new AnalysisException(String.format("Exceeded the maximum
children of an "
- + "expression tree (%s).", Config.expr_children_limit));
+ throw new AnalysisException(ErrorCode.EXPRESSION_EXCEEDS_LIMIT,
+ String.format("Exceeded the maximum children of an
expression tree (%s).",
+ Config.expr_children_limit));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/algebra/Project.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/algebra/Project.java
index 23232570f1b..5f29e50f471 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/algebra/Project.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/algebra/Project.java
@@ -28,12 +28,13 @@ import
org.apache.doris.nereids.trees.plans.logical.ProjectMergeable;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.PlanUtils;
+import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
-import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
+import java.util.Optional;
/**
* Common interface for logical/physical project.
@@ -67,24 +68,30 @@ public interface Project extends ProjectMergeable {
}
/**
- * combine upper level and bottom level projections
+ * combine upper level and bottom level projections.
+ * if the combined expressions too huge, will return empty.
* 1. alias combination, for example
* proj(x as y, b) --> proj(a as x, b, c) =>(a as y, b)
* 2. remove used projection in bottom project
* @param childProject bottom project
* @return project list for merged project
*/
- default List<NamedExpression> mergeProjections(Project childProject) {
- List<NamedExpression> projects = new ArrayList<>(
- PlanUtils.mergeProjections(childProject.getProjects(),
getProjects())
- );
+ default Optional<List<NamedExpression>> mergeProjections(Project
childProject) {
+ Optional<List<NamedExpression>> parentProjectsOpt
+ = PlanUtils.tryMergeProjections(childProject.getProjects(),
getProjects());
+ if (!parentProjectsOpt.isPresent()) {
+ return Optional.empty();
+ }
+ ImmutableList.Builder<NamedExpression> projectsBuilder
+ =
ImmutableList.builderWithExpectedSize(parentProjectsOpt.get().size());
+ projectsBuilder.addAll(parentProjectsOpt.get());
for (NamedExpression expression : childProject.getProjects()) {
// keep NoneMovableFunction for later use
if (expression.containsType(NoneMovableFunction.class)) {
- projects.add(expression);
+ projectsBuilder.add(expression);
}
}
- return projects;
+ return Optional.of(projectsBuilder.build());
}
/** check can merge two projects */
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/ProjectMergeable.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/ProjectMergeable.java
index 0663909d699..fb2c06a9abe 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/ProjectMergeable.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/ProjectMergeable.java
@@ -22,7 +22,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.NoneMovableFunction;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.util.PlanUtils;
-import java.util.ArrayList;
+import com.google.common.collect.ImmutableList;
+
import java.util.List;
import java.util.Optional;
@@ -48,27 +49,32 @@ public interface ProjectMergeable extends ProjectProcessor,
OutputPrunable, Plan
/** merge project until can not merge */
static Optional<Plan> mergeContinuedProjects(List<NamedExpression>
parentProject, Plan plan) {
- if (!(plan instanceof ProjectMergeable)
- || !((ProjectMergeable)
plan).canProcessProject(parentProject)) {
- return Optional.empty();
- }
+ Optional<Plan> result = Optional.empty();
List<NamedExpression> mergedProjects = parentProject;
- ProjectMergeable child = (ProjectMergeable) plan;
- while (true) {
- mergedProjects = new
ArrayList<>(PlanUtils.mergeProjections(child.getProjects(), mergedProjects));
- for (NamedExpression expression : child.getProjects()) {
+ for (Plan child = plan; child instanceof ProjectMergeable;
+ child = child.arity() == 1 ? child.child(0) : null) {
+ ProjectMergeable projectable = (ProjectMergeable) child;
+ if (!projectable.canProcessProject(mergedProjects)) {
+ break;
+ }
+ Optional<List<NamedExpression>> newMergeProjectsOpt
+ = PlanUtils.tryMergeProjections(projectable.getProjects(),
mergedProjects);
+ if (!newMergeProjectsOpt.isPresent()) {
+ break;
+ }
+ ImmutableList.Builder<NamedExpression> newProjectsBuilder
+ =
ImmutableList.builderWithExpectedSize(newMergeProjectsOpt.get().size());
+ newProjectsBuilder.addAll(newMergeProjectsOpt.get());
+ for (NamedExpression expression : projectable.getProjects()) {
// keep NoneMovableFunction for later use
if (expression.containsType(NoneMovableFunction.class)) {
- mergedProjects.add(expression);
+ newProjectsBuilder.add(expression);
}
}
- if (child.arity() == 1 && child.child(0) instanceof
ProjectMergeable
- && ((ProjectMergeable)
child.child(0)).canProcessProject(mergedProjects)) {
- child = (ProjectMergeable) child.child(0);
- continue;
- }
- return Optional.of(child.withProjects(mergedProjects));
+ mergedProjects = newProjectsBuilder.build();
+ result = Optional.of(projectable.withProjects(mergedProjects));
}
+ return result;
}
List<NamedExpression> getProjects();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/PlanUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/PlanUtils.java
index 9c18c6ba32c..7dcf9ecb145 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/PlanUtils.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/PlanUtils.java
@@ -25,6 +25,7 @@ import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.StatementContext;
import org.apache.doris.nereids.analyzer.Scope;
import org.apache.doris.nereids.analyzer.UnboundSlot;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.glue.translator.ExpressionTranslator;
import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
import org.apache.doris.nereids.jobs.executor.Rewriter;
@@ -143,7 +144,23 @@ public class PlanUtils {
}
/**
- * merge childProjects with parentProjects
+ * try merge childProjects with parentProjects. if merged expression
exceeds limit, return empty.
+ */
+ public static Optional<List<NamedExpression>> tryMergeProjections(List<?
extends NamedExpression> childProjects,
+ List<? extends NamedExpression> parentProjects) {
+ try {
+ return Optional.of(mergeProjections(childProjects,
parentProjects));
+ } catch (AnalysisException e) {
+ if (e.getErrorCode() ==
AnalysisException.ErrorCode.EXPRESSION_EXCEEDS_LIMIT) {
+ return Optional.empty();
+ } else {
+ throw e;
+ }
+ }
+ }
+
+ /**
+ * merge childProjects with parentProjects. if merged expression exceeds
limit, will throw AnalysisException.
*/
public static List<NamedExpression> mergeProjections(List<? extends
NamedExpression> childProjects,
List<? extends NamedExpression> parentProjects) {
diff --git
a/regression-test/data/nereids_rules_p0/merge_project/test_merge_project.out
b/regression-test/data/nereids_rules_p0/merge_project/test_merge_project.out
new file mode 100644
index 00000000000..56c097d00f8
Binary files /dev/null and
b/regression-test/data/nereids_rules_p0/merge_project/test_merge_project.out
differ
diff --git
a/regression-test/suites/nereids_rules_p0/merge_project/test_merge_project.groovy
b/regression-test/suites/nereids_rules_p0/merge_project/test_merge_project.groovy
new file mode 100644
index 00000000000..f522dcdfa7d
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/merge_project/test_merge_project.groovy
@@ -0,0 +1,105 @@
+// 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_merge_project', 'nonConcurrent') {
+ setFeConfigTemporary([expr_children_limit : 200]) {
+ def tbl = 'tbl_test_merge_project'
+ multi_sql """
+ SET ignore_shape_nodes='PhysicalDistribute';
+ drop table if exists ${tbl} force;
+ create table ${tbl} (a int, b int) properties('replication_num' =
'1');
+ insert into ${tbl} values (2, 1), (4, 3);
+ """
+
+ explainAndOrderResult 'exceeds_expression_limit', """
+ select
+ case
+ when k15 > k16 then k15 + k16 + 1
+ when k15 > k16 - 1 then k15 + k16 - 1
+ when k15 > k16 - 2 then k15 + k16 - 2
+ else 0
+ end as k17,
+ k15 + k16 as k18
+ from
+ (select
+ case
+ when k13 > k14 then k13 + k14 + 1
+ when k13 > k14 - 1 then k13 + k14 - 1
+ when k13 > k14 - 2 then k13 + k14 - 2
+ else 0
+ end as k15,
+ k13 + k14 as k16
+ from
+ (select
+ case
+ when k11 > k12 then k11 + k12 + 1
+ when k11 > k12 - 1 then k11 + k12 - 1
+ when k11 > k12 - 2 then k11 + k12 - 2
+ else 0
+ end as k13,
+ k11 + k12 as k14
+ from
+ (select
+ case
+ when k9 > k10 then k9 + k10 + 1
+ when k9 > k10 - 1 then k9 + k10 - 1
+ when k9 > k10 - 2 then k9 + k10 - 2
+ else 0
+ end as k11,
+ k9 + k10 as k12
+ from
+ (select
+ case
+ when k7 > k8 then k7 + k8 + 1
+ when k7 > k8 - 1 then k7 + k8 - 1
+ when k7 > k8 - 2 then k7 + k8 - 2
+ else 0
+ end as k9,
+ k7 + k8 as k10
+ from
+ (select
+ case
+ when k5 > k6 then k5 + k6 + 1
+ when k5 > k6 - 1 then k5 + k6 - 1
+ when k5 > k6 - 2 then k5 + k6 - 2
+ else 0
+ end as k7,
+ k5 + k6 as k8
+ from
+ (select
+ case
+ when k3 > k4 then k3 + k4 + 1
+ when k3 > k4 - 1 then k3 + k4 - 1
+ when k3 > k4 - 2 then k3 + k4 - 2
+ else 0
+ end as k5,
+ k3 + k4 as k6
+ from
+ (select
+ case
+ when k1 > k2 then k1 + k2 + 1
+ when k1 > k2 - 1 then k1 + k2 - 1
+ when k1 > k2 - 2 then k1 + k2 - 2
+ else 0
+ end as k3,
+ k1 + k2 as k4
+ from
+ (select a as k1, b as k2
+ from ${tbl}) t1) t2) t3) t4) t5) t6) t7) t8;
+ """
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
