ClickHouse / ClickHouse/ClickHouse
Mutation leads to CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN (regardless of validate_mutation_query value)
- 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
Assessment
This issue has not been assessed yet.