SQL API: aggregates don't skip NULL values (COUNT(col) counts them, SUM and AVG throw NPE)
Maintainers usually reply within 2 days
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 20/100
Research direction
Start with AggregateAddCols.java, AggregateFunction.java, and AggregateGetResult.java at the linked lines; trace how NULL values are handled through aggregation and how AVG gets its count. Reproduce the listed queries on the Java platform, including a GROUP BY case, and check the behavior on Spark. Done means COUNT(col), SUM, and AVG match the expected results while COUNT(*) still counts every row.
Written by the indexing model from the issue text.
Description
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:
AggregateAddCols.java:53sets every COUNT column to1, soCOUNT(col)is always the same asCOUNT(*).AggregateFunction.java:118-124picks the type from whichever side isn't null, then still passes the null to the function. For example,Integer::sumis applied to(1, null)and fails on unboxing.AggregateGetResult.java:55-56divides 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.
- Dominant language
- Java
- Stars
- 284
- Forks
- 147
- Avg merge
- 2d 13h
- Merged PRs (30d)
- 13
Getting set up
- Ships a Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from apache/wayang
-
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 2 days
-
JavaReservoirSampleOperator returns the whole input when the dataset size is unknownPossibly taken @sasmith-14 claimed this 7 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
apache/wayang#884 · 3 comments ·
Maintainers usually reply within 2 days
-
Record.compareTo() throws ClassCastException when comparing recordsPossibly taken @harshitsingh070 claimed this 69 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 2 days
-
Unused parameter in function ProjectionDescriptor.createPojoJavaImplementation()Possibly taken @AliRana30 claimed this 77 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
apache/wayang#732 · 3 comments ·
Maintainers usually reply within 2 days
-
DISCLAIMER and some "(incubating)" references are still around after graduationPossibly taken @sasmith-14 claimed this 1 day ago. Open
Difficulty 3/5 1-2 days Newbie friendliness 66/100
apache/wayang#895 · 1 comment · 1 assignee ·
Maintainers usually reply within 2 days
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
jenkins-infra/plugin-modernizer-tool#2070 · 1 comment ·
Maintainers usually reply within 1 day
-
Event search answers 500 when calendars, attendees or organizers holds a null elementMay be free again A pull request for this issue was closed without being merged. Openai-driven-qa bug Minor
Difficulty 2/5 1-3 hours Newbie friendliness 70/100
linagora/twake-calendar-side-service#1615 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
eclipse-tradista/tradista#371 ·
Maintainers usually reply within 1 day
-
critical
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
mars-sim/mars-sim#2199 · 2 comments ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
projeto-ong-amar/projeto-ong-amar-backend#26 ·
Maintainers usually reply within 1 day