Picking a date column for sorting results in "day" granularity sort order

[tlrobinson]

When selecting a date column in the sort picker we incorrectly include "day" as the unit, causing rows to be sorted by day but sometimes not sorted correctly within each day.

Note that clicking a table header doesn't cause the unit to be included, so it sorts correctly. This can be used as a workaround until we fix this issue.

[agilliland]

@tlrobinson can you explain a bit more how you are reproducing this? I'm not seeing this happen.

[tlrobinson]

Incorrect, when using the sort picker:

Correct, when clicking a column header:

[agilliland]

So, this is ultimately an issue on the backend where running a :rows query with a simple order-by clause on a datetime field is incorrectly forcing that field representation to be grouped by day.

https://github.com/metabase/metabase/blame/master/src/metabase/query_processor/resolve.clj#L106

For example when I run the above query I see the following, which is not the correct SQL because the order by clause should not be casting the created_at column to a date:

SELECT "PUBLIC"."ORDERS"."USER_ID" AS "USER_ID", "PUBLIC"."ORDERS"."TOTAL" AS "TOTAL", "PUBLIC"."ORDERS"."TAX" AS "TAX", "PUBLIC"."ORDERS"."SUBTOTAL" AS "SUBTOTAL", "PUBLIC"."ORDERS"."PRODUCT_ID" AS "PRODUCT_ID", "PUBLIC"."ORDERS"."ID" AS "ID", "PUBLIC"."ORDERS"."CREATED_AT" AS "CREATED_AT"
FROM "PUBLIC"."ORDERS"
ORDER BY CAST("PUBLIC"."ORDERS"."CREATED_AT" AS DATE) ASC
LIMIT 2000

[tlrobinson]

@agilliland So I think we agreed the correct fix is:

1. ensure the frontend uses a bare field-id rather than datetime_field with a granularity
2. ensure the backend doesn't group by day when sorting by a datetime column without a datetime_field and granularity

I can handle 1, do you or @camsaul want to take 2?

[salsakran]

@camsaul raw rows should definitely not cast the timestamp to a date. This is incorrect, and should be fixed.

We can and should default to timezone when charting aggregates by a timestamp, but in this case it is incorrect.

[camsaul]

Ok @tlrobinson part 2 is merged so I'm going to assign this back to you so you can tackle part 1.

[salsakran]

Re-opening as the fix for this wasn't correct.

The resulting behavior is incorrect and a hard blocker on 0.17.1

[camsaul]

Fixed by #2586