Waterfall Clickhouse Query Fix for DateTime on X-Axis?

[christianluis]

Superset 3.0.2
clickhouse-connect 0.6.23 Clickhouse 23.11.3.23

How to reproduce the bug

Using waterfall diagram with DateTime on X-Axis throws error:

Error:

Code: 215. DB::Exception: Column `Posting_Date` is not under aggregate function and not in GROUP BY. Have columns: ['sum(metric)','toStartOfQuarter(toDateTime(Posting_Date))']: While processing Posting_Date ASC. (NOT_AN_AGGREGATE) (version 23.11.3.23 (official build))

Generated query (throwing error):

SELECT toStartOfQuarter(toDateTime(`Posting_Date`)) AS `Posting_Date_87e8a1`,
       sum(`metric`) AS `Metric_216ab4`
FROM `db`.`table`
GROUP BY toStartOfQuarter(toDateTime(`Posting_Date`))
ORDER BY Posting_Date ASC
LIMIT 10000;

If the generated query would look like this, it would work:

SELECT toStartOfQuarter(toDateTime(`Posting_Date`)) AS `Posting_Date_87e8a1`,
       sum(`metric`) AS `Metric_216ab4`
FROM `db`.`table`
GROUP BY toStartOfQuarter(toDateTime(`Posting_Date`))
ORDER BY toStartOfQuarter(toDateTime(`Posting_Date`)) ASC -- <-HERE
LIMIT 10000;

[christianluis]

Here is a workaround:

• Delete Time grain within the interface
• Define Time grain via custom SQL in your x-axis

[clayheaton]

I've added a column that has the timeframe I want (weekly) as a string that will order properly ('2024-01', '2024-02', etc) but it is a less than ideal workaround.