ClickHouse / ClickHouse/ClickHouse

force_primary_key incorrectly rejects a trivial primary-key query when optimize_extract_common_expressions rewrites it

Open
#119,087 0 comments 0 reactions 0 assignees View on GitHub
comp-query-optimizer external potential bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.