ClickHouse / ClickHouse/ClickHouse

REPLACE PARTITION silently fails when one of the partition keys is of type Bool

Open
#90,582 0 comments 2 reactions 0 assignees View on GitHub
comp-mergetree external unexpected behaviour
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.