Having clause doesn't work on movingAverage query
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### 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,"extractionFn":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}}]
```
Contributor guide
Research direction
Start by reproducing the movingAverage queries from the issue on Druid 0.18.1, then compare their having behavior with the working groupBy query. Trace the movingAverage query path and its handling of greaterThan and filter having clauses; done means valid having clauses filter rows without being ignored or causing a NullPointerException.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100