cube-js / cube-js/cube

ClickHouse: CAST(NULL, 'STRING') fails — ClickHouseQuery missing string type override

Open
#10,415 3 comments 1 reaction 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## Bug

Queries using the new LAG/LEAD SQL pushdown (added in v1.6.14 via #10407) fail against ClickHouse with:

```
Error: Unknown data type family: STRING. Maybe you meant: ['String','Ring']
```

## Root Cause

`BaseQuery.js` defines the default type template as:

```js
types: {
string: 'STRING',
...
}
```

`ClickHouseQuery.ts` overrides several types (`boolean`, `timestamp`, etc.) in `sqlTemplates()` but **does not override `string`**. ClickHouse type names are case-sensitive and require `String`, not `STRING`.

When LAG/LEAD pushdown generates the full SQL sent to ClickHouse, it includes internal columns:

```sql
CAST(NULL, 'STRING') AS __user, CAST(NULL, 'STRING') AS __cubejoinfield,
```

ClickHouse rejects `STRING` as an unknown type.

## Expected Behavior

The query should execute successfully against ClickHouse.

## Suggested Fix

Add the `string` type override in `ClickHouseQuery.ts` `sqlTemplates()`:

```typescript
templates.types.string = 'String';
```

## Environment

- Cube version: v1.6.14
- Database: ClickHouse
- API: SQL API (PostgreSQL wire protocol)

## Reproduction

Run any query using `LAG()` or `LEAD()` window functions via the Cube SQL API against a ClickHouse data source.

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.