Doris-Breakwater commented on issue #66231: URL: https://github.com/apache/doris/issues/66231#issuecomment-5115381793
### Initial analysis This is reproducible and the empty tablets are explained by the current distribution algorithm. I would classify it as a **systematic hash-quality/design limitation**, not an ingestion failure or random statistical skew. There are currently no labels or earlier comments on the issue. #### Root cause For hash-distributed OLAP writes, Doris initializes the hash to zero, feeds the raw bytes of each distribution column to zlib-compatible CRC32, and selects `hash % num_buckets` ([4.0.5 routing code](https://github.com/apache/doris/blob/59de8c4c524008e8ab2e43b79312f716a3a423a8/be/src/exec/tablet_info.h#L229-L255)). A `SMALLINT` contributes exactly two bytes ([4.0.5 type handling](https://github.com/apache/doris/blob/59de8c4c524008e8ab2e43b79312f716a3a423a8/be/src/runtime/raw_value.h#L63-L69)). Current master retains the same routing rule ([master routing code](https://github.com/apache/doris/blob/27cb451229c7a42dd089c5248847828eb0536d02/be/src/storage/tablet_info.h#L239-L264)). For the exact little-endian `SMALLINT` domain `0..511` and 512 buckets, the lower nine CRC32 bits have a deterministic collision: ```text bucket(x) = bucket(x XOR 0x117), for every x in [0, 511] ``` Consequently, the 512 values reach only **256 distinct bucket indexes**, with two values per reachable bucket. For example: ```text shard_num 0 -> bucket 255 shard_num 279 -> bucket 255 shard_num 1 -> bucket 446 shard_num 278 -> bucket 446 ``` Thus, even if every `shard_num` has the same row count, half of the 512 logical tablets can never receive a row. The zero-row replica groups in the screenshot are consistent with this result. `DISTRIBUTED BY HASH` does not generally promise a one-to-one mapping when key NDV equals the bucket count, but this 50% unreachable pattern is a particularly poor interaction between CRC32's low bits, a two-byte sequential domain, and a power-of-two bucket count. A small Doris-side validation is: ```sql SELECT COUNT(DISTINCT crc32_internal(CAST(number AS SMALLINT)) % 512) AS smallint_used_buckets, COUNT(DISTINCT crc32_internal(CAST(number AS INT)) % 512) AS int_used_buckets FROM numbers("number" = "512"); ``` With the current algorithm, the results are `256` and `512`, respectively. `crc32_internal` is appropriate here because it is implemented specifically to match tablet routing; ordinary SQL `crc32()` is not the routing function. #### Compatibility and PR direction Changing the default hash or adding a final mixing step in place would be unsafe. Existing rows must continue to map to their original tablets; otherwise an upgraded table could route new rows differently, and FE tablet pruning could select a different tablet from the one containing old rows. FE independently computes CRC32 and applies the same modulo during pruning ([pruner](https://github.com/apache/doris/blob/27cb451229c7a42dd089c5248847828eb0536d02/fe/fe-core/src/main/java/org/apache/doris/planner/HashDistributionPruner.java#L90-L95), [CRC32 calculation](https://github.com/apache/doris/blob/27cb451229c7a42dd089c5248847828eb0536d02/fe/fe-core/src/main/java/org/apache/doris/catalog/PartitionKey.java#L245-L253)). The requested custom hash is therefore best treated as a versioned table feature rather than a BE-only fix: 1. Persist a distribution-hash algorithm identifier in table metadata and in the FE-to-BE sink descriptor; existing tables must default to legacy CRC32. 2. Initially support a closed set of deterministic algorithms (for example, legacy CRC32 and XXH64) rather than arbitrary SQL expressions. The current grammar and metadata carry only distribution-column names, not an expression or algorithm ([DDL grammar](https://github.com/apache/doris/blob/27cb451229c7a42dd089c5248847828eb0536d02/fe/fe-sql-parser/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4#L190-L203), [sink descriptor](https://github.com/apache/doris/blob/27cb451229c7a42dd089c5248847828eb0536d02/gensrc/thrift/Descriptors.thrift#L303-L315)). 3. Apply the selected algorithm consistently in BE write routing, FE tablet pruning, bucket-shuffle/colocation compatibility checks, schema changes, rollups/materialized indexes, and all load paths. 4. Add regression coverage for `SMALLINT 0..511 / 512 buckets`, pruning correctness, restart/metadata replay, mixed-version upgrade, and rejection of incompatible colocate definitions. 5. Require a rewrite/rebuild to change the algorithm of a table that already contains data. For this specific schema, practical workarounds for a newly created table are: - Use 256 buckets: the same 512 `SMALLINT` values occupy all 256 buckets, two shard values per bucket. - If 512-way placement and one-tablet-per-shard behavior are required, use an `INT` physical distribution key; for this exact `0..511` domain, the current four-byte CRC32 mapping reaches all 512 buckets. Verify with the query above before migration. - Add a higher-cardinality second distribution column if keeping all rows for one `shard_num` in one tablet is not required. #### Clarifications useful for final validation The root cause does not require logs or a query profile. To confirm the observed scope and prepare a regression case, please provide: - whether the affected build is exactly 4.0.5, current master, or both (the version field is ambiguous); - valid `SHOW CREATE TABLE` output (the pasted column definitions omit commas); - for one affected partition, the number of distinct zero-row **TabletId** values rather than replica rows, plus whether all 512 `shard_num` values occur in that partition. Breakwater-GitHub-Analysis-Slot: slot_be381027fb5f -- 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
