songwdfu opened a new issue, #16055:
URL: https://github.com/apache/pinot/issues/16055
Currently in Pinot, SUM0 is actually implemented using SUM.
Current PinotAggregateReduceFunctionsRule + AggregateCaseToFilterRule
results in the following behavior for queries like these:
**Query A:**
```sql
SUM(CASE WHEN x=’foo’ THEN cnt ELSE 0 END)
```
is rewritten to
```sql
CASE WHEN COUNT(*)=0 THEN NULL ELSE SUM(cnt) FILTER (x='foo') END
```
With behavior of
```sql
empty input => NULL
no match => NULL
```
This rewrite is incorrect, the correct behavior should be
```sql
empty input => NULL
no match => 0
```
If we wanna fix this with minimal changes, a solution could be to not match
for this case (A2) in AggregateCaseToFilterRule.
Also we've been discussing not to reduce SUM to SUM0 at all. If we do that
we should disable both A2 and B cases in AggregateCaseToFilterRule
```
// A1: AGG(CASE WHEN x = 'foo' THEN expr END)
// => AGG(expr) FILTER (x = 'foo')
// A2: SUM0(CASE WHEN x = 'foo' THEN cnt ELSE 0 END)
// => SUM0(cnt) FILTER (x = 'foo')
// B: SUM0(CASE WHEN x = 'foo' THEN 1 ELSE 0 END)
// => COUNT() FILTER (x = 'foo')
// C: COUNT(CASE WHEN x = 'foo' THEN 'dummy' END)
// => COUNT() FILTER (x = 'foo')
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
