SQl API. Clickhouse as underlying db. Max(timeDimension) creates wrong query: data introspection queries support
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Failed SQL**
`select max(InvoiceDateTime) from sales`
**Version:**
0.30.28
**Additional context**
We have cube **'sales'** with underlying clickhouse table. It contains **InvoiceDateTime** column.
Dimension is configured like below
```
invoiceDatetime: {
title: `Date/time`,
sql: `${CUBE}."InvoiceDateTime"`,
type: `time`
}
```
Selecting **max()** on this dimension via sql-api results in `2022-08-01 00:00:00.000000` (start of the month)
Query, that goes to clickhouse
```
SELECT
toDateTime(toStartOfMonth(toTimeZone(toDateTime(`sales`."InvoiceDateTime"), 'UTC'), 'UTC'), 'UTC') `sales__invoice_datetime_month`
FROM
sales AS `sales` GROUP BY `sales__invoice_datetime_month` ORDER BY `sales__invoice_datetime_month` ASC LIMIT 10000 FORMAT JSON
```
On the other hand, query below returns correct result, but it seems to be just workaround(
```
select InvoiceDateTime from sales
order by InvoiceDateTime desc
limit 1;
```
Can someone shed a light why sql-api tries to apply **toStartOfMonth** while selecting max(timeDimension) query? Is it a bug?
Contributor guide
Assessment
This issue has not been assessed yet.