apache / apache/druid

Wrong order when ordering by StringLast agg with GroupBy V2

Open
#7,691 0 comments 0 reactions 0 assignees View on GitHub
Area - Querying Bug
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

### Affected Version

0.13.0+

### Description

A user on the mailing list reported the following issue: https://groups.google.com/d/msg/druid-user/x0xuqSFM794/C8tn3J4eDAAJ

The following data, index task, and queries can be used to reproduce the described issue:

data
```
{"time":"2015-09-12T00:46:58.771Z", "personId":"1111", "name":"ZZZZZ", "age": 30}
{"time":"2015-09-12T01:46:58.771Z", "personId":"1111", "name":"BBBBB", "age": 31}
{"time":"2015-09-12T02:46:58.771Z", "personId":"2222", "name":"YYYYY", "age": 20}
{"time":"2015-09-12T03:46:58.771Z", "personId":"2222", "name":"AAAAA", "age": 21}
```

task
```
{
"type" : "index",
"spec" : {
"dataSchema" : {
"dataSource" : "stringlaster",
"parser" : {
"type" : "string",
"parseSpec" : {
"format" : "json",
"dimensionsSpec" : {
"dimensions" : [
"time",
"personId",
"name"
]
},
"timestampSpec" : {
"format" : "auto",
"column" : "time"
}
}
},
"metricsSpec" : [
{ "type" : "longSum", "name" : "age", "fieldName" : "age" }
],
"granularitySpec" : {
"type" : "uniform",
"segmentGranularity" : "MONTH",
"queryGranularity" : "none",
"intervals" : ["2015-09-01/2015-10-01"],
"rollup" : false
}
},
"ioConfig" : {
"type" : "index",
"firehose" : {
"type" : "local",
"baseDir" : "quickstart/",
"filter" : "stringlast_data.json"
},
"appendToExisting" : false
},
"tuningConfig" : {
"type" : "index",
"targetPartitionSize" : null,
"maxRowsInMemory" : 25000,
"forceGuaranteedRollup" : true,
"numShards": 2
}
}
}
```

query, ordering by string last (wrong results):
```
{
"queryType": "groupBy",
"dataSource": { "type": "table", "name": "stringlaster" },
"intervals": { "type": "intervals", "intervals": [ "2015-09-01/2015-10-01" ] },
"granularity": { "type": "all" },
"dimensions": [ "personId" ],
"aggregations": [
{ "type": "stringLast", "name": "name", "fieldName": "name" },
{ "type": "longLast", "name": "age", "fieldName": "age" }
],
"limitSpec": {
"type": "default", "limit": 1000,
"columns" : [{ "dimension":"name","direction":"ascending","dimensionOrder":"alphanumeric"}]
},
"context": {}
}
```

wrong results for the above query:
```
[
{
"version": "v1",
"timestamp": "2015-09-01T00:00:00.000Z",
"event": {
"name": "BBBBB",
"personId": "1111",
"age": 31
}
},
{
"version": "v1",
"timestamp": "2015-09-01T00:00:00.000Z",
"event": {
"name": "AAAAA",
"personId": "2222",
"age": 21
}
}
]
```

query ordering by long last (correct results)
```
{
"queryType": "groupBy",
"dataSource": { "type": "table", "name": "stringlaster" },
"intervals": { "type": "intervals", "intervals": [ "2015-09-01/2015-10-01" ] },
"granularity": { "type": "all" },
"dimensions": [ "personId" ],
"aggregations": [
{ "type": "stringLast", "name": "name", "fieldName": "name" },
{ "type": "longLast", "name": "age", "fieldName": "age" }
],
"limitSpec": {
"type": "default", "limit": 1000,
"columns" : [{ "dimension":"age","direction":"ascending","dimensionOrder":"alphanumeric"}]
},
"context": {}
}
```

right results for above query:
```
[
{
"version": "v1",
"timestamp": "2015-09-01T00:00:00.000Z",
"event": {
"name": "AAAAA",
"personId": "2222",
"age": 21
}
},
{
"version": "v1",
"timestamp": "2015-09-01T00:00:00.000Z",
"event": {
"name": "BBBBB",
"personId": "1111",
"age": 31
}
}
]
```

Contributor guide

Open the contributing guide

Research direction

Use the supplied indexing task and GroupBy V2 queries to reproduce the StringLast ordering result, then compare it with the LongLast ordering case. Done means ordering by the stringLast aggregation returns the expected personId order and values shown in the issue.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
analytics, databases
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.