This is an automated email from the ASF dual-hosted git repository. mbudiu pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push: new 7a402e8c9d [CALCITE-6020] Created a new RelRule for SUM to SUM0 conversion 7a402e8c9d is described below commit 7a402e8c9d229b780eaa86a7e80f7fc64da0369a Author: Norman Jordan <norman.jor...@improving.com> AuthorDate: Wed Jul 24 14:47:43 2024 -0700 [CALCITE-6020] Created a new RelRule for SUM to SUM0 conversion A new RelRule was created that looks for OVER in a the project list. If the OVER contains SUM in the aggregate, it is changed to SUM0. The conversion now happens during planning rather than when SQL is converted to Rel. --- .../java/org/apache/calcite/plan/RelOptRules.java | 1 + .../org/apache/calcite/rel/rules/CoreRules.java | 4 + .../rel/rules/ProjectOverSumToSum0Rule.java | 110 +++++++++++++++++++++ .../java/org/apache/calcite/rex/RexShuttle.java | 11 ++- .../apache/calcite/sql2rel/SqlToRelConverter.java | 9 +- .../java/org/apache/calcite/tools/Programs.java | 3 +- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 16 ++- .../org/apache/calcite/test/RelOptRulesTest.java | 12 +++ .../org/apache/calcite/test/RelOptRulesTest.xml | 19 ++++ .../apache/calcite/test/SqlToRelConverterTest.xml | 10 +- 10 files changed, 178 insertions(+), 17 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java index aeb7101c64..8b513de0fb 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java @@ -114,6 +114,7 @@ public class RelOptRules { CoreRules.UNION_MERGE, CoreRules.INTERSECT_MERGE, CoreRules.MINUS_MERGE, + CoreRules.PROJECT_OVER_SUM_TO_SUM0_RULE, CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW, CoreRules.FILTER_MERGE, DateRangeRules.FILTER_INSTANCE, diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java index 69766761fb..b88e1459b2 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java @@ -431,6 +431,10 @@ public class CoreRules { public static final SubQueryRemoveRule JOIN_SUB_QUERY_TO_CORRELATE = SubQueryRemoveRule.Config.JOIN.toRule(); + /** Rule that converts SUM to SUM0 in OVER clauses in a project list. */ + public static final ProjectOverSumToSum0Rule PROJECT_OVER_SUM_TO_SUM0_RULE = + ProjectOverSumToSum0Rule.Config.DEFAULT.toRule(); + /** Rule that transforms a {@link Project} * into a mixture of {@code LogicalProject} * and {@link LogicalWindow}. */ diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectOverSumToSum0Rule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectOverSumToSum0Rule.java new file mode 100644 index 0000000000..14c17b3db2 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectOverSumToSum0Rule.java @@ -0,0 +1,110 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.plan.RelRule; +import org.apache.calcite.rel.core.Project; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.rex.RexShuttle; +import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.fun.SqlStdOperatorTable; +import org.apache.calcite.tools.RelBuilder; + +import org.immutables.value.Value; + +import java.util.List; + +/** + * Planner rule that converts SUM to SUM0 when it is the aggregate for an OVER clause inside + * the project list. Need to ensure that the result of the aggregate function for an OVER + * clause is not NULL. + */ +public class ProjectOverSumToSum0Rule + extends RelRule<ProjectOverSumToSum0Rule.Config> + implements TransformationRule { + + /** Creates a ProjectOverSumToSum0Rule. */ + protected ProjectOverSumToSum0Rule(ProjectOverSumToSum0Rule.Config config) { + super(config); + } + + @Override public void onMatch(final RelOptRuleCall call) { + Project project = call.rel(0); + assert project.containsOver(); + + // For OVER clauses, convert SUM to SUM0 + final RexShuttle sumToSum0 = new RexShuttle() { + @Override public SqlAggFunction visitOverAggFunction(SqlAggFunction op) { + if (op == SqlStdOperatorTable.SUM) { + return SqlStdOperatorTable.SUM0; + } else { + return op; + } + } + }; + + // Modify the top LogicalProject + final List<RexNode> topProjExps = + sumToSum0.visitList(project.getProjects()); + + final RelBuilder relBuilder = call.builder(); + relBuilder.push(project.getInput()); + relBuilder.project(topProjExps, project.getRowType().getFieldNames()); + call.transformTo(relBuilder.build()); + } + + private static boolean projectContainsOverWithSum(Project project) { + if (project.containsOver()) { + final HaveOverWithSumRexShuttle rexShuttle = new HaveOverWithSumRexShuttle(); + rexShuttle.visitList(project.getProjects()); + return rexShuttle.haveOverWithSum; + } + + return false; + } + + /** A RexShuttle that looks for a SUM aggregate in an OVER clause. + */ + private static class HaveOverWithSumRexShuttle extends RexShuttle { + private boolean haveOverWithSum; + + @Override public SqlAggFunction visitOverAggFunction(SqlAggFunction op) { + if (op == SqlStdOperatorTable.SUM) { + haveOverWithSum = true; + } + + return op; + } + } + + /** Rule configuration. */ + @Value.Immutable + public interface Config extends RelRule.Config { + ProjectOverSumToSum0Rule.Config DEFAULT = + ImmutableConfig.of() + .withOperandSupplier(b -> + b.operand(Project.class) + .predicate(ProjectOverSumToSum0Rule::projectContainsOverWithSum) + .anyInputs()) + .withDescription("ProjectOverSumToSum0Rule"); + + @Override default ProjectOverSumToSum0Rule toRule() { + return new ProjectOverSumToSum0Rule(this); + } + } +} diff --git a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java index a836a4ac65..466a0df6fb 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java +++ b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.rex; +import org.apache.calcite.sql.SqlAggFunction; + import com.google.common.collect.ImmutableList; import org.checkerframework.checker.nullness.qual.Nullable; @@ -41,8 +43,9 @@ public class RexShuttle implements RexVisitor<RexNode> { @Override public RexNode visitOver(RexOver over) { boolean[] update = {false}; List<RexNode> clonedOperands = visitList(over.operands, update); + SqlAggFunction overAggregator = visitOverAggFunction(over.getAggOperator()); RexWindow window = visitWindow(over.getWindow()); - if (update[0] || (window != over.getWindow())) { + if (update[0] || (window != over.getWindow()) || overAggregator != over.getAggOperator()) { // REVIEW jvs 8-Mar-2005: This doesn't take into account // the fact that a rewrite may have changed the result type. // To do that, we would need to take a RexBuilder and @@ -50,7 +53,7 @@ public class RexShuttle implements RexVisitor<RexNode> { // the type is embedded in the original call. return new RexOver( over.getType(), - over.getAggOperator(), + overAggregator, clonedOperands, window, over.isDistinct(), @@ -60,6 +63,10 @@ public class RexShuttle implements RexVisitor<RexNode> { } } + public SqlAggFunction visitOverAggFunction(SqlAggFunction op) { + return op; + } + public RexWindow visitWindow(RexWindow window) { boolean[] update = {false}; List<RexFieldCollation> clonedOrderKeys = diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index 44f264758a..e32c48f756 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -6192,12 +6192,9 @@ public class SqlToRelConverter { return histogramCall; } else { - boolean needSum0 = aggOp == SqlStdOperatorTable.SUM + boolean nullWhenCountZero = aggOp == SqlStdOperatorTable.SUM && type.isNullable(); - SqlAggFunction aggOpToUse = - needSum0 ? SqlStdOperatorTable.SUM0 - : aggOp; - return relBuilder.aggregateCall(aggOpToUse, exprs) + return relBuilder.aggregateCall(aggOp, exprs) .distinct(distinct) .ignoreNulls(ignoreNulls) .over() @@ -6208,7 +6205,7 @@ public class SqlToRelConverter { : c.rangeBetween(lowerBound, upperBound)) .exclude(exclude) .allowPartial(allowPartial) - .nullWhenCountZero(needSum0) + .nullWhenCountZero(nullWhenCountZero) .toRex(); } } diff --git a/core/src/main/java/org/apache/calcite/tools/Programs.java b/core/src/main/java/org/apache/calcite/tools/Programs.java index 0b7a12eacf..b30d278dda 100644 --- a/core/src/main/java/org/apache/calcite/tools/Programs.java +++ b/core/src/main/java/org/apache/calcite/tools/Programs.java @@ -249,7 +249,8 @@ public class Programs { builder.addRuleCollection( ImmutableList.of(CoreRules.FILTER_SUB_QUERY_TO_CORRELATE, CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE, - CoreRules.JOIN_SUB_QUERY_TO_CORRELATE)); + CoreRules.JOIN_SUB_QUERY_TO_CORRELATE, + CoreRules.PROJECT_OVER_SUM_TO_SUM0_RULE)); return of(builder.build(), true, metadataProvider); } diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index 605aa377d9..6d5af48cdc 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -37,6 +37,7 @@ import org.apache.calcite.rel.rules.AggregateJoinTransposeRule; import org.apache.calcite.rel.rules.AggregateProjectMergeRule; import org.apache.calcite.rel.rules.CoreRules; import org.apache.calcite.rel.rules.FilterJoinRule; +import org.apache.calcite.rel.rules.ProjectOverSumToSum0Rule; import org.apache.calcite.rel.rules.ProjectToWindowRule; import org.apache.calcite.rel.rules.PruneEmptyRules; import org.apache.calcite.rel.type.RelDataType; @@ -1397,8 +1398,14 @@ class RelToSqlConverterTest { + " ELSE NULL END / (COUNT(\"net_weight\")" + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW))\n" + "FROM \"foodmart\".\"product\""; - sql(query) - .withPostgresql().ok(expectedPostgresql); + + HepProgramBuilder builder = new HepProgramBuilder(); + builder.addRuleClass(ProjectOverSumToSum0Rule.class); + HepPlanner hepPlanner = new HepPlanner(builder.build()); + RuleSet rules = + RuleSets.ofList(CoreRules.PROJECT_OVER_SUM_TO_SUM0_RULE); + + sql(query).withPostgresql().optimize(rules, hepPlanner).ok(expectedPostgresql); } /** Test case for @@ -4659,9 +4666,12 @@ class RelToSqlConverterTest { + "FROM \"foodmart\".\"employee\""; HepProgramBuilder builder = new HepProgramBuilder(); + builder.addRuleClass(ProjectOverSumToSum0Rule.class); builder.addRuleClass(ProjectToWindowRule.class); HepPlanner hepPlanner = new HepPlanner(builder.build()); - RuleSet rules = RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW); + RuleSet rules = + RuleSets.ofList(CoreRules.PROJECT_OVER_SUM_TO_SUM0_RULE, + CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW); sql(query0).optimize(rules, hepPlanner).ok(expected0); sql(query1).optimize(rules, hepPlanner).ok(expected1); diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 8b350a2051..8a35065972 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -4400,6 +4400,18 @@ class RelOptRulesTest extends RelOptTestBase { .check(); } + @Test void testProjectOverWithAvg() { + final String sql = "select avg(sal) over (order by empno rows 3 preceding) from emp"; + + sql(sql) + .withRule(CoreRules.PROJECT_OVER_SUM_TO_SUM0_RULE, + CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW, + CoreRules.PROJECT_MERGE, + CoreRules.PROJECT_WINDOW_TRANSPOSE, + CoreRules.WINDOW_REDUCE_EXPRESSIONS) + .check(); + } + @Test void testEmptyFilterProjectUnion() { // Plan should be same as for // select * from (values (30, 3)) as t(x, y)"); diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index 3135c5e958..01aa3af1ff 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -8337,6 +8337,25 @@ from ( LogicalProject(EXPR$0=[ROW_NUMBER() OVER (ORDER BY $0)], COL1=[$1]) LogicalProject(DEPTNO=[$7], COL1=[SUM(100) OVER (PARTITION BY $7 ORDER BY $5)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectOverWithAvg"> + <Resource name="sql"> + <![CDATA[select avg(sal) over (order by empno rows 3 preceding) from emp]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EXPR$0=[CAST(/(CASE(>(COUNT($5) OVER (ORDER BY $0 ROWS 3 PRECEDING), 0), SUM($5) OVER (ORDER BY $0 ROWS 3 PRECEDING), null:INTEGER), COUNT($5) OVER (ORDER BY $0 ROWS 3 PRECEDING))):INTEGER]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EXPR$0=[CAST(/(CASE(>($2, 0), $3, null:INTEGER), $2)):INTEGER]) + LogicalWindow(window#0=[window(order by [0] rows between $2 PRECEDING and CURRENT ROW aggs [COUNT($1), $SUM0($1)])]) + LogicalProject(EMPNO=[$0], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index 4b08621046..d97cf8c12e 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -5803,7 +5803,7 @@ LogicalSort(sort0=[$1], dir0=[ASC]) <TestCase name="testOverAvg"> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[CAST(/(CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER), COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING))):INTEGER]) +LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[CAST(/(CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER), COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING))):INTEGER]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -5823,7 +5823,7 @@ window w1 as (partition by job order by hiredate rows 2 preceding)]]> </Resource> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[/(CASE(>(COUNT(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:REAL), COUNT(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING))]) +LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[/(CASE(>(COUNT(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:REAL), COUNT(CAST($5):REAL NOT NULL) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING))]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -5870,7 +5870,7 @@ LogicalProject(C1=[COUNT() OVER (ORDER BY $7)], C2=[COUNT() OVER (ORDER BY $7)], <TestCase name="testOverMultiple"> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[CASE(>(COUNT($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), $SUM0($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$2=[CASE(>=(COUNT() OVER (PARTITION BY $2 ORDER BY $4 ROWS 3 PRECEDING), 2), CASE(>(COUNT($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 3 PRECEDING), 0), [...] +LogicalProject(EXPR$0=[CASE(>(COUNT($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM($5) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$1=[CASE(>(COUNT($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), 0), SUM($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 2 PRECEDING), null:INTEGER)], EXPR$2=[CASE(>=(COUNT() OVER (PARTITION BY $2 ORDER BY $4 ROWS 3 PRECEDING), 2), CASE(>(COUNT($7) OVER (PARTITION BY $2 ORDER BY $4 ROWS 3 PRECEDING), 0), SUM( [...] LogicalFilter(condition=[>(-($7, $5), 999)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> @@ -6919,7 +6919,7 @@ LogicalProject(DUMMY=[null:TIMESTAMP(0)]) <TestCase name="testSelectOverDistinct"> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CASE(>(COUNT(DISTINCT $7) OVER (ORDER BY $0 ROWS 10 PRECEDING), 0), $SUM0(DISTINCT $7) OVER (ORDER BY $0 ROWS 10 PRECEDING), null:INTEGER)]) +LogicalProject(EXPR$0=[CASE(>(COUNT(DISTINCT $7) OVER (ORDER BY $0 ROWS 10 PRECEDING), 0), SUM(DISTINCT $7) OVER (ORDER BY $0 ROWS 10 PRECEDING), null:INTEGER)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> @@ -9199,7 +9199,7 @@ WINDOW w AS (PARTITION BY REGION ORDER BY n_nationkey)]]> </Resource> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CASE(>(COUNT(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY')), 0), $SUM0(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY')), null:ANY)]) +LogicalProject(EXPR$0=[CASE(>(COUNT(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY')), 0), SUM(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY')), null:ANY)]) LogicalTableScan(table=[[CATALOG, SALES, NATION]]) ]]> </Resource>