Sorting by stringLast aggregator values in groupBy queries

49 views
Skip to first unread message

Ben Buckley

unread,
Mar 29, 2019, 11:24:21 AM3/29/19
to Druid User
Hi,

I wondered if anyone else has experienced any issues sorting by stringLast aggregators in groupBy queries in 0.13.0? 
My sample queries look to be returning unsorted results when attempting to sort by a stringLast aggregator however longLast numeric sorts are working as expected?

This query seems to return results in an unordered (with respect to name) manner. 
It looks like the results might be being sorted by the dimension value (personId) rather than the string metric.
{
  "queryType": "groupBy",
  "dataSource": { "type": "table", "name": "myTable" },
  "intervals": { "type": "intervals", "intervals": [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] },
  "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": {}
} 

In contrast, this query will return results ordered by age as expected.
{
  "queryType": "groupBy",
  "dataSource": { "type": "table", "name": "myTable" },
  "intervals": { "type": "intervals", "intervals": [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] },
  "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": {}
}

Does this look like a bug, or am I specifying something incorrectly in the query?

Cheers,
-Ben 

Ben Buckley

unread,
Mar 29, 2019, 1:32:02 PM3/29/19
to Druid User
Note, I have been able to work around this by referencing this aggregation directly in a postAggregator and then sorting by that (inspiration came from the test case in GitHub).

The following query sorts as expected:

{
  "queryType": "groupBy",
  "dataSource": { "type": "table", "name": "myTable" },
  "intervals": { "type": "intervals", "intervals": [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] },
  "granularity": { "type": "all" },
  "dimensions": [ "personId" ],
  "aggregations": [
    { "type": "stringLast", "name": "name", "fieldName": "name" },
    { "type": "longLast",   "name": "age", "fieldName": "age" }
  ],
  "postAggregations": [
    { "type":"expression", "name":"sortColumn", "expression":"name" }
  ]
  "limitSpec": {
    "type": "default", "limit": 1000,
    "columns" : [{ "dimension":"sortColumn","direction":"ascending","dimensionOrder":"alphanumeric"}]
  },
  "context": {}

Cheers,
-Ben

jon...@imply.io

unread,
Apr 11, 2019, 9:14:22 PM4/11/19
to Druid User
Hi Ben,

I'm not sure what's happening, but this sounds like a bug, could you please file an issue for this?

Thanks,
Jon

Jonathan Wei

unread,
May 17, 2019, 6:26:40 PM5/17/19
to Druid User
Reply all
Reply to author
Forward
0 new messages