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:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
