Skip to content

[SQL] Generalize optimization rule for MAX(CASE WHEN expression THEN 1 END) #6591

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
mihaibudiu merged 1 commit into from
Jul 6, 2026
Merged

[SQL] Generalize optimization rule for MAX(CASE WHEN expression THEN 1 END) #6591

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
85 changes: 55 additions & 30 deletions .../org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/optimizer/MaxCaseToCountRule.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -30,15 +30,34 @@

/**
* Rule that converts MAX(CASE ... THEN 1 ELSE 0 END)
* into COUNT(...) FILTER (WHERE ...)
* Rewrite:
* or MAX(CASE ... THEN 1 ELSE NULL END) (equivalently, no ELSE clause)
* into COUNT(...) FILTER (WHERE ...).
*
* <p>Rewrite for ELSE 0:
* <pre>
* LogicalAggregate(group=[{0}], [MAX($1)]
* LogicalProject([$0], $f1=[CASE(=(...), 1, 0)])
* into
* </pre>
* becomes
* <pre>
* LogicalProject([$0], [CASE(>($1, 0), 1, 0)])
* LogicalAggregate(group=[{0}], agg#0=[COUNT(*) FILTER $1])
* LogicalProject([$0], $f1=[IS TRUE(...)])
* This works only if there is a GROUP BY.
* </pre>
*
* <p>Rewrite for ELSE NULL (or no ELSE):
* <pre>
* LogicalAggregate(group=[{0}], [MAX($1)]
* LogicalProject([$0], $f1=[CASE(=(...), 1, null)])
* </pre>
* becomes
* <pre>
* LogicalProject([$0], [CASE(>($1, 0), 1, null)])
* LogicalAggregate(group=[{0}], agg#0=[COUNT(*) FILTER $1])
* LogicalProject([$0], $f1=[IS TRUE(...)])
* </pre>
*
* <p>This works only if there is a GROUP BY.
*/
public class MaxCaseToCountRule
extends RelRule<DefaultOptRuleConfig<MaxCaseToCountRule>>
Expand Down Expand Up @@ -66,16 +85,17 @@ public void onMatch(RelOptRuleCall call) {
RelDataType bigInt = cluster.getTypeFactory().createSqlType(SqlTypeName.BIGINT);
bigInt = cluster.getTypeFactory().createTypeWithNullability(bigInt, true);

RexLiteral one = rexBuilder.makeLiteral(BigDecimal.ONE, bigInt);
newProjects.add(one);
for (int i = 0; i < aggregate.getGroupCount(); i++)
// Preserve the keys in the postProjects
postProjects.add(new RexInputRef(i, resultType.getFieldList().get(i).getType()));

final int groupCount = aggregate.getGroupCount();
for (AggregateCall aggregateCall : aggregate.getAggCallList()) {
AggregateCall newCall = transform(aggregateCall, project, newProjects, postProjects, bigInt);
if (newCall == null) {
postProjects.add(rexBuilder.makeInputRef(aggregateCall.getType(), newAggregates.size()));
// Aggregate output: [group_keys (0..groupCount-1), agg_0, agg_1, ...]
postProjects.add(rexBuilder.makeInputRef(
aggregateCall.getType(), groupCount + newAggregates.size()));
newAggregates.add(aggregateCall);
} else {
newAggregates.add(newCall);
Expand Down Expand Up @@ -147,11 +167,17 @@ public static Long getLiteralValue(RexNode node) {
if (op1Value == null)
return null;
Long op2Value = getLiteralValue(op2);
if (op2Value == null)
if (op2Value == null && !(op2 instanceof RexLiteral))

@mythical-fred mythical-fred Jul 3, 2026

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Nit: op2Value == null && !(op2 instanceof RexLiteral) reads as "reject non-literal op2", but you already know getLiteralValue returned null. A more direct spelling would be op2 instanceof RexLiteral lit && lit.isNull() (accept only null literal). Same behavior, clearer intent. Non-blocking.

return null;
boolean elseIsNull = op2Value == null;

boolean flip;
if (op1Value == 0L && op2Value == 1L) {
final boolean flip;
if (elseIsNull) {
// Only THEN 1 ELSE NULL is supported: MAX = 1 if any row matches, NULL otherwise.
if (op1Value != 1L)
return null;
flip = false;
} else if (op1Value == 0L && op2Value == 1L) {
flip = true;
} else if (op1Value == 1L && op2Value == 0L) {
flip = false;
Expand Down Expand Up @@ -179,27 +205,26 @@ public static Long getLiteralValue(RexNode node) {
false, call.rexList, ImmutableList.of(), newProjects.size() - 1, null,
RelCollations.EMPTY, bigInt, call.getName());

// CASE(WHEN $1 IS NULL THEN NULL WHEN $1 = 0 THEN 0 ELSE 1 END)
RelDataType nullable =
cluster.getTypeFactory().createTypeWithNullability(op1.getType(), call.type.isNullable());
List<RexNode> postOperands = new ArrayList<>();
// WHEN $1 IS NULL
RexInputRef ref = rexBuilder.makeInputRef(bigInt, postProjects.size());
RexNode isNull = rexBuilder.makeCall(pos, SqlStdOperatorTable.IS_NULL, ref);
postOperands.add(isNull);
// THEN NULL
RexLiteral nullLit = rexBuilder.makeNullLiteral(nullable);
postOperands.add(nullLit);
// WHEN $1 = 0
RexLiteral biggerZero = rexBuilder.makeLiteral(BigDecimal.ZERO, bigInt);
postOperands.add(rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, ref, biggerZero));
// THEN 0
RexNode zero = rexBuilder.makeLiteral(BigDecimal.ZERO, nullable, true);
postOperands.add(zero);
// ELSE one
RexNode one = rexBuilder.makeLiteral(BigDecimal.ONE, nullable, true);
postOperands.add(one);
final RexNode postCondition = rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE, postOperands);
List<RexNode> postOperands = new ArrayList<>();
RelDataType nullable = cluster.getTypeFactory().createTypeWithNullability(
op1.getType(), call.type.isNullable());
RexLiteral zero = rexBuilder.makeLiteral(BigDecimal.ZERO, bigInt);
final RexNode postCondition;
if (elseIsNull) {
// CASE WHEN count > 0 THEN 1 ELSE NULL END
postOperands.add(rexBuilder.makeCall(pos, SqlStdOperatorTable.GREATER_THAN, ref, zero));
postOperands.add(rexBuilder.makeLiteral(BigDecimal.ONE, nullable, true));
postOperands.add(rexBuilder.makeNullLiteral(nullable));
} else {
// CASE WHEN count IS NULL THEN NULL WHEN count = 0 THEN 0 ELSE 1 END
postOperands.add(rexBuilder.makeCall(pos, SqlStdOperatorTable.IS_NULL, ref));
postOperands.add(rexBuilder.makeNullLiteral(nullable));
postOperands.add(rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, ref, zero));
postOperands.add(rexBuilder.makeLiteral(BigDecimal.ZERO, nullable, true));
postOperands.add(rexBuilder.makeLiteral(BigDecimal.ONE, nullable, true));
}
postCondition = rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE, postOperands);
postProjects.add(postCondition);
return result;
}
Expand Down
80 changes: 78 additions & 2 deletions ...SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/Regression2Tests.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -980,13 +980,14 @@ public void issue5927() {
ccs.stepWeightOne("", """
g | max
---------""");
ccs.stepWeightOne("INSERT INTO T VALUES ('a', 1), ('b', 3), ('c', 3), ('c', 5), ('d', 1), ('d', 5);", """
ccs.stepWeightOne("INSERT INTO T VALUES ('a', 1), ('b', 3), ('c', 3), ('c', 5), ('d', 1), ('d', 5), ('e', NULL);", """
g | max
---------
a| 0
b| 1
c| 1
d| 1""");
d| 1
e| 0""");
ccs.visit(this.findLinear(ccs.compiler));
}

Expand Down Expand Up @@ -1042,6 +1043,81 @@ public void issue5927a() {
ccs.visit(this.findLinear(ccs.compiler));
}

@Test
public void issue6590() {
// MAX(CASE WHEN cond THEN 1 ELSE NULL END) — ELSE NULL variant
var ccs = this.getCCS("""
CREATE TABLE T(g VARCHAR, x INT);
CREATE VIEW V AS SELECT g, MAX(CASE WHEN x > 2 THEN 1 ELSE NULL END) FROM T GROUP BY g;""");
// Validated on Postgres: NULL when no row satisfies cond, 1 otherwise.
ccs.stepWeightOne("", """
g | max
---------""");
ccs.stepWeightOne("""
INSERT INTO T VALUES
('a', 1), ('b', 3), ('c', 3), ('c', 5), ('d', 1), ('d', 5), ('e', NULL);
""", """
g | max
---------
a| NULL
b| 1
c| 1
d| 1
e| NULL""");
ccs.visit(this.findLinear(ccs.compiler));
}

@Test
public void issue6590a() {
// MAX(CASE WHEN cond THEN 1 END) — no ELSE clause (equivalent to ELSE NULL)
var ccs = this.getCCS("""
CREATE TABLE T(g VARCHAR, x INT);
CREATE VIEW V AS SELECT g, MAX(CASE WHEN x > 2 THEN 1 END) FROM T GROUP BY g;""");
// Validated on Postgres: NULL when no row satisfies cond, 1 otherwise.
ccs.stepWeightOne("", """
g | max
---------""");
ccs.stepWeightOne("""
INSERT INTO T VALUES
('a', 1), ('b', 3), ('c', 3), ('c', 5), ('d', 1), ('d', 5),
('e', NULL);
""", """
g | max
---------
a| NULL
b| 1
c| 1
d| 1
e| NULL""");
ccs.visit(this.findLinear(ccs.compiler));
}

@Test
public void issue6590b() {
// MAX(CASE...) mixed with SUM in the same aggregate.
// Exercises the post-project index path for untransformed aggregate calls.
var ccs = this.getCCS("""
CREATE TABLE T(g VARCHAR, x INT);
CREATE VIEW V AS SELECT g, MAX(CASE WHEN x > 2 THEN 1 ELSE 0 END), SUM(x) FROM T GROUP BY g;""");
// Validated on Postgres
ccs.stepWeightOne("", """
g | max | sum
--------------""");
ccs.stepWeightOne("""
INSERT INTO T VALUES
('a', 1), ('b', 3), ('c', 3), ('c', 5), ('d', 1), ('d', 5),
('e', NULL);
""", """
g | max | sum
--------------
a| 0| 1
b| 1| 3
c| 1| 8
d| 1| 6
e| 0| NULL""");
ccs.visit(this.findLinear(ccs.compiler));
}

@Test
public void calciteIssue7501() {
this.getCC("""
Expand Down