[SQL] Generalize optimization rule for MAX(CASE WHEN expression THEN 1 END)#6591
Conversation
…1 END) Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
mythical-fred
left a comment
There was a problem hiding this comment.
Generalization is sound.
Semantics (ELSE NULL / no ELSE): With GROUP BY, every materialized group has ≥1 row, so MAX-of-empty-group isn't reachable. Per-row values are 1 (P true) or NULL (P false / P NULL). MAX ignores NULLs, so the result is 1 iff any row has P true, else NULL. The rewrite CASE WHEN COUNT_FILTER(P IS TRUE) > 0 THEN 1 ELSE NULL END matches: count > 0 iff a row has P true. IS TRUE correctly folds NULL predicates into the "no match" bucket. COUNT never returns NULL, so no defensive branch needed for that side.
Nice bonus: the drive-by fix to the post-project input-ref index (groupCount + newAggregates.size() instead of just newAggregates.size()) corrects a latent bug for untransformed aggregate calls in mixed aggregates. issue6590b (MAX + SUM) exercises exactly that path.
Tests: cover empty group, P-true, P-false, P-NULL, and the mixed-aggregate index path. Matches Postgres.
One tiny nit inline — non-blocking.
| return null; | ||
| Long op2Value = getLiteralValue(op2); | ||
| if (op2Value == null) | ||
| if (op2Value == null && !(op2 instanceof RexLiteral)) |
There was a problem hiding this comment.
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.
Fixes #6590
Checklist