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 2021/05/26 13:05:28 UTC
[GitHub] [druid] koushik-peak opened a new issue #11306: Having clause doesn't work on movingAverage query
koushik-peak opened a new issue #11306:
URL: https://github.com/apache/druid/issues/11306
### Affected Version
0.18.1
### Description
#### Overall info:
- Cluster size: micro-quickstart
- Configurations in use: macbook pro, 2GHZ quad core i5, 16 GB ram
#### Problem:
The `having` clause with moving average query either causes an exception or doesn't seem to have any effect on the output. Below are the queries demonstrating this.
**1. With Having clause:**
`{"queryType":"movingAverage","dataSource":"sample_sales","granularity":{"type":"period","period":"P7D","origin":"2021-01-18T00:00:00Z"},"intervals":["2021-01-25T00:00:00Z/2021-01-31T00:00:00Z"],"dimensions":[{"type":"default","dimension":"product","outputName":"product","outputType":"STRING"}],"aggregations":[{"name":"currentBucketSale","fieldName":"sale","type":"longSum"}],"averagers":[{"name":"twoBucketSale","fieldName":"currentBucketSale","type":"longSum","buckets":2}],"postAveragers":[{"name":"lastBucketSale","type":"arithmetic","fn":"-","fields":[{"type":"fieldAccess","fieldName":"twoBucketSale"},{"type":"fieldAccess","fieldName":"currentBucketSale"}]}],"having":{"type":"greaterThan","aggregation":"currentBucketSale","value":"600"}}`
output: Query works, but filter doesnt work.
```
[{"version":"v1","timestamp":"2021-01-25T00:00:00.000Z","event":{"twoBucketSale":429,"product":"a","lastBucketSale":210,"currentBucketSale":219}},{"version":"v1","timestamp":"2021-01-25T00:00:00.000Z","event":{"twoBucketSale":559,"product":"b","lastBucketSale":280,"currentBucketSale":279}},{"version":"v1","timestamp":"2021-01-25T00:00:00.000Z","event":{"twoBucketSale":689,"product":"c","lastBucketSale":350,"currentBucketSale":339}},{"version":"v1","timestamp":"2021-01-25T00:00:00.000Z","event":{"twoBucketSale":819,"product":"d","lastBucketSale":420,"currentBucketSale":399}}]
```
**2. Having clause with filter object:**
`{"queryType":"movingAverage","dataSource":"sample_sales","granularity":{"type":"period","period":"P7D","origin":"2021-01-18T00:00:00Z"},"intervals":["2021-01-25T00:00:00Z/2021-01-31T00:00:00Z"],"dimensions":[{"type":"default","dimension":"product","outputName":"product","outputType":"STRING"}],"aggregations":[{"name":"currentBucketSale","fieldName":"sale","type":"longSum"}],"averagers":[{"name":"twoBucketSale","fieldName":"currentBucketSale","type":"longSum","buckets":2}],"postAveragers":[{"name":"lastBucketSale","type":"arithmetic","fn":"-","fields":[{"type":"fieldAccess","fieldName":"twoBucketSale"},{"type":"fieldAccess","fieldName":"currentBucketSale"}]}],"having":{"type":"filter","filter":{"type":"bound","dimension":"currentBucketSale","lower":"300"}}}`
output : exception-
```
{
"error" : "Unknown exception",
"errorMessage" : null,
"errorClass" : "java.lang.NullPointerException",
"host" : null
}
```
Broker log:
```
2021-05-26T11:25:55,614 WARN [qtp876695548-121[movingAverage_[sample_sales]_e951e25a-dd91-4dda-91e3-bd5fdc06c66d]] org.apache.druid.server.QueryLifecycle - Exception while processing queryId [e951e25a-dd91-4dda-91e3-bd5fdc06c66d] (java.lang.NullPointerException)
2021-05-26T11:25:55,615 ERROR [qtp876695548-121[movingAverage_[sample_sales]_e951e25a-dd91-4dda-91e3-bd5fdc06c66d]] org.apache.druid.server.QueryResource - Exception handling request: {class=org.apache.druid.server.QueryResource, exceptionType=class java.lang.NullPointerException, exceptionMessage=null, query={"queryType":"movingAverage","dataSource":{"type":"table","name":"sample_sales"},"intervals":{"type":"LegacySegmentSpec","intervals":["2021-01-25T00:00:00.000Z/2021-01-31T00:00:00.000Z"]},"filter":null,"granularity":{"type":"period","period":"P7D","timeZone":"UTC","origin":"2021-01-18T00:00:00.000Z"},"dimensions":[{"type":"default","dimension":"product","outputName":"product","outputType":"STRING"}],"aggregations":[{"type":"longSum","name":"currentBucketSale","fieldName":"sale","expression":null}],"postAggregations":[],"having":{"type":"filter","filter":{"type":"bound","dimension":"currentBucketSale","lower":"300","upper":null,"lowerStrict":false,"upperStrict":false,"extracti
onFn":null,"ordering":{"type":"lexicographic"}},"finalize":true},"averagers":[{"type":"longSum","name":"twoBucketSale","buckets":2,"cycleSize":1,"fieldName":"currentBucketSale"}],"postAveragers":[{"type":"arithmetic","name":"lastBucketSale","fn":"-","fields":[{"type":"fieldAccess","name":null,"fieldName":"twoBucketSale"},{"type":"fieldAccess","name":null,"fieldName":"currentBucketSale"}],"ordering":null}],"limitSpec":{"type":"NoopLimitSpec"},"context":{"queryId":"e951e25a-dd91-4dda-91e3-bd5fdc06c66d"},"descending":false}, peer=0:0:0:0:0:0:0:1} (java.lang.NullPointerException)
```
**3. Wrapped in scan query with filter:**
`{{"queryType":"scan","dataSource":{"type":"query","query":{"queryType":"movingAverage","dataSource":"sample_sales","granularity":{"type":"period","period":"P7D","origin":"2021-01-18T00:00:00Z"},"intervals":["2021-01-25T00:00:00Z/2021-01-31T00:00:00Z"],"dimensions":[{"type":"default","dimension":"product","outputName":"product","outputType":"STRING"}],"aggregations":[{"name":"currentBucketSale","fieldName":"sale","type":"longSum"}],"averagers":[{"name":"twoBucketSale","fieldName":"currentBucketSale","type":"longSum","buckets":2}],"postAveragers":[{"name":"lastBucketSale","type":"arithmetic","fn":"-","fields":[{"type":"fieldAccess","fieldName":"twoBucketSale"},{"type":"fieldAccess","fieldName":"currentBucketSale"}]}]}},"intervals":["2021-01-25T00:00:00Z/2021-01-31T00:00:00Z"],"filter":{"type":"bound","dimension":"saleChangePct","lower":0}}
`
output:
```
{
"error" : "Unsupported operation",
"errorMessage" : "Query type 'movingAverage' does not support returning results as arrays",
"errorClass" : "org.apache.druid.java.util.common.UOE",
"host" : null
}
```
**4. Having clause works with groupBy query**
```
{"queryType":"groupBy","dataSource":{"type":"table","name":"sample_sales"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"granularity":{"type":"all"},"dimensions":[{"type":"default","dimension":"product","outputName":"d0","outputType":"STRING"}],"aggregations":[{"type":"longSum","name":"a0","fieldName":"sale","expression":null}],"having":{"type":"greaterThan","aggregation":"a0","value":"800"}}
```
output (rows with a0 < 800 have been filtered) :
```
[{"version":"v1","timestamp":"-146136543-09-08T08:23:32.096Z","event":{"d0":"b","a0":1085}},{"version":"v1","timestamp":"-146136543-09-08T08:23:32.096Z","event":{"d0":"c","a0":1395}},{"version":"v1","timestamp":"-146136543-09-08T08:23:32.096Z","event":{"d0":"d","a0":1705}}]
```
--
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