metabase.query-processor.middleware.reconcile-breakout-and-order-by-bucketing — metabase-core 1.0.0-SNAPSHOT

SQL places restrictions when using a GROUP BY clause (MBQL :breakout) in combination with an ORDER BY clause (MBQL :order-by) -- columns that appear in the ORDER BY must appear in the GROUP BY. When we apply datetime or binning bucketing in a breakout, for example cast(x AS DATE) (MBQL :datetime-field clause), we need to apply the same bucketing to instances of that Field in the order-by clause. In other words:

Bad:

SELECT count(*) FROM table GROUP BY CAST(x AS date) ORDER BY x ASC

(MBQL)

{:source-table 1 :breakout [[:datetime-field [:field-id 1] :day]] :order-by [[:asc [:field-id 1]]]}

Good:

SELECT count(*) FROM table GROUP BY CAST(x AS date) ORDER BY CAST(x AS date) ASC

(MBQL)

{:source-table 1 :breakout [[:datetime-field [:field-id 1] :day]] :order-by [[:asc [:datetime-field [:field-id 1] :day]]]}

The frontend, on the rare occasion it generates a query that explicitly specifies an order-by clause, usually will generate one that directly corresponds to the bad example above. This middleware finds these cases and rewrites the query to look like the good example.

reconcile-breakout-and-order-by-bucketing clj

(reconcile-breakout-and-order-by-bucketing qp)

Replace any unwrapped Field clauses (:field-id, :field-literal, or :fk->) in the order-by clause with corresponding wrapped clauses (:datetime-field or :binning-strategy) used for the same Field in the breakout clause.

{:query {:breakout [[:datetime-field [:field-id 1] :day]] :order-by [[:asc [:field-id 1]]]} -> {:query {:breakout [[:datetime-field [:field-id 1] :day]] :order-by [[:datetime-field [:asc [:field-id 1]] :day]]}