You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/07/15 16:38:59 UTC
[calcite] 03/06: [CALCITE-3144] Add rule, AggregateCaseToFilterRule,
that converts "SUM(CASE WHEN b THEN x END)" to "SUM(x) FILTER
(WHERE b)"
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 687b7d8cc8c63259138b43745a9e28ef64483839
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Mon Jul 1 12:57:14 2019 -0700
[CALCITE-3144] Add rule, AggregateCaseToFilterRule, that converts "SUM(CASE WHEN b THEN x END)" to "SUM(x) FILTER (WHERE b)"
Copied from Apache Druid's CaseFilteredAggregatorRule.
Some aggregate functions (e.g. SINGLE_VALUE, GROUPING, GROUP_ID) do
not allow filter, so we skip them in the rule.
---
.../java/org/apache/calcite/plan/RelOptRules.java | 2 +
.../org/apache/calcite/rel/core/AggregateCall.java | 2 +
.../rel/rules/AggregateCaseToFilterRule.java | 268 +++++++++++++++++++++
.../calcite/sql/fun/SqlSingleValueAggFunction.java | 4 +
.../org/apache/calcite/test/RelOptRulesTest.java | 18 +-
.../org/apache/calcite/test/RelOptTestBase.java | 8 +-
.../org/apache/calcite/test/RelOptRulesTest.xml | 30 +++
core/src/test/resources/sql/agg.iq | 32 +++
8 files changed, 361 insertions(+), 3 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 dcb2f7d..cff4b6c 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
@@ -22,6 +22,7 @@ import org.apache.calcite.interpreter.NoneToBindableConverterRule;
import org.apache.calcite.linq4j.function.Experimental;
import org.apache.calcite.plan.volcano.AbstractConverter;
import org.apache.calcite.rel.rules.AbstractMaterializedViewRule;
+import org.apache.calcite.rel.rules.AggregateCaseToFilterRule;
import org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule;
import org.apache.calcite.rel.rules.AggregateJoinTransposeRule;
import org.apache.calcite.rel.rules.AggregateMergeRule;
@@ -121,6 +122,7 @@ public class RelOptRules {
FilterJoinRule.FILTER_ON_JOIN,
JoinPushExpressionsRule.INSTANCE,
AggregateExpandDistinctAggregatesRule.INSTANCE,
+ AggregateCaseToFilterRule.INSTANCE,
AggregateReduceFunctionsRule.INSTANCE,
FilterAggregateTransposeRule.INSTANCE,
ProjectWindowTransposeRule.INSTANCE,
diff --git a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
index e95f46a..bb3c109 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
@@ -26,6 +26,7 @@ import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.util.mapping.Mapping;
import org.apache.calcite.util.mapping.Mappings;
+import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import java.util.List;
@@ -100,6 +101,7 @@ public class AggregateCall {
this.distinct = distinct;
this.approximate = approximate;
this.ignoreNulls = ignoreNulls;
+ Preconditions.checkArgument(filterArg < 0 || aggFunction.allowsFilter());
}
//~ Methods ----------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
new file mode 100644
index 0000000..77966ec
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
@@ -0,0 +1,268 @@
+/*
+ * 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.RelOptCluster;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexLiteral;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlPostfixOperator;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.tools.RelBuilderFactory;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.List;
+import javax.annotation.Nullable;
+
+/**
+ * Rule that converts CASE-style filtered aggregates into true filtered
+ * aggregates.
+ *
+ * <p>For example,
+ *
+ * <blockquote>
+ * <code>SELECT SUM(CASE WHEN gender = 'F' THEN salary END)<br>
+ * FROM Emp</code>
+ * </blockquote>
+ *
+ * <p>becomes
+ *
+ * <blockquote>
+ * <code>SELECT SUM(salary) FILTER (WHERE gender = 'F')<br>
+ * FROM Emp</code>
+ * </blockquote>
+ */
+public class AggregateCaseToFilterRule extends RelOptRule {
+ public static final AggregateCaseToFilterRule INSTANCE =
+ new AggregateCaseToFilterRule(RelFactories.LOGICAL_BUILDER, null);
+
+ /** Creates an AggregateCaseToFilterRule. */
+ protected AggregateCaseToFilterRule(RelBuilderFactory relBuilderFactory,
+ String description) {
+ super(operand(Aggregate.class, operand(Project.class, any())),
+ relBuilderFactory, description);
+ }
+
+ @Override public boolean matches(final RelOptRuleCall call) {
+ final Aggregate aggregate = call.rel(0);
+ final Project project = call.rel(1);
+
+ for (AggregateCall aggregateCall : aggregate.getAggCallList()) {
+ final int singleArg = soleArgument(aggregateCall);
+ if (singleArg >= 0
+ && isThreeArgCase(project.getProjects().get(singleArg))) {
+ return true;
+ }
+ }
+
+ return false;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Aggregate aggregate = call.rel(0);
+ final Project project = call.rel(1);
+ final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder();
+ final List<AggregateCall> newCalls =
+ new ArrayList<>(aggregate.getAggCallList().size());
+ final List<RexNode> newProjects = new ArrayList<>(project.getProjects());
+ final List<RexNode> newCasts = new ArrayList<>();
+
+ for (int fieldNumber : aggregate.getGroupSet()) {
+ newCasts.add(
+ rexBuilder.makeInputRef(
+ project.getProjects().get(fieldNumber).getType(), fieldNumber));
+ }
+
+ for (AggregateCall aggregateCall : aggregate.getAggCallList()) {
+ AggregateCall newCall =
+ transform(aggregateCall, project, newProjects);
+
+ // Possibly CAST the new aggregator to an appropriate type.
+ final int i = newCasts.size();
+ final RelDataType oldType =
+ aggregate.getRowType().getFieldList().get(i).getType();
+ if (newCall == null) {
+ newCalls.add(aggregateCall);
+ newCasts.add(rexBuilder.makeInputRef(oldType, i));
+ } else {
+ newCalls.add(newCall);
+ newCasts.add(
+ rexBuilder.makeCast(oldType,
+ rexBuilder.makeInputRef(newCall.getType(), i)));
+ }
+ }
+
+ if (newCalls.equals(aggregate.getAggCallList())) {
+ return;
+ }
+
+ final RelBuilder relBuilder = call.builder()
+ .push(project.getInput())
+ .project(newProjects);
+
+ final RelBuilder.GroupKey groupKey =
+ relBuilder.groupKey(aggregate.getGroupSet(),
+ aggregate.getGroupSets());
+
+ relBuilder.aggregate(groupKey, newCalls)
+ .convert(aggregate.getRowType(), false);
+
+ call.transformTo(relBuilder.build());
+ call.getPlanner().setImportance(aggregate, 0.0);
+ }
+
+ private @Nullable AggregateCall transform(AggregateCall aggregateCall,
+ Project project, List<RexNode> newProjects) {
+ final int singleArg = soleArgument(aggregateCall);
+ if (singleArg < 0) {
+ return null;
+ }
+
+ final RexNode rexNode = project.getProjects().get(singleArg);
+ if (!isThreeArgCase(rexNode)) {
+ return null;
+ }
+
+ final RelOptCluster cluster = project.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final RexCall caseCall = (RexCall) rexNode;
+
+ // If one arg is null and the other is not, reverse them and set "flip",
+ // which negates the filter.
+ final boolean flip = RexLiteral.isNullLiteral(caseCall.operands.get(1))
+ && !RexLiteral.isNullLiteral(caseCall.operands.get(2));
+ final RexNode arg1 = caseCall.operands.get(flip ? 2 : 1);
+ final RexNode arg2 = caseCall.operands.get(flip ? 1 : 2);
+
+ // Operand 1: Filter
+ final SqlPostfixOperator op =
+ flip ? SqlStdOperatorTable.IS_FALSE : SqlStdOperatorTable.IS_TRUE;
+ final RexNode filterFromCase =
+ rexBuilder.makeCall(op, caseCall.operands.get(0));
+
+ // Combine the CASE filter with an honest-to-goodness SQL FILTER, if the
+ // latter is present.
+ final RexNode filter;
+ if (aggregateCall.filterArg >= 0) {
+ filter = rexBuilder.makeCall(SqlStdOperatorTable.AND,
+ project.getProjects().get(aggregateCall.filterArg), filterFromCase);
+ } else {
+ filter = filterFromCase;
+ }
+
+ final SqlKind kind = aggregateCall.getAggregation().getKind();
+ if (aggregateCall.isDistinct()) {
+ // Just one style supported:
+ // COUNT(DISTINCT CASE WHEN x = 'foo' THEN y END)
+ // =>
+ // COUNT(DISTINCT y) FILTER(WHERE x = 'foo')
+
+ if (kind == SqlKind.COUNT
+ && RexLiteral.isNullLiteral(arg2)) {
+ newProjects.add(arg1);
+ newProjects.add(filter);
+ return AggregateCall.create(SqlStdOperatorTable.COUNT, true, false,
+ false, ImmutableList.of(newProjects.size() - 2),
+ newProjects.size() - 1, RelCollations.EMPTY,
+ aggregateCall.getType(), aggregateCall.getName());
+ }
+ return null;
+ }
+
+ // Four styles supported:
+ //
+ // A1: AGG(CASE WHEN x = 'foo' THEN cnt END)
+ // => operands (x = 'foo', cnt, null)
+ // A2: SUM(CASE WHEN x = 'foo' THEN cnt ELSE 0 END)
+ // => operands (x = 'foo', cnt, 0); must be SUM
+ // B: SUM(CASE WHEN x = 'foo' THEN 1 ELSE 0 END)
+ // => operands (x = 'foo', 1, 0); must be SUM
+ // C: COUNT(CASE WHEN x = 'foo' THEN 'dummy' END)
+ // => operands (x = 'foo', 'dummy', null)
+
+ if (kind == SqlKind.COUNT // Case C
+ && arg1.isA(SqlKind.LITERAL)
+ && !RexLiteral.isNullLiteral(arg1)
+ && RexLiteral.isNullLiteral(arg2)) {
+ newProjects.add(filter);
+ return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false,
+ false, ImmutableList.of(), newProjects.size() - 1,
+ RelCollations.EMPTY, aggregateCall.getType(),
+ aggregateCall.getName());
+ } else if (kind == SqlKind.SUM // Case B
+ && isIntLiteral(arg1) && RexLiteral.intValue(arg1) == 1
+ && isIntLiteral(arg2) && RexLiteral.intValue(arg2) == 0) {
+
+ newProjects.add(filter);
+ final RelDataTypeFactory typeFactory = cluster.getTypeFactory();
+ final RelDataType dataType =
+ typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BIGINT), false);
+ return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false,
+ false, ImmutableList.of(), newProjects.size() - 1,
+ RelCollations.EMPTY, dataType, aggregateCall.getName());
+ } else if ((RexLiteral.isNullLiteral(arg2) // Case A1
+ && aggregateCall.getAggregation().allowsFilter())
+ || (kind == SqlKind.SUM // Case A2
+ && isIntLiteral(arg2)
+ && RexLiteral.intValue(arg2) == 0)) {
+ newProjects.add(arg1);
+ newProjects.add(filter);
+ return AggregateCall.create(aggregateCall.getAggregation(), false,
+ false, false, ImmutableList.of(newProjects.size() - 2),
+ newProjects.size() - 1, RelCollations.EMPTY,
+ aggregateCall.getType(), aggregateCall.getName());
+ } else {
+ return null;
+ }
+ }
+
+ /** Returns the argument, if an aggregate call has a single argument,
+ * otherwise -1. */
+ private static int soleArgument(AggregateCall aggregateCall) {
+ return aggregateCall.getArgList().size() == 1
+ ? aggregateCall.getArgList().get(0)
+ : -1;
+ }
+
+ private static boolean isThreeArgCase(final RexNode rexNode) {
+ return rexNode.getKind() == SqlKind.CASE
+ && ((RexCall) rexNode).operands.size() == 3;
+ }
+
+ private static boolean isIntLiteral(final RexNode rexNode) {
+ return rexNode instanceof RexLiteral
+ && SqlTypeName.INT_TYPES.contains(rexNode.getType().getSqlTypeName());
+ }
+}
+
+// End AggregateCaseToFilterRule.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java
index 6f4518e..8eb52b7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java
@@ -59,6 +59,10 @@ public class SqlSingleValueAggFunction extends SqlAggFunction {
//~ Methods ----------------------------------------------------------------
+ @Override public boolean allowsFilter() {
+ return false;
+ }
+
@SuppressWarnings("deprecation")
public List<RelDataType> getParameterTypes(RelDataTypeFactory typeFactory) {
return ImmutableList.of(type);
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 756d223..8e97702 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -61,6 +61,7 @@ import org.apache.calcite.rel.metadata.CachingRelMetadataProvider;
import org.apache.calcite.rel.metadata.ChainedRelMetadataProvider;
import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataProvider;
+import org.apache.calcite.rel.rules.AggregateCaseToFilterRule;
import org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule;
import org.apache.calcite.rel.rules.AggregateExtractProjectRule;
import org.apache.calcite.rel.rules.AggregateFilterTransposeRule;
@@ -3297,6 +3298,21 @@ public class RelOptRulesTest extends RelOptTestBase {
sql(sql).withPre(pre).withRule(rule).checkUnchanged();
}
+ @Test public void testAggregateCaseToFilter() {
+ final String sql = "select\n"
+ + " sum(sal) as sum_sal,\n"
+ + " count(distinct case\n"
+ + " when job = 'CLERK'\n"
+ + " then deptno else null end) as count_distinct_clerk,\n"
+ + " sum(case when deptno = 10 then sal end) as sum_sal_d10,\n"
+ + " sum(case when deptno = 20 then sal else 0 end) as sum_sal_d20,\n"
+ + " sum(case when deptno = 30 then 1 else 0 end) as count_d30,\n"
+ + " count(case when deptno = 40 then 'x' end) as count_d40,\n"
+ + " count(case when deptno = 20 then 1 end) as count_d20\n"
+ + "from emp";
+ sql(sql).withRule(AggregateCaseToFilterRule.INSTANCE).check();
+ }
+
@Test public void testPullAggregateThroughUnion() {
HepProgram program = new HepProgramBuilder()
.addRuleInstance(AggregateUnionAggregateRule.INSTANCE)
@@ -5256,7 +5272,7 @@ public class RelOptRulesTest extends RelOptTestBase {
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3121">[CALCITE-3121]
- * VolcanoPlanner hangs due to subquery with dynamic star</a>. */
+ * VolcanoPlanner hangs due to sub-query with dynamic star</a>. */
@Test public void testSubQueryWithDynamicStarHang() {
String sql = "select n.n_regionkey from (select * from "
+ "(select * from sales.customer) t) n where n.n_nationkey >1";
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
index 507edd2..bf263c9 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
@@ -262,8 +262,12 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
transforms);
}
- public Sql withRule(RelOptRule rule) {
- return with(HepProgram.builder().addRuleInstance(rule).build());
+ public Sql withRule(RelOptRule... rules) {
+ final HepProgramBuilder builder = HepProgram.builder();
+ for (RelOptRule rule : rules) {
+ builder.addRuleInstance(rule);
+ }
+ return with(builder.build());
}
/** Adds a transform that will be applied to {@link #tester}
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 d2d83e0..288dfce 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -16,6 +16,36 @@ See the License for the specific language governing permissions and
limitations under the License.
-->
<Root>
+ <TestCase name="testAggregateCaseToFilter">
+ <Resource name="sql">
+ <![CDATA[select
+ sum(sal) as sum_sal,
+ count(distinct case
+ when job = 'CLERK'
+ then deptno else null end) as count_distinct_clerk,
+ sum(case when deptno = 10 then sal end) as sum_sal_d10,
+ sum(case when deptno = 20 then sal else 0 end) as sum_sal_d20,
+ sum(case when deptno = 30 then 1 else 0 end) as count_d30,
+ count(case when deptno = 40 then 'x' end) as count_d40,
+ count(case when deptno = 20 then 1 end) as count_d20
+from emp]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{}], SUM_SAL=[SUM($0)], COUNT_DISTINCT_CLERK=[COUNT(DISTINCT $1)], SUM_SAL_D10=[SUM($2)], SUM_SAL_D20=[SUM($3)], COUNT_D30=[SUM($4)], COUNT_D40=[COUNT($5)], COUNT_D20=[COUNT($6)])
+ LogicalProject(SAL=[$5], $f1=[CASE(=($2, 'CLERK'), $7, null:INTEGER)], $f2=[CASE(=($7, 10), $5, null:INTEGER)], $f3=[CASE(=($7, 20), $5, 0)], $f4=[CASE(=($7, 30), 1, 0)], $f5=[CASE(=($7, 40), 'x', null:CHAR(1))], $f6=[CASE(=($7, 20), 1, null:INTEGER)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SUM_SAL=[$0], COUNT_DISTINCT_CLERK=[$1], SUM_SAL_D10=[$2], SUM_SAL_D20=[$3], COUNT_D30=[CAST($4):INTEGER], COUNT_D40=[$5], COUNT_D20=[$6])
+ LogicalAggregate(group=[{}], SUM_SAL=[SUM($0)], COUNT_DISTINCT_CLERK=[COUNT(DISTINCT $7) FILTER $8], SUM_SAL_D10=[SUM($9) FILTER $10], SUM_SAL_D20=[SUM($11) FILTER $12], COUNT_D30=[COUNT() FILTER $13], COUNT_D40=[COUNT() FILTER $14], COUNT_D20=[COUNT() FILTER $15])
+ LogicalProject(SAL=[$5], $f1=[CASE(=($2, 'CLERK'), $7, null:INTEGER)], $f2=[CASE(=($7, 10), $5, null:INTEGER)], $f3=[CASE(=($7, 20), $5, 0)], $f4=[CASE(=($7, 30), 1, 0)], $f5=[CASE(=($7, 40), 'x', null:CHAR(1))], $f6=[CASE(=($7, 20), 1, null:INTEGER)], DEPTNO=[$7], $f8=[=($2, 'CLERK')], SAL0=[$5], $f10=[=($7, 10)], SAL1=[$5], $f12=[=($7, 20)], $f13=[=($7, 30)], $f14=[=($7, 40)], $f15=[=($7, 20)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testAggregateExtractProjectRule">
<Resource name="sql">
<![CDATA[select sum(sal)
diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq
index 550e36f..3f939f7 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -2548,6 +2548,38 @@ select deptno, bit_and(empno), bit_or(empno) from "scott".emp group by deptno;
!ok
+# Based on [DRUID-7593] Exact distinct-COUNT with complex expression (CASE, IN) throws
+# NullPointerException
+WITH wikipedia AS (
+ SELECT empno AS delta,
+ CASE WHEN deptno = 10 THEN 'true' ELSE 'false' END AS isRobot,
+ ename AS "user"
+ FROM "scott".emp)
+SELECT COUNT(DISTINCT
+ CASE WHEN (((CASE WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
+ THEN REPLACE('Yes', 'Yes', 'Yes')
+ ELSE REPLACE('No', 'No', 'No') END) = 'No'))
+ AND (wikipedia.isRobot = 'true')
+ THEN (wikipedia."user")
+ ELSE NULL END)
+ - (MAX(CASE WHEN (((CASE WHEN wikipedia.delta IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
+ THEN REPLACE('Yes', 'Yes', 'Yes')
+ ELSE REPLACE('No', 'No', 'No') END) = 'No'))
+ AND (wikipedia.isRobot = 'true')
+ THEN NULL
+ ELSE -9223372036854775807 END)
+ + 9223372036854775807 + 1) AS "wikipedia.count_distinct_filters_that_dont_work"
+FROM wikipedia
+LIMIT 500;
++-------------------------------------------------+
+| wikipedia.count_distinct_filters_that_dont_work |
++-------------------------------------------------+
+| 2 |
++-------------------------------------------------+
+(1 row)
+
+!ok
+
# [CALCITE-2266] JSON_OBJECTAGG, JSON_ARRAYAGG
!use post