SEPURI-SAI-KRISHNA opened a new issue, #13160: URL: https://github.com/apache/seatunnel/issues/13160
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Bug%22) and found no similar issues. ### What happened In CDC mode the Databend sink can silently leave a target row with the wrong values, or remove it. The job finishes successfully. There are two causes in the same merge, which runs at each checkpoint commit and again on close: ```sql MERGE INTO db.target a USING (SELECT ..., action FROM db.stream QUALIFY ROW_NUMBER() OVER(PARTITION BY <conflict_key> ORDER BY add_time DESC) = 1) b ON a.<conflict_key> = b.<conflict_key> WHEN MATCHED AND b.action = 'update' THEN UPDATE * WHEN MATCHED AND b.action = 'delete' THEN DELETE WHEN NOT MATCHED AND b.action!='delete' THEN INSERT * ``` **1. An INSERT for a key that already exists is ignored.** No clause handles `MATCHED AND action = 'insert'`, so the target row keeps its old values. With the default `enable_delete = false` the DELETE is dropped, so a later re-insert of that key never takes effect. A snapshot that runs again over an existing table goes through the same clause, so it would not refresh changed rows (I did not run that case). **2. The latest event of a key does not always win.** The merge orders by `add_time`, which reaches the raw table with only milliseconds: the writer calls `setTimestamp`, and databend-jdbc 0.3.7 formats that with `yyyy-MM-dd HH:mm:ss.SSS` (sent `.123456`, stored `.123000`, while a SQL literal keeps `.123456`). Events of one key in the same millisecond tie, and the tied row that wins can be an older one. Measured with real SeaTunnel jobs on Zeta against `datafuselabs/databend:nightly` (v1.2.960), FakeSource to Databend, on `dev`: | Events | Expected | Result on `dev` | |---|---|---| | Target has `(5, old)`, then INSERT `(5, new)` | `new` | `old` | | Target has `(6, old)`, then DELETE (dropped) and INSERT `(6, new)` | `new` | `old` | | INSERT, UPDATE, UPDATE of each of 200 keys in one merge | 200 rows with `v3` | 200 rows: `v1` 147, `v2` 22, `v3` 31 | | 300 existing keys, DELETE then INSERT of each in one merge, `enable_delete = true` | 300 rows | 117 rows in one run, 168 in another, all with old values | In the run that left 117 rows, 183 of the 300 keys had a tied `add_time` in the raw table, the same as the number of rows lost (300 - 183 = 117). Updates and deletes applied one at a time work, and `DatabendCDCSinkIT` only covers that: it uses `batch_size = 1` and an empty table, so neither cause shows. I have a fix ready and will open a PR for this issue. ### SeaTunnel Version dev (9b8252eaf0). I measured on 941276923f, and `connector-databend` and its E2E module have not changed since. ### SeaTunnel Config ```conf env { parallelism = 1 job.mode = "BATCH" checkpoint.interval = 1000 } source { FakeSource { schema = { fields { id = "int", name = "string" } } rows = [ { kind = INSERT, fields = [1, "v1"] } { kind = UPDATE_AFTER, fields = [1, "v2"] } { kind = UPDATE_AFTER, fields = [1, "v3"] } ] } } sink { Databend { url = "jdbc:databend://127.0.0.1:8000/default?ssl=false" username = "root" password = "" database = "default" table = "target" conflict_key = "id" batch_size = 1000 } } ``` ### Running Command I ran this on an embedded Zeta server from a test harness, with more rows per job. With a binary distribution it is: ```shell bin/seatunnel.sh --config job.conf -m local ``` ### Error Exception ```log No error. The job finishes and the target table holds the wrong values. ``` ### Zeta or Flink or Spark Version Zeta (dev) ### Java or Scala Version JDK 17 ### Screenshots _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
