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

[Fergus-Boydell]

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:

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:

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():

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.

[Fergus-Boydell]

Update: The fix needs to be Nullable(String), not just String.

CAST(NULL, 'String') still fails with: Cannot convert NULL to a non-nullable type.

ClickHouse requires Nullable(String) to hold NULL values. The correct override should be:

templates.types.string = 'Nullable(String)';

[Fergus-Boydell]

Update 2: Two additional issues found beyond the STRING type problem.

Issue 2: ClickHouse requires Nullable(String), not String

CAST(NULL, 'String') fails with: Cannot convert NULL to a non-nullable type. The fix needs to be:

templates.types.string = 'Nullable(String)';

Issue 3: ClickHouse uses lagInFrame/leadInFrame, not LAG/LEAD

ClickHouse (tested on v25.3.2) does not have standard SQL LAG/LEAD functions. It uses lagInFrame and leadInFrame instead. The adapter needs:

templates.functions.LAG = 'lagInFrame({{ args_concat }})';
templates.functions.LEAD = 'leadInFrame({{ args_concat }})';

Issue 4: Pushdown query mixes aggregates with non-aggregated columns

After patching the above, the generated SQL fails with: Column ridership_report.id is not under aggregate function and not in GROUP BY keys.

The pushdown generates a single flat SELECT that includes ALL model columns (dimensions and measures). The measures use aggregate functions (e.g. quantileExact, multiIf), but the dimensions are not wrapped in aggregates or included in a GROUP BY clause. ClickHouse strictly requires every non-aggregated column to appear in GROUP BY when aggregate functions are present.

This is a query planning issue in cubesql — the pushdown should either:

• Only push down the columns the user actually requested, or
• Properly separate the window function evaluation from the aggregation step

Summary

LAG/LEAD SQL pushdown for ClickHouse requires at least 4 fixes:

1. Type mapping: templates.types.string = 'Nullable(String)' (not STRING)
2. Function name: templates.functions.LAG = 'lagInFrame({{ args_concat }})'
3. Function name: templates.functions.LEAD = 'leadInFrame({{ args_concat }})'
4. Query structure: Pushdown query mixes aggregates with non-aggregated columns without proper GROUP BY

Environment: Cube v1.6.14, ClickHouse v25.3.2, SQL API (PostgreSQL wire protocol)

[Fergus-Boydell]

Correction on Issue 3: lagInFrame/leadInFrame are NOT drop-in replacements for standard SQL lag/lead.

Key differences:

• lag(x) operates over the entire partition and returns NULL when no previous row exists
• lagInFrame(x) operates only within the window frame and returns the type default (0 for numbers, empty string for strings) when out of frame

To emulate standard lag using lagInFrame, the window frame must be explicitly set to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. But since the Cube template only controls the function name (not the OVER clause), this can't be properly fixed from the adapter alone.

Standard SQL lag()/lead() was added in ClickHouse ~v25.6+. On v25.3.2, only lagInFrame/leadInFrame are available.

The proper fix for Cube would be either:

1. Emit lagInFrame/leadInFrame AND force ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for ClickHouse
2. Require ClickHouse v25.6+ which supports native lag()/lead()