ClickHouse / ClickHouse/ClickHouse
force_primary_key incorrectly rejects a trivial primary-key query when optimize_extract_common_expressions rewrites it
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Company or project name
_No response_
### Describe what's wrong
`optimize_extract_common_expressions` (on by default) can rewrite a `WHERE`/`PREWHERE` filter
of the form `(A AND X) OR A` down to the logically equivalent `A`. When doing so, it wraps the
result in an internal cast back to the original expression's type if the simplification changed
it (e.g. the original was `Nullable(UInt8)` because `X` touches a `Nullable` column, while `A`
alone is not nullable). That cast makes primary-key analysis (`KeyCondition`) give up entirely
on the expression underneath it, so the primary key doesn't get used at all — and with
`force_primary_key = 1`, the query is then rejected with `INDEX_NOT_USED`, even though the
condition is trivially just a plain equality on the primary key.
Reproducer:
### Does it reproduce on the most recent release?
Yes
### How to reproduce
- ClickHouse server, default settings, `optimize_extract_common_expressions = 1` (the default)
- `CREATE TABLE`:
```sql
CREATE TABLE t_extract_common_pk
(
k UInt32,
n Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY k
SETTINGS index_granularity = 1;
INSERT INTO t_extract_common_pk SELECT number, number FROM numbers(100);
```
- Query:
```sql
SELECT count() FROM t_extract_common_pk WHERE (k = 1 AND n = 1) OR k = 1
SETTINGS force_primary_key = 1;
```
### Expected behavior
The query succeeds. `(k = 1 AND n = 1) OR k = 1` is logically just `k = 1`, a trivial
primary-key lookup — the primary key should be used, and `EXPLAIN indexes = 1` should show real
pruning.
### Error message and/or stacktrace
```
Code: 277. DB::Exception: Primary key (k) is not used and setting 'force_primary_key' is set. (INDEX_NOT_USED)
```
Confirming with `EXPLAIN indexes = 1` (dropping `force_primary_key` so it doesn't throw) shows
the primary key is not analyzed at all:
```
PrimaryKey
Condition: true
Parts: 1/1
Granules: 12/12
```
Setting `optimize_extract_common_expressions = 0` makes the exact same query succeed and use
the primary key normally, which is what pointed at this setting as the cause.
### Related issues and pull requests
_No response_
### Additional context
Root cause and fix: https://github.com/ClickHouse/ClickHouse/pull/119083 — `KeyCondition`'s `isTrivialCast` helper (which strips
semantically-harmless cast wrappers before primary-key analysis) already recognizes the
analyzer's internal `_CAST` function name (fixed by #105291, for a different symptom), but
still doesn't recognize a cast that only widens a non-nullable type to `Nullable` of the same
type — which is exactly the shape of cast this optimization inserts.
Contributor guide
Research direction
Start by reproducing the SQL example with force_primary_key and then run EXPLAIN indexes = 1 with optimize_extract_common_expressions enabled and disabled. Read KeyCondition::isTrivialCast and the referenced pull request #119083; done means the optimized query succeeds and EXPLAIN shows primary-key pruning without weakening force_primary_key.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 30/100