DDL Translation error: modify column datatype with DEFAULT NULL

[subkanthi]

  
   CREATE TABLE `test1` (
  `clearing_file_spec_id` int NOT NULL AUTO_INCREMENT,
  `clearing_vendor_id` int NOT NULL,
  `file_mask` varchar(512) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  `expected_arrival_time` time(6) NOT NULL,
  `version_num` smallint unsigned NOT NULL,
  `clearing_file_frequency` varchar(256) COLLATE latin1_general_cs DEFAULT NULL,
  `created_by` mediumint NOT NULL,
  `db_time` datetime(6) NOT NULL,
  PRIMARY KEY (`clearing_file_spec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=110002 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

alter table  test1 add  column `vendor_folder` varchar(128) COLLATE latin1_general_cs NOT NULL after expected_arrival_time  -- worked
alter table  test1 modify  column `vendor_folder` varchar(128) COLLATE latin1_general_cs DEFAULT NULL  -- did not work , was not replicated

ClickHouse:

ALTER TABLE ch_test.test1 ADD COLUMN `vendor_folder` String after expected_arrival_time

[subkanthi]

Clickhouse does not support NOT NULL in alter table add column
https://stackoverflow.com/questions/73487837/how-to-alter-table-x-add-column-y-type-not-null-in-clickhouse

ALTER TABLE ch_test.test1 MODIFY COLUMN `vendor_folder` String DEFAULT NULL

[subkanthi]

MySQL

alter table  test1 add  column `vendor_folder` varchar(128) COLLATE latin1_general_cs NOT NULL after expected_arrival_time  -- worked

Note: The NOT NULL is parsed and so the column vendor_folder is not set to Nullable(String)

CH Translated DDL:

ALTER TABLE ch_test.test1 ADD COLUMN `vendor_folder` String after expected_arrival_time

[subkanthi]

Example: when a table is created with a column as NOT NULL it is translated to ClickHouse as NOT NULL, but the NOT NULL is not supported in ClickHouse in ALTER TABLE ADD COLUMN

MySQL

CREATE TABLE `test1` (
clickhouse-sink-connector-lt_1  |   `clearing_file_spec_id` int NOT NULL AUTO_INCREMENT,
clickhouse-sink-connector-lt_1  |   `clearing_vendor_id` int NOT NULL,
clickhouse-sink-connector-lt_1  |   `file_mask` varchar(512) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
clickhouse-sink-connector-lt_1  |   `expected_arrival_time` time(6) NOT NULL,
clickhouse-sink-connector-lt_1  |   `version_num` smallint unsigned NOT NULL,
clickhouse-sink-connector-lt_1  |   `clearing_file_frequency` varchar(256) COLLATE latin1_general_cs DEFAULT NULL,
clickhouse-sink-connector-lt_1  |   `created_by` mediumint NOT NULL,
clickhouse-sink-connector-lt_1  |   `db_time` datetime(6) NOT NULL,
clickhouse-sink-connector-lt_1  |   PRIMARY KEY (`clearing_file_spec_id`)

CREATE TABLE ch_test.`test1`(`clearing_file_spec_id` Int32 NOT NULL ,`clearing_vendor_id` Int32 NOT NULL ,`file_mask` Nullable(String),`expected_arrival_time` String NOT NULL ,`version_num` Int32 NOT NULL ,`clearing_file_frequency` Nullable(String),`created_by` Int32 NOT NULL ,`db_time` DateTime64(6, 0) NOT NULL ,`_version` UInt64,`is_deleted` UInt8) Engine=ReplacingMergeTree(_version,is_deleted) ORDER BY (`clearing_file_spec_id`)