Sorting Breaks When Grouping by Month based on date (creation date) field

Get Help
1

When building a new question in Metabase, I encountered a bug where changing the grouping level to "month" causes the query to break. Here's the sequence of actions leading to the issue:

Table: BigQuery
my_date field in BQ: Date
my_date field in Metabase: creation Date

Base question:

  1. Start by grouping by a date field at the day level and summing a numeric field.
  2. Add a sort section, visualize the results, and confirm everything works correctly.
  3. Add my_date in descending order.
  4. Save the question

Open Again the (already saved) question to have a view on a month level aggregation.

  1. Change the grouping level to month, visualize and the query breaks.
  2. The issue occurs regardless of the sort order (ascending or descending).

The order by statement of the generated sql query is this

ORDER BY
  DATE_TRUNC(
    `dataset.table`.`my_date`,
    month
  ) DESC,
  `my_date` ASC

Here's the full error message:

400 Bad Request
POST https://bigquery....../projects/myproject/queries
{
"code": 400,
"errors": [
{
"domain": "global",
"location": "q",
"locationType": "parameter",
"message": "ORDER BY clause expression references dataset.table.my_date which is neither grouped nor aggregated at [1:268]",
"reason": "invalidQuery"
}
],
"message": "ORDER BY clause expression references dataset.table.my_date which is neither grouped nor aggregated at [1:268]",
"status": "INVALID_ARGUMENT"
}

Here is the two sections of the query builder:
Note that when it is My Date: Day it works perfectly

image
image1272×285 10 KB

2

I think this may be what you're seeing:

You should be able to work around it for now by removing the summarize block and adding it back. Just make sure you choose "month" granularity first instead of allowing default granularity to be set and then changing it. It seems like the issue arises when the granularity is changed (regardless of initial or new granularity).