AarushShetty346 opened a new pull request, #893:
URL: https://github.com/apache/wayang/pull/893

   Fixes #891
   
   ### What changes were proposed in this pull request?
   
   SQL aggregates now ignore NULL values, as in standard SQL:
   
   - `AggregateAddCols`: `COUNT(col)` adds 0 for a row where its argument is 
null. `COUNT(*)` has no arguments, so it still adds 1. For `AVG`, the column 
now holds `{value, 1}`, or `{null, 0}` when the value is null, so the number of 
non-null values is tracked separately from the row count.
   - `AggregateFunction`: `castAndMap` returns the other side when one side is 
null. `SUM`, `MIN` and `MAX` therefore skip nulls, and if all values are null 
the result stays null. `AVG` merges the sum and the count separately. This also 
removes the `countDone` handling, which updated the shared row count from 
inside the AVG case.
   - `AggregateGetResult`: `AVG` divides the sum by the number of non-null 
values. If there are none, it returns null.
   
   ### How was this patch tested?
   
   I added three tests to `SqlToWayangRelTest`, using a new 
`exampleNullGroups.csv` (six rows in groups `a`, `b`, `c`; group `c` has only a 
NULL):
   
   - `javaAggregatesSkipNulls` runs `COUNT(*)`, `COUNT(col1)`, `SUM`, `AVG`, 
`MIN` and `MAX` without `GROUP BY`. Each aggregate runs as its own query 
because of a separate problem with several aggregates and no `GROUP BY`, which 
I can open an issue for.
   - `javaGroupedAggregatesSkipNulls` and `sparkGroupedAggregatesSkipNulls` run 
`COUNT(col1)`, `SUM` and `AVG` per group. The expected rows are `(a, 1, 1, 
1.0)`, `(b, 2, 8, 4.0)` and `(c, 0, null, null)`.
   
   On main, `COUNT(col1)` returns 6 instead of 3, the grouped Java query throws 
an NPE in `JavaReduceBy`, and the Spark test fails as well. With this change, 
all `wayang-api-sql` tests pass (34 in total, Spark included).


-- 
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]

Reply via email to