apache / apache/druid

Having clause doesn't work on movingAverage query

Open
#11,306 0 comments 0 reactions 0 assignees View on GitHub
Area - Querying
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.