ORDER BY on Date not behave correctly

Hi,

I am running on druid 0.11.0.

The results presents here come throw SQL over http.

This first query use TIME_FORMAT on the __time dimension with an order on time DESC

SELECT TIME_FORMAT("__time", ‘yyyy-MM-dd’) AS “date”,

d AS “dimension”,

sum(m) / 3600.0 AS “metric”

FROM matable

WHERE ((CASE WHEN (("__time" ) >= ((CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END)) AND ("__time" ) < ((CASE WHEN 60 = 0 THEN CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END ELSE TIMESTAMPADD(DAY, 60, CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END) END))) THEN true ELSE false END)) AND (d LIKE ‘%VALUE1%’)

GROUP BY TIME_FORMAT("__time", ‘yyyy-MM-dd’), d

ORDER BY TIME_FORMAT("__time", ‘yyyy-MM-dd’) DESC

LIMIT 5000

``

The result is valid

date
dimension
metric
18-09-02
VALUE1
6989.856944
18-09-01
VALUE1
2325.544722

``

This second query use FLOOR to day instead of TIME_FORMAT with an order on time ASC

SELECT CAST(FLOOR("__time" TO DAY) AS DATE) AS “date”,

d AS “dimension”,

sum(m) / 3600.0 AS “metric”

FROM matable

WHERE ((CASE WHEN (("__time" ) >= ((CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END)) AND ("__time" ) < ((CASE WHEN 60 = 0 THEN CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END ELSE TIMESTAMPADD(DAY, 60, CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END) END))) THEN true ELSE false END)) AND (d LIKE ‘%VALUE1%’)

GROUP BY CAST(FLOOR("__time" TO DAY) AS DATE), d

ORDER BY CAST(FLOOR("__time" TO DAY) AS DATE) ASC

LIMIT 5000

``

The result is valid

date
dimension
metric
18-09-01
VALUE1
2325.544722
18-09-02
VALUE1
6989.856944

``

The last one use FLOOR but with order DESC

SELECT CAST(FLOOR("__time" TO DAY) AS DATE) AS “date”,

d AS “dimension”,

sum(m) / 3600.0 AS “metric”

FROM matable

WHERE ((CASE WHEN (("__time" ) >= ((CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END)) AND ("__time" ) < ((CASE WHEN 60 = 0 THEN CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END ELSE TIMESTAMPADD(DAY, 60, CASE WHEN -10 = 0 THEN CURRENT_TIMESTAMP ELSE TIMESTAMPADD(DAY, -10, CURRENT_TIMESTAMP) END) END))) THEN true ELSE false END)) AND (d LIKE ‘%VALUE1%’)

GROUP BY CAST(FLOOR("__time" TO DAY) AS DATE), d

ORDER BY CAST(FLOOR("__time" TO DAY) AS DATE) DESC

LIMIT 5000

``

The result is not good, there is multiple row per day. If we sum the value per date we have the same result as the queries abovs !

date
dimension
metric
18-09-02
VALUE1
2098.042222
18-09-02
VALUE1
1102.634444
18-09-02
VALUE1
1629.099444
18-09-02
VALUE1
2160.080833
18-09-01
VALUE1
448.6094444
18-09-01
VALUE1
630.0213889
18-09-01
VALUE1
793.0719444
18-09-01
VALUE1
453.8419444

``

I also test with different parameters with the FLOOR, week or month, it’s the same issue.

We use a BI tool, so we can’t change the generated query that use the FLOOR.

Is that a bug ?

You might be hitting this bug that was fixed in 0.12.0: https://github.com/apache/incubator-druid/pull/5453

For the query with FLOOR and DESC, what does that query plan into if you prepend "EXPLAIN PLAN FOR "?

Hi,

We have plan to update our druid to 0.12 so will see.

Their the query plan

DruidQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“matable”},“intervals”:{“type”:“intervals”,“intervals”:[“2018-09-08T18:59:29.000Z/2018-11-07T18:59:29.000Z”]},“virtualColumns”:,“filter”:{“type”:“like”,“dimension”:“dimension”,“pattern”:"%VALUE1%",“escape”:null,“extractionFn”:null},“granularity”:{“type”:“all”},“dimensions”:[{“type”:“extraction”,“dimension”:"__time",“outputName”:“d0”,“outputType”:“LONG”,“extractionFn”:{“type”:“cascade”,“extractionFns”:[{“type”:“timeFormat”,“format”:null,“timeZone”:null,“locale”:null,“granularity”:“DAY”,“asMillis”:true},{“type”:“timeFormat”,“format”:null,“timeZone”:null,“locale”:null,“granularity”:“DAY”,“asMillis”:true}]}},{“type”:“default”,“dimension”:“dimension”,“outputName”:“d1”,“outputType”:“STRING”}],“aggregations”:[{“type”:“longSum”,“name”:“a0”,“fieldName”:“metric”,“expression”:null}],“postAggregations”:[{“type”:“expression”,“name”:“p0”,“expression”:"(“a0” / 3600.0)",“ordering”:null}],“having”:null,“limitSpec”:{“type”:“default”,“columns”:[{“dimension”:“d0”,“direction”:“descending”,“dimensionOrder”:{“type”:“numeric”}}],“limit”:500},“context”:{},“descending”:false}], signature=[{d0:LONG, d1:STRING, p0:DOUBLE}])

``

Hi,
we have upgraded to druid 0.12.3 and the issue is fixed.

Thanks