apache / apache/gravitino

[Bug report] ClickHouse partition expression round-trip broken for day transforms and nested functions

Open
#11,841 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Java
Stars
3.2k
Forks
935
Avg merge
1d 16h
Merged PRs (30d)
298

Description

### Version

main branch

### Describe what's wrong

Two problems in the ClickHouse partition expression handling:

**1. Day transform maps to `toDate()` instead of `toYYYYMMDD()`.**

`toPartitionExpression` outputs `toDate(col)` for the `day` transform. `toDate()` returns a `Date` type, but ClickHouse's integer day-partitioned tables use `toYYYYMMDD()` which returns `UInt32` (e.g. `20230622`). This mismatch means `loadTable()` + `createTable()` does not produce an equivalent table — the partition expression type differs from what ClickHouse actually stores.

**2. Nested partition function calls are not handled.**

`parsePartitionExpression` uses `normalizeIdentifier()` to strip backticks from the inner argument of `toYear()`, `toYYYYMM()`, and `toDate()`. This fails on nested expressions like `toYYYYMM(toDate(dt))` because `normalizeIdentifier("toDate(dt)")` returns `"toDate(dt)"`, which contains parentheses and is not a simple identifier. The function should recursively peel wrapper functions to find the innermost column name.

### Error message and/or stacktrace

For problem 1: no error, but `loadTable` followed by `createTable` produces a table with `PARTITION BY toDate(col)` instead of `PARTITION BY toYYYYMMDD(col)`, meaning the round-trip does not preserve the original partition semantics.

For problem 2: `IllegalArgumentException: Only simple identifier is supported` when loading a table whose partition key contains nested function wrappers.

### How to reproduce

1. Create a ClickHouse table with `PARTITION BY toYYYYMMDD(dt)`.
2. Load it via Gravitino → partition is parsed as `day("dt")`.
3. Create a new table with the same partition transform → Gravitino generates `PARTITION BY toDate(dt)` instead of `toYYYYMMDD(dt)`.

For nested functions:
1. Create a ClickHouse table with `PARTITION BY toYYYYMM(toDate(event_time))`.
2. Load it via Gravitino → `parsePartitionExpression` fails on `toDate(event_time)`.

### Additional context

Both fixes are in `ClickHouseTableSqlUtils`:
- Change `toPartitionExpression` to output `toYYYYMMDD` for the `day` transform.
- Add `extractInnermostField` to recursively unwrap nested date/time functions (with max depth guard).
- Add `TO_DAY_PATTERN` so `toYYYYMMDD(...)` is recognized during parsing.

Contributor guide

Open the contributing guide

Research direction

Start in ClickHouseTableSqlUtils by tracing toPartitionExpression and parsePartitionExpression for day and nested date/time functions. Verify the round trip for toYYYYMMDD(dt) and nested expressions such as toYYYYMM(toDate(event_time)); done means both parse without the simple-identifier error and preserve the original partition semantics.

Written by the indexing model from the issue text.

Assessment

Tech stack
clickhouse, java
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.