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