cube-js / cube-js/cube

SQl API. Clickhouse as underlying db. Max(timeDimension) creates wrong query: data introspection queries support

Open
#5,170 3 comments 0 reactions 0 assignees View on GitHub
api:sql
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.