yzeng1618 opened a new pull request, #12991: URL: https://github.com/apache/seatunnel/pull/12991
### Purpose of this pull request Chunk-split sampling sorted the sampled split keys on the Java side with `Arrays.sort` (`String.compareTo`, i.e. UTF-16 code-unit order), while the snapshot/split chunk reads filter rows with `WHERE key >= ? AND NOT(key = ?) AND key <= ?`, which the database evaluates under its own collation. For string split keys (varchar/uuid primary keys) the two orderings disagree (e.g. `en_US.UTF-8` on PostgreSQL, `utf8mb4_0900_ai_ci` on MySQL, `SQL_Latin1_General_CP1_CI_AS` on SQL Server), so: - chunk ranges overlap: the same row matches two chunks and is read twice (duplicate records at the sink), - or invert: some chunks stay permanently empty, - and rows can concentrate into a single giant chunk (the unbounded last chunk swallows most of the key space), overloading one subtask — we observed a 130M-row single chunk OOM a TaskManager in production. This PR adds `ORDER BY <split column>` to the sampling query and removes the Java-side `Arrays.sort`, in all five CDC connectors and in the JDBC batch source, so the sample, the chunk boundaries and the chunk WHERE filters all live in the same (database) order. For numeric split keys nothing changes (Java numeric order equals database order); the fix matters for string keys. PostgreSQL uuid detail: the SELECT list keeps the `convertType` cast (`"id"::text`, matching the split-key type the snapshot WHERE filters compare), but the ORDER BY deliberately uses the RAW column. An ORDER BY on the cast expression cannot use the uuid btree index and would force a seq scan plus a server-side external sort on huge tables; the raw uuid column orders by uuid byte order, which is identical to the hex-text order that `::text` comparisons use, so the boundaries stay consistent while the planner can satisfy the ORDER BY with an index scan. Additional cleanups in the same area: - The JDBC batch sampling now skips NULL sample values: a null boundary breaks the chunk range construction (and crashed the removed `Arrays.sort` with an NPE). NULL keys never match any range predicate anyway. - The unused MOD-based sampling variants in the CDC utils (dead code — the splitters only call the streaming variant) are removed; the streaming sampling methods are renamed to `sampleDataFromColumn` (`skipReadAndSampleData` for PostgreSQL, which has a column-aware overload). ### Does this PR introduce _any_ user-facing change? No configuration-surface change (no options added/renamed/removed). Behaviour: for tables with string split keys that reach the sampling split path, chunk boundaries are now computed in database collation order, eliminating duplicate reads across chunk boundaries, permanently empty chunks and single-giant-chunk skew. On engines without a servable index for the split column (e.g. a user-configured non-leading `snapshotSplitColumn`, or MPP engines using the default dialect), the ORDER BY introduces a server-side sort of the sampled column — the same trade-off the per-chunk boundary queries (`queryNextChunkMax`) already make today. ### How was this patch tested? Unit tests added/extended: - `PostgresUtilsTest` (extended): uuid sampling SQL shape — `SELECT "id"::text FROM ... ORDER BY "id"` (cast kept in SELECT, raw column in ORDER BY); non-uuid SQL shape; the sample preserves database collation order without a Java re-sort; auto-commit state is restored after sampling. - `Db2UtilsTest` (extended): sampling SQL contains `ORDER BY` on the raw column. - `DuckDBDialectTest` (extended, runs against a real DuckDB): NULL sample values are skipped (this test fails with an NPE on the old code); the sample is returned in database order rather than physical insertion order. - The existing `DuckDBDialectTest#testSampleDataFromColumn` and the Oracle dialect sampling IT keep passing — ORDER BY does not change their results. The sampling split path cannot be reached by the existing E2E suites (their tables are far below the `split.sample-sharding.threshold`), so no E2E changes were needed. `spotless:apply` was applied to all touched modules; remaining verification is left to CI. -- 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]
