HhH666-ux opened a new pull request, #40094: URL: https://github.com/apache/shardingsphere/pull/40094
Fixes #40001. ## Problem For a cross-shard `SELECT DISTINCT user_id FROM t_order` on MySQL, the merged result contains duplicate rows: with shard results `[NULL, 10]` and `[10]`, the merge produces `[10, NULL, 10]` instead of `[NULL, 10]`. ## Root cause `OrderByContextEngine#createOrderByContextForDistinctRowWithoutGroupBy` injects `ORDER BY` items for DISTINCT-without-GROUP-BY queries and stores the dialect default `NullsOrderType` verbatim, which is `LOW` for MySQL. `OrderByItemSegment#getNullsOrderType(DatabaseType)` only resolves the stored value when it is `null`, so `LOW` reaches the stream merger unchanged. `CompareUtils#compareTo` treats anything other than `FIRST` as nulls-last, while MySQL shards return NULL first for ASC. The priority queue therefore interleaves NULL between equal values, `GroupByStreamMergedResult` splits the group, and the equal rows survive deduplication. Dialects defaulting to `HIGH` (PostgreSQL, Oracle, openGauss) are not affected because HIGH + ASC already means nulls-last. This is the same resolution pattern introduced for `ShardingProjectionsTokenGenerator` by #32813. ## Fix - `OrderByContextEngine`: resolve the dialect default with `getResolvedOrderType(OrderDirection.ASC.name())` when creating the injected ORDER BY item. - `ShardingDQLResultMerger#setGroupByForDistinctRow`: resolve the dialect default for the injected GROUP BY items the same way. The rewritten SQL pushed down to shards is unaffected: `ShardingOrderByTokenGenerator` renders only the column label and ASC/DESC, never the nulls order type. ## Tests - `OrderByContextEngineTest#assertCreateOrderInDistinctByWithoutOrderByResolvesDefaultNullsOrderType` asserts the injected item carries `FIRST` for MySQL. - `ShardingDQLResultMergerTest#assertMergedDistinctRowWithNullValueForMySQL` merges shard results `[NULL, 10]` and `[10]` and asserts the output is exactly `[NULL, 10]`. -- 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]
