SEPURI-SAI-KRISHNA commented on PR #13092: URL: https://github.com/apache/seatunnel/pull/13092#issuecomment-6776439384
@nzw921rx thanks for the questions. **Why the result stays exact.** Every split filters on the split column: range splits with `>=` and `<=` comparisons (the first and last ranges are open), hash splits with one hash bucket (MySQL uses `CRC32(col)`). In SQL a comparison with NULL is never true, so on `dev`, once a table is read by more than one split, a row whose split column is NULL matches no split and is not read, and the job still ends with success. (A table read as one split has no filter.) This PR adds one split with `col IS NULL`. It matches exactly the rows that no other split reads, so each row is read once. Hash splits of a nullable column also get `AND col IS NOT NULL`, because some databases hash NULL to a value (DuckDB does), and those rows would be read twice. The extra split is added only when the column is nullable and a probe finds a NULL, so other columns keep today's splits. The unit tests read every split and check that all 100 ids come back exactly once, for range and hash splits. One cost: all NULL rows go to that one split, so a mostly NULL column gives an uneven split, but no wrong data. **Not splitting on NULL instead.** We discussed this in #13091 with @SEZ9. Not splitting on a nullable column (what #12597 did for CDC, which needs ordered key ranges) would change the splits of every table with a nullable unique key, even one with no NULL, and a user who sets a nullable `partition_column` would lose the parallel read or get an error. A warning alone would still drop the rows. So we chose the extra split. **Business examples.** 1. No split settings. A MySQL table without a primary key and with a unique key on a nullable column, for example `users.email`, where users who signed up by phone have no email. MySQL allows many NULLs in a unique key, and without a primary key the JDBC source picks the unique key column as the split column by itself (`ChunkSplitter#findSplitKey`). Once the table is read by more than one split, no user without an email is copied. The new `JdbcMysqlSplitIT` test uses this setup. 2. A user sets `partition_column` to a nullable column, such as `orders.customer_id`, which is NULL for guest checkouts. With more than one split, every guest order is lost. In my local MySQL runs (1000 rows, 100 with a NULL split column), `dev` reads 900 rows and reports success; with this PR it reads 1000, with no duplicates. If you prefer another behaviour, tell me and I will change it. -- 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]
