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]

Reply via email to