[GitHub] [arrow-datafusion] alamb commented on issue #2067: Support `sum0` as a built-in agg function

[GitBox <gi...@apache.org>]

alamb commented on issue #2067:
URL: https://github.com/apache/arrow-datafusion/issues/2067#issuecomment-1076759264


   Hi @liukun4515  I think you can get the same effect as `sum0` using a `CASE` statement, as shown below. Given the very minor difference between `sum` and `sum0` it seems like it is not a great idea to add it as a built in to DataFusion from my perspective. 
   
   ```sql
   CASE 
     WHEN sum(x) IS NULL 
       THEN 0 
       ELSE sum(x) 
     END as sum0
   ```
   
   A more full featured example:
   
   ```sql
   DataFusion CLI v7.0.0
   ❯ create table foo as select * from (values (1), (null), (null)) as sq;
   0 rows in set. Query took 0.044 seconds.
   
   ❯ select * from foo;
   +---------+
   | column1 |
   +---------+
   | 1       |
   |         |
   |         |
   +---------+
   3 rows in set. Query took 0.002 seconds.
   
   -- Results when there are only nulls
   ❯ select case when sum(column1) IS NULL THEN 0 ELSE sum(column1) END as sum0 from foo where column1 IS NULL;
   +------+
   | sum0 |
   +------+
   | 0    |
   +------+
   1 row in set. Query took 0.005 seconds.
   
   -- Results when there are nulls and non-null
   ❯ select case when sum(column1) IS NULL THEN 0 ELSE sum(column1) END as sum0 from foo;
   +------+
   | sum0 |
   +------+
   | 1    |
   +------+
   1 row in set. Query took 0.005 seconds.


-- 
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: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org