Values of the generated columns in MySQL are not transfered to the ClickHouse table

[Selfeer]

If we create a table in MySQL that has a generated column it is created in ClickHouse but if in MySQL we insert the data into that table it is not transferred to ClickHouse table.

For example,

CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, 
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
  email VARCHAR(100) NOT NULL);

INSERT INTO test_table (id, first_name, last_name, email) VALUES (1, "Test", "Name", "test@gmail.com");

SELECT * FROM test_table;

+----+------------+---------------+---------------------+------------------------------+
| id | first_name | last_name |    fullname        |        email                     |
+----+------------+---------------+---------------------+------------------------------+
|  1 | Test      | Name           |   Test Name     |    test@gmail.com        |
+----+------------+---------------+---------------------+------------------------------+
1 row in set (0.00 sec)

If after inserting the value we check the table in ClickHouse we see that it's empty

SELECT *
FROM test_table;

Query id: e8c78e7b-dea9-49aa-a735-1c4c59f6990b

Ok.

0 rows in set. Elapsed: 0.003 sec.

SHOW CREATE TABLE test_table;

CREATE TABLE test.test_table
(
    `id` Int32,
    `first_name` String,
    `last_name` String,
    `fullname` Nullable(String) ALIAS concat(first_name, ' ', last_name),
    `email` String,
    `_version` UInt64,
    `is_deleted` UInt8
)
ENGINE = ReplacingMergeTree(_version, is_deleted)
ORDER BY id
SETTINGS index_granularity = 8192

The build used: altinityinfra/clickhouse-sink-connector:443-14a14ba5c18fb3bc9f57c48d9a5079d4d6fe15e8-lt
ClickHouse Version: 23.11.1.2711
MySQL Version: 8.2.0

The error in logs

clickhouse-sink-connector-lt_1  | 4399415 2024-02-08 16:24:29.437 [Sink Connector thread-pool-1] INFO com.altinity.clickhouse.sink.connector.db.DBMetadata  - ResultSetcom.clickhouse.jdbc.ClickHouseResultSet@4464425
clickhouse-sink-connector-lt_1  | 4399416 2024-02-08 16:24:29.438 [Sink Connector thread-pool-1] INFO com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor  - *** QUERY***insert into `test_table`(`id`,`first_name`,`last_name`,`fullname`,`email`,`_version`,`is_deleted`) select `id`,`first_name`,`last_name`,`fullname`,`email`,`_version`,`is_deleted` from input('`id` Int32,`first_name` String,`last_name` String,`fullname` Nullable(String),`email` String,`_version` UInt64,`is_deleted` UInt8')
clickhouse-sink-connector-lt_1  | 4399425 2024-02-08 16:24:29.447 [Sink Connector thread-pool-1] ERROR com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor  - ******* ERROR inserting Batch *****************
clickhouse-sink-connector-lt_1  | java.sql.BatchUpdateException: Code: 16. DB::Exception: No such column fullname in table test.test_table (c70a2180-ac8f-44ba-bf11-13cc6729b40a). (NO_SUCH_COLUMN_IN_TABLE) (version 23.11.1.2711 (official build))
clickhouse-sink-connector-lt_1  | , server ClickHouseNode [uri=http://clickhouse:8123/test, options={custom_settings=allow_experimental_object_type=1,client_name=Sink Connector Lightweight}]@-1682470461
clickhouse-sink-connector-lt_1  | 	at com.clickhouse.jdbc.SqlExceptionUtils.batchUpdateError(SqlExceptionUtils.java:107)
clickhouse-sink-connector-lt_1  | 	at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.executeAny(InputBasedPreparedStatement.java:154)
clickhouse-sink-connector-lt_1  | 	at com.clickhouse.jdbc.internal.AbstractPreparedStatement.executeLargeBatch(AbstractPreparedStatement.java:85)
clickhouse-sink-connector-lt_1  | 	at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.executeBatch(ClickHouseStatementImpl.java:752)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.lambda$executePreparedStatement$0(PreparedStatementExecutor.java:153)
clickhouse-sink-connector-lt_1  | 	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.executePreparedStatement(PreparedStatementExecutor.java:114)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.addToPreparedStatementBatch(PreparedStatementExecutor.java:85)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.flushRecordsToClickHouse(ClickHouseBatchRunnable.java:273)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.processRecordsByTopic(ClickHouseBatchRunnable.java:239)
clickhouse-sink-connector-lt_1  | 	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.run(ClickHouseBatchRunnable.java:141)
clickhouse-sink-connector-lt_1  | 	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
clickhouse-sink-connector-lt_1  | 	at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
clickhouse-sink-connector-lt_1  | 	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
clickhouse-sink-connector-lt_1  | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
clickhouse-sink-connector-lt_1  | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
clickhouse-sink-connector-lt_1  | 	at java.base/java.lang.Thread.run(Thread.java:833)
clickhouse-sink-connector-lt_1  | 4399426 2024-02-08 16:24:29.448 [Sink Connector thread-pool-1] ERROR com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor  - **** ERROR: executing prepared statement
clickhouse-sink-connector-lt_1  | 4399426 2024-02-08 16:24:29.448 [Sink Connector thread-pool-1] ERROR com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable  - Error processing records for topic: embeddedconnector.test.test_table

[Selfeer]

Related to: #429

[aadant]

Good catch. A fullname is generated and virtual by default, so they are not transmitted in the binary log and should not be inserted but only calculated in CH.

Please note that a generated column can be stored (currently we do not support this, the equivalent in CH would be MATERIALIZED, not ALIAS).

see also https://bugs.mysql.com/bug.php?id=101978

A difference with MySQL is that the ALIAS or MATERIALIZED columns are not visible by default in CH

see also ClickHouse/ClickHouse#19659

[subkanthi]

JDBC metadata doesnt have information if the column is Alias, have to try other ways to identify if the column is ALIAS ```
select * from information_schema.columns where table_name='contacts' and column_name='fullname';

SELECT *
FROM information_schema.columns
WHERE (table_name = 'contacts') AND (column_name = 'fullname')

Query id: 9d7d9549-e308-40f2-968e-46e19d457d3c

┌─table_catalog─┬─table_schema─┬─table_name─┬─TABLE_SCHEMA─┬─TABLE_NAME─┬─column_name─┬─ordinal_position─┬─column_default─────────────────────┬─is_nullable─┬─data_type────────┬─character_maximum_length─┬─character_octet_length─┬─numeric_precision─┬─numeric_precision_radix─┬─numeric_scale─┬─datetime_precision─┬─character_set_catalog─┬─character_set_schema─┬─character_set_name─┬─collation_catalog─┬─collation_schema─┬─collation_name─┬─domain_catalog─┬─domain_schema─┬─domain_name─┬─column_comment─┬─column_type──────┐
│ employees │ employees │ contacts │ employees │ contacts │ fullname │ 4 │ concat(first_name, ' ', last_name) │ 1 │ Nullable(String) │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ │ Nullable(String) │
└───────────────┴──────────────┴────────────┴──────────────┴────────────┴─────────────┴──────────────────┴────────────────────────────────────┴─────────────┴──────────────────┴──────────────────────────┴────────────────────────┴───────────────────┴─────────────────────────┴───────────────┴────────────────────┴───────────────────────┴──────────────────────┴────────────────────┴───────────────────┴──────────────────┴────────────────┴────────────────┴───────────────┴─────────────┴────────────────┴──────────────────┘

[subkanthi]

Fixed in #443.

Mapped to MATERIALIZED and passed jdbc configuration variable to insert to materialized columns