You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2020/06/24 22:05:45 UTC
[GitHub] [druid] damnMeddlingKid opened a new issue #10072: Average aggregate does not ignore nulls in numeric column when druid.generic.useDefaultValueForNull=false
damnMeddlingKid opened a new issue #10072:
URL: https://github.com/apache/druid/issues/10072
### Affected Version
Tested in version 0.18.1
### Description
While investigating the correctness of druid's null handling by setting `druid.generic.useDefaultValueForNull=false` I noticed that the `avg` aggregate is not correctly ignoring nulls in its count. The returned average is the same as if the nulls were filled with 0.
### Steps to reproduce
1) set `druid.generic.useDefaultValueForNull=false` in common.runtime.properties, i used the the micro quick start to test this (./bin/start-micro-quickstart)
2) Load the example data from the ingestion spec below, this is an inline datasource. the data looks like this:
```
{"id":1,"duration":null}
{"id":2,"duration":null}
{"id":3,"duration":98}
{"id":4,"duration":6}
{"id":5,"duration":73}
{"id":6,"duration":1}
{"id":7,"duration":56}
{"id":8,"duration":null}
{"id":9,"duration":null}
{"id":10,"duration":null}
```
3) run the following query
```
select avg(duration) as "average", sum(duration) as "sum", count(duration) as "count" from just_duration
```
**Actual result :** {"average": 23, "sum": 234, "count": 5}
**Expected result:** the average should be 234/5 = 46.8
### Example ingestion spec
```
{
"type": "index_parallel",
"spec": {
"ioConfig": {
"type": "index_parallel",
"inputSource": {
"type": "inline",
"data": "{\"id\":1,\"duration\":null}\n{\"id\":2,\"duration\":null}\n{\"id\":3,\"duration\":98}\n{\"id\":4,\"duration\":6}\n{\"id\":5,\"duration\":73}\n{\"id\":6,\"duration\":1}\n{\"id\":7,\"duration\":56}\n{\"id\":8,\"duration\":null}\n{\"id\":9,\"duration\":null}\n{\"id\":10,\"duration\":null}"
},
"inputFormat": {
"type": "json"
}
},
"tuningConfig": {
"type": "index_parallel",
"partitionsSpec": {
"type": "dynamic"
}
},
"dataSchema": {
"dataSource": "just_duration",
"granularitySpec": {
"type": "uniform",
"queryGranularity": "NONE",
"rollup": false,
"segmentGranularity": "YEAR"
},
"timestampSpec": {
"column": "id",
"format": "posix"
},
"dimensionsSpec": {
"dimensions": [
{
"type": "long",
"name": "duration"
}
]
}
}
}
}
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] damnMeddlingKid commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
damnMeddlingKid commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-650485882
took a stab at a fix here https://github.com/damnMeddlingKid/druid/pull/1/files . I check if the column is nullable and then use a filter aggregate factory to perform the count if it is nullable. Not sure if this approach is good, happy to clean up the PR and add tests if this is a good direction.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] damnMeddlingKid commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
damnMeddlingKid commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-655619368
Thanks everyone, i would also like to get opinions on https://github.com/apache/druid/issues/10073
if thats not the expected behaviour i'd like to work on a fix for that too.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] gianm commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
gianm commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-652550179
It's possible both cases (nullable and non-nullable) would be covered by existing tests, but if not, please add enough tests to cover both to CalciteQueryTest.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] gianm commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
gianm commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-652548762
Hi @damnMeddlingKid,
Looks like this is a bug and you got the right fix. The logic is similar to what's in CountSqlAggregator already, so I'd suggest extracting it to a helper function, for example, `CountAggregatorFactory.createFieldCountAggregatorFactory(PlannerContext, RowSignature, VirtualColumnRegistry, RexBuilder, RexNode)`. Then we could call that helper in AvgSqlAggregator as well. It would be similar to how it already calls out to `SumSqlAggregator.createSumAggregatorFactory`.
If you raised a PR doing this it would be appreciated! Please feel free to add me as a reviewer on it or at-mention me in a comment.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] gianm closed issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
gianm closed issue #10072:
URL: https://github.com/apache/druid/issues/10072
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] damnMeddlingKid commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
damnMeddlingKid commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-650343199
I've tracked down the issue to here https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/AvgSqlAggregator.java#L71
we don't filter this count aggregator factory so it's counting all the rows in the column even if they're null.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] gianm edited a comment on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
gianm edited a comment on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-652550179
Ah, it's possible both cases (nullable and non-nullable) would be covered by existing tests, but if not, please add enough tests to cover both to CalciteQueryTest.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] damnMeddlingKid commented on issue #10072: Average aggregate does not ignore nulls in long column when druid.generic.useDefaultValueForNull=false
Posted by GitBox <gi...@apache.org>.
damnMeddlingKid commented on issue #10072:
URL: https://github.com/apache/druid/issues/10072#issuecomment-652560849
Thanks Gian, I'll refactor it and put up a PR.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org