apache / apache/druid

GroupBy query gives different response when changing columns in the limitSpec.

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

Description

I have a question regarding an Apache Druid incubating query.

I have a simple group by to select the number of calls per operator. See here my query:

{
"queryType": "groupBy",
"dataSource": "ivr-calls",
"intervals": [
"2019-12-06T00:00:00.000Z/2019-12-07T00:00:00.000Z"
],
"dimensions": [
{
"type": "lookup",
"dimension": "operator_id",
"outputName": "value",
"name": "ivr_operator",
"replaceMissingValueWith": "Unknown"
},
{
"type": "default",
"dimension": "operator_id",
"outputType": "long",
"outputName": "id"
}
],
"granularity": "all",
"aggregations": [
{
"type": "longSum",
"name": "calls",
"fieldName": "calls"
}
],
"limitSpec": {
"type": "default",
"limit": 999999,
"columns": [
{
"dimension": "value",
"direction": "ascending",
"dimensionOrder": "numeric"
}
]
}
}

In this query I order the result by the "value" dimension, I receive 218 results.
I noticed that some of the records are duplicate. (I see some operators two times in my resultset). This is strange because in my experience all dimensions which you select are also used for grouping by. So, they should be unique.

If I add an order by to the "id" dimension, I receive 183 results (which is expected):

"columns": [
{
"dimension": "value",
"direction": "ascending",
"dimensionOrder": "numeric"
},
{
"dimension": "id",
"direction": "ascending",
"dimensionOrder": "numeric"
}
]

The documentation tells me nothing about this strange behavior (https://druid.apache.org/docs/latest/querying/limitspec.html).

My previous experience with druid is that the order by is just "ordering".

I am running druid version 0.15.0-incubating-iap9.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the supplied groupBy query against the 0.15.0-incubating-iap9 behavior, comparing the limitSpec ordered only by value with the version also ordered by id. Read the linked limitSpec documentation and inspect how the dimensions and limitSpec columns affect grouping and result uniqueness. Done means the differing result counts are explained and the behavior or documentation is corrected.

Written by the indexing model from the issue text.

Assessment

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