Alter of nullable columns to not nullable is not supported

[den-crane]

create table XXX (x Nullable(String), y String) engine=MergeTree order by tuple();
insert into XXX (y) values ('a');
select * from XXX
┌─x────┬─y─┐
│ ᴺᵁᴸᴸ │ a │
└──────┴───┘

ALTER TABLE XXX MODIFY COLUMN x String;
DB::Exception: Cannot convert NULL value to non-Nullable type.

ALTER TABLE XXX MODIFY COLUMN x String DEFAULT 'xxx';
DB::Exception: Cannot convert NULL value to non-Nullable type.

alter table XXX update x ='xxx' where 1;
select * from XXX
┌─x───┬─y─┐
│ xxx │ a │
└─────┴───┘

ALTER TABLE XXX MODIFY COLUMN x String;
DB::Exception: Cannot convert NULL value to non-Nullable type

Though,

drop table XXX;
create table XXX (x Nullable(String), y String) engine=MergeTree order by tuple();
ALTER TABLE XXX MODIFY COLUMN x String DEFAULT 'xxx';
Ok.
DESCRIBE TABLE XXX

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ x    │ String │ DEFAULT      │ 'xxx'              │         │                  │                │
│ y    │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

[alexey-milovidov]

Intended behaviour: convert NULL values to default values while ALTERing (?).

[den-crane]

It has different behaviour with different RDMS.

PG does not allow to change to not nullable until column contains NULLs. Probably it possible because PG DDL are transactional and PG able to check and alter in one transaction. So PG makes you to update the column to replace all nulls with some value before alter.

Mysql behaviour:

create table t(A int);
insert into t values(Null);

select * from t;
+------+
| A    |
+------+
| NULL |
+------+

alter table t modify A int not null;
Records: 1  Duplicates: 0  Warnings: 1

select * from t;
+---+
| A |
+---+
| 0 |
+---+

Probably mysql behaviour is OK -- to replace Nulls with column default or type default values.

[alesapin]

Now it works when all values in column is not equal to NULL.

[amosbird]

So PG makes you to update the column to replace all nulls with some value before alter.

We can introduce an extended type with user-defined null defaults, such as NonNullable(int, 42). This type behaves exactly the same as the underlying type but allows for the implicit casting of NULL to the user-defined default value.

For example, executing "alter table t modify A int not null;" will fail if column A contains NULL values. However, using "alter table t modify A NonNullable(int, 0);" will work as expected, conveying the desired intentions.

[AleksaC]

ClickHouse no longer seems to throw this error, nor does it convert NULL to default value:

select version();

SELECT version()

Query id: b1ba1d75-a5a2-4052-b44c-3f5ef081ae6c

┌─version()──┐
│ 23.12.2.59 │
└────────────┘

1 row in set. Elapsed: 0.002 sec.

create or replace table t(
    a Int8,
    b Nullable(Int8)
) engine MergeTree() primary key ();

CREATE OR REPLACE TABLE t
(
    `a` Int8,
    `b` Nullable(Int8)
)
ENGINE = MergeTree
PRIMARY KEY tuple()

Query id: 81f5b2e9-1123-4cb7-bae1-fe398f3b1880

Ok.

0 rows in set. Elapsed: 0.016 sec.

insert into t values (5, NULL), (1, 2);

INSERT INTO t FORMAT Values

Query id: 78e0d7ee-40cf-4879-9cf2-36454df846a7

Ok.

2 rows in set. Elapsed: 0.029 sec.

select * from t;

SELECT *
FROM t

Query id: c2b22715-fa50-4bd3-b7a7-d238958f89d3

┌─a─┬────b─┐
│ 5 │ ᴺᵁᴸᴸ │
│ 1 │    2 │
└───┴──────┘

2 rows in set. Elapsed: 0.005 sec.

alter table t modify column b Int8 default 0;

ALTER TABLE t
    MODIFY COLUMN `b` Int8 DEFAULT 0

Query id: d5329566-90a7-49a9-9f56-a35baeb6cd4e


Elapsed: 0.036 sec.

Received exception from server (version 23.12.2):
Code: 341. DB::Exception: Received from localhost:9000. DB::Exception: Exception happened during execution of mutation 'mutation_2.txt' with part 'all_1_1_0' reason: 'Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION _CAST(b :: 1, 'Int8' :: 2) -> _CAST(b, 'Int8') Int8 : 3': (while reading from part /var/lib/clickhouse/store/d5e/d5ebb810-5319-40c8-a4b1-68c5c4b41ddb/all_1_1_0/ located on disk default of type local): While executing MergeTreeSequentialSource. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 23.12.2.59 (official build))'. This error maybe retryable or not. In case of unretryable error, mutation can be killed with KILL MUTATION query. (UNFINISHED)

select * from t;

SELECT *
FROM t

Query id: e20589f9-6157-4fb4-8da8-dd2017ad3380


Elapsed: 0.006 sec.

Received exception from server (version 23.12.2):
Code: 349. DB::Exception: Received from localhost:9000. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION _CAST(b :: 1, 'Int8' :: 2) -> _CAST(b, 'Int8') Int8 : 3': (while reading from part /var/lib/clickhouse/store/d5e/d5ebb810-5319-40c8-a4b1-68c5c4b41ddb/all_1_1_0/ located on disk default of type local): While executing MergeTreeSelect(pool: ReadPoolInOrder, algorithm: InOrder). (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN)

I also tested in ClickHouse Cloud v23.9 and v24 and the same issue appears except I get the response that alter was successful and when selecting I get some results before getting the error.

Thankfully it seems that the table can be returned to the previous state by killing the mutation which seems to get stuck indefinitely (neither killed nor done, referencing part that doesn't exist in parts_to_do_names) and altering it back to the nullable type.