AarushShetty346 opened a new issue, #891: URL: https://github.com/apache/wayang/issues/891
**Aggregates in the SQL API treat NULL as a value. `COUNT(col)` counts NULL rows, and `SUM` / `AVG` throw a `NullPointerException` when a column has a NULL.** In SQL, every aggregate except `COUNT(*)` ignores NULL values. There are three spots where this goes wrong: 1. [`AggregateAddCols.java:53`](https://github.com/apache/wayang/blob/713faa7d8bf4726536f6b0920ebb73053160274c/wayang-api/wayang-api-sql/src/main/java/org/apache/wayang/api/sql/calcite/converter/functions/AggregateAddCols.java#L53) sets every COUNT column to `1`, so `COUNT(col)` is always the same as `COUNT(*)`. 2. [`AggregateFunction.java:118-124`](https://github.com/apache/wayang/blob/713faa7d8bf4726536f6b0920ebb73053160274c/wayang-api/wayang-api-sql/src/main/java/org/apache/wayang/api/sql/calcite/converter/functions/AggregateFunction.java#L118-L124) picks the type from whichever side isn't null, then still passes the null to the function. For example, `Integer::sum` is applied to `(1, null)` and fails on unboxing. 3. [`AggregateGetResult.java:55-56`](https://github.com/apache/wayang/blob/713faa7d8bf4726536f6b0920ebb73053160274c/wayang-api/wayang-api-sql/src/main/java/org/apache/wayang/api/sql/calcite/converter/functions/AggregateGetResult.java#L55-L56) divides the AVG sum by the row count, not by the number of non-null values. AVG would therefore be wrong even without the NPE. ### To reproduce ``` col1:int,col2:string 1;a ;b 3;c ``` On the Java platform: | query | result | expected | |---|---|---| | `SELECT COUNT(*) FROM fs.exampleNull` | 3 | 3 | | `SELECT COUNT(col1) FROM fs.exampleNull` | 3 | 2 | | `SELECT SUM(col1) FROM fs.exampleNull` | NPE | 4 | | `SELECT AVG(col1) FROM fs.exampleNull` | NPE | 2.0 | ``` java.lang.NullPointerException at org.apache.wayang.api.sql.calcite.converter.functions.AggregateFunction.castAndMap(AggregateFunction.java:127) at org.apache.wayang.api.sql.calcite.converter.functions.AggregateFunction.apply(AggregateFunction.java:58) ``` The same thing happens with `GROUP BY` when a group has both NULL and non-NULL values. It fails in `JavaReduceBy`, and in Spark too. Tested on main at 713faa7. I have a fix with tests and will open a PR for it. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
