Modify column clickhouse (NULL not retained unless specified) vs MySQL

modify_column.sql

create table test_modify(uid String, newcol Nullable(String)) Engine=MergeTree() order by uid;

CREATE TABLE test_modify
(
    `uid` String,
    `newcol` Nullable(String)
)
ENGINE = MergeTree
ORDER BY uid

Query id: 7759480e-b503-401a-a287-06e095e52681

Ok.

0 rows in set. Elapsed: 0.009 sec. 

kanthi :) alter table test_modify modify column newcol Decimal(10,2);

ALTER TABLE test_modify
    MODIFY COLUMN `newcol` Decimal(10, 2)

Query id: 212cca97-0327-42bf-b689-3af97491e7ea

Ok.

0 rows in set. Elapsed: 0.008 sec. 


show create table test_modify;

SHOW CREATE TABLE test_modify

Query id: 2b9436ca-e878-4685-a5e9-661d2c3b99fa

┌─statement─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.test_modify
(
    `uid` String,
    `newcol` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY uid
SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

mysql> show create table add_test;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| add_test | CREATE TABLE `add_test` (
  `col3` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  `col1` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table add_test modify column col3 decimal(10,10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table add_test;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                           |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| add_test | CREATE TABLE `add_test` (
  `col3` decimal(10,10) DEFAULT NULL,
  `col2` int DEFAULT NULL,
  `col1` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)