ClickHouse / ClickHouse/ClickHouse

Mutation leads to CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN (regardless of validate_mutation_query value)

Open
#92,911 1 comment 0 reactions 0 assignees View on GitHub
comp-mutations 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

https://fiddle.clickhouse.com/6aa52808-6b0d-4f44-9585-0e434eff1791

There's a mutation with `SETTINGS validate_mutation_query = 1`. It starts and leads to mutation errors:

```
Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type
```

It's possible to detect that nullable argument will be assigned to not nullable destination in one of cases before query execution.

### Does it reproduce on the most recent release?

Yes

### How to reproduce

https://fiddle.clickhouse.com/6aa52808-6b0d-4f44-9585-0e434eff1791

```sql
DROP TABLE IF EXISTS warden_a;
CREATE TABLE warden_a
(
`salary` Nullable(String)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO warden_a
VALUES
('1, 2 , 3')
('wrong'),
(NULL);

ALTER TABLE warden_a
ADD COLUMN IF NOT EXISTS `parsed_salary` Array(Int64);
ALTER TABLE
warden_a
UPDATE
`parsed_salary` = multiIf(
salary IS NULL, CAST('[]', 'Array(Int64)'),
CAST(
coalesce(
arrayCompact(
arrayMap(
x -> toInt64OrNull(trimBoth(x)),
splitByString(',', ifNull(salary, ''))
)
),
[]
),
'Array(Int64)'
)
)
WHERE
1
SETTINGS validate_mutation_query = 1;

SELECT sleep(2);
SELECT * FROM system.mutations;
```

### Expected behavior

Mutation does not start. It throws an error in query check phase, not in mutation execution time.

### Error message and/or stacktrace

```
0 all_1_1_0 2025-12-23 14:39:13 Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing \'FUNCTION multiIf(isNull(salary) :: 7, CAST(\'[]\', \'Array(Int64)\') :: 2, CAST(coalesce(arrayCompact(arrayMap(lambda(tuple(x), toInt64OrNull(trimBoth(x))), splitByString(\',\',... :: 6) -> multiIf(isNull(salary), CAST(\'[]\', \'Array(Int64)\'), CAST(coalesce(arrayCompact(arrayMap(lambda(tuple... Array(Int64) : 8\'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 25.12.1.649 (official build)) CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN
```

### Additional context

_No response_

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.