[Flink CDC][Oracle] Flink CDC Table Initial Snapshot Only One Record

[superfive666]

-- create oracle table
create table oracle.test_table (
  id number,
  name varchar2(20)
);

-- initial data
-- insert 100 records into the table

After setting up the oracle cdc table in our flink streamed table execution environment:

-- source table
create table if not exists source_catalog.source_oracle.test_table (
  `ID` bigint,
  `NAME` string,
  primary key(`ID`,`NAME`) not enforced
) with (
  'connector' = 'oracle-cdc',
  'hostname' = '<oracle-host>',
  'username' = 'flinkuser',
  'password' = 'password',
  'schema-name' = 'ORACLE',
  'database-name' = 'ORCL',
  'port' = '1521',
  'table-name' = 'TEST_TABLE',
  'debezium.snapshot.num-retained.max' = '10',
  'debezium.incremental.snapshot.watermarking.strategy' = 'insert_delete',
  'debezium.log.mining.strategy' = 'online_catalog',
  'debezium.log.mining.continuous.mine' = 'true',
  'debezium.database.tablename.case.insensitive'='false'
);

-- destination table: iceberg
create table if not exists glue_catalog.oracle.test_table (
  `ID` bigint,
  `NAME` string,
  primary key(`ID`,`NAME`) not enforced
) with (
  'format-version'='2',
  'write.upsert.enabled'='true'
);

After execute the following statement:

insert into glue_catalog.oracle.test_table
select id, name from source_catalog.source_oracle.test_table;

We noticed that only one record with the earliest "ora_rowscn" was being added to the downstream iceberg table.
Anyone can suggest what could be the potential cause of this behaviour?

We did subsequent testing on the data replication:
insert into oracle.test_table (id, name) values (100, 'abcdabcd'); commit;
This record is being replicated successfully in the downstream iceberg table.

After we do the update:
update oracle.test_table set name = 'defdefdef' where id = 100; commit;
We noticed the bug that in the downstream table, there are 2 records:

+---+-------------+
|ID|NAME         |
+---+-------------+
|100|abcdabcd       |
|1|first record       |
|100 |defdefdef     |
+---+-------------+

Could this be due to the reason of composite primary key(id, name)?
Anyone can share any insight on this?

[superfive666]

And here is an updated observation on the behaviour of duplicated insert of records.
Due to the fact of the composite primary key, when updating an existing record in the source table, we are not expected to update the primary key of the row. For example,
update test_tabe set id = 123 where id = 55;
As the primary key is already gloablly unique, updating it to a different value does not add any value and it might break any foreign key relation in other table.

However, as the archived log showing an update event "u", the downstream table should be updated according to the payload before and payload after in the merge-append process.