ClickHouse / ClickHouse/ClickHouse
REPLACE PARTITION silently fails when one of the partition keys is of type Bool
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Company or project name
CloudQuery
### Describe the unexpected behaviour
When using `REPLACE PARTITION` on two tables that have a Bool-type column as part of the partition key, the operation succeeds but actually does not do a replacement. The target table's data stays the same as before.
### Which ClickHouse versions are affected?
Tested with ClickHouse Server 25.6.13 and 25.10.2, both show this behavior.
### How to reproduce
Using 25.10.2.
### 1. Create tables
```sql
CREATE TABLE target_table
(
id UInt32,
country String,
active Bool
)
ENGINE = MergeTree()
PARTITION BY (country, active)
ORDER BY id;
CREATE TABLE source_table
(
id UInt32,
country String,
active Bool
)
ENGINE = MergeTree()
PARTITION BY (country, active)
ORDER BY id;
```
### 2. Insert initial data
Old data in `target_table`:
```sql
INSERT INTO target_table VALUES
(1, 'US', 1),
(2, 'US', 1),
(3, 'UK', 0);
```
New data in `source_table`:
```sql
INSERT INTO source_table VALUES
(10, 'US', 1),
(20, 'US', 1);
```
### 3a. Verify initial target contents
```
SELECT *, _partition_id
FROM target_table
ORDER BY id, country, active;
```
Should see:
```
┌─id─┬─country─┬─active─┬─_partition_id────────────────────┐
1. │ 1 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
2. │ 2 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
3. │ 3 │ UK │ false │ 4dabae32786d92b48c9ba08ec1d938c9 │
└────┴─────────┴────────┴──────────────────────────────────┘
```
### 3b. Verify initial source contents
```sql
SELECT *, _partition_id
FROM source_table
ORDER BY id, country, active;
```
Should see:
```
┌─id─┬─country─┬─active─┬─_partition_id────────────────────┐
1. │ 10 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
2. │ 20 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
└────┴─────────┴────────┴──────────────────────────────────┘
```
### 4. Replace the (country='US', active=true) partition
```sql
ALTER TABLE target_table
REPLACE PARTITION ('US', true)
FROM source_table;
```
This should remove the old US/true partition and copy it from `source_table`.
### 5. Verify replacement
```sql
SELECT *, _partition_id
FROM target_table
ORDER BY id, country, active;
```
Observed output:
```
┌─id─┬─country─┬─active─┬─_partition_id────────────────────┐
1. │ 1 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
2. │ 2 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
3. │ 3 │ UK │ false │ 4dabae32786d92b48c9ba08ec1d938c9 │
└────┴─────────┴────────┴──────────────────────────────────┘
```
### Expected behavior
Expected result is that the partition would be replaced in `target_table`, and should look like this:
```
┌─id─┬─country─┬─active─┬─_partition_id────────────────────┐
1. │ 3 │ UK │ false │ 4dabae32786d92b48c9ba08ec1d938c9 │
2. │ 10 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
3. │ 20 │ US │ true │ 63156e9962354cc7f4628e34086f86a3 │
└────┴─────────┴────────┴──────────────────────────────────┘
```
### Error message and/or stacktrace
None
### Additional context
I also tried using `1` instead of `true` / `false` but the result is the same.
Changing the partition key to use: `toUInt8()` seems to work as a workaround.
Contributor guide
Assessment
This issue has not been assessed yet.