terrymanu commented on issue #25923:
URL:
https://github.com/apache/shardingsphere/issues/25923#issuecomment-3633415053
## Problem Understanding
- On SQLServer via ShardingSphere-JDBC (Druid datasource, sharded tables
WS_LOG_1..3 with INLINE on column type), executing SELECT ... ORDER BY
create_time DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY in MyBatis-Plus
pagination throws Unknown exception: Index: 2, Size: 2
(HY000/30000), occurring in the JDBC/MyBatis stack.
## Root Cause
- The error indicates a pagination parameter index out of bounds: trying
to access a 3rd parameter (index 2) when only 2 parameters exist. The SQL has
two ?, so ShardingSphere’s pagination parsing/rewriter derived or recognized a
parameter index that doesn’t match the
actual parameter list. This often happens in SQLServer OFFSET/FETCH
parsing or cross-shard pagination when a derived bound (e.g., row_number upper
limit) is misindexed.
## Issue Analysis
- For SQLServer cross-shard pagination, ShardingSphere computes
row_number/top bounds; if FETCH NEXT ? or a derived bound is assigned index 2
while only indexes 0 and 1 exist, an IndexOutOfBoundsException occurs and is
wrapped as Unknown exception.
- The SQL omits the sharding key type, triggering full-shard routing with
pagination merge, which depends on ShardingSphere’s pagination rewrite; an
incorrect parameter index (labeled 2 with only two params passed) surfaces in
full-routing scenarios.
- Need to distinguish between a SQLServer dialect indexing bug and
external parameter modification (e.g., MyBatis-Plus pagination plugin). Current
data is insufficient for a definitive root cause.
## Conclusion
- Evidence points to a mismatch between pagination parameter indexing and
the provided argument list, leading to out-of-bounds access. It’s unclear
whether this is a ShardingSphere pagination parse/rewrite defect or external
parameter misalignment. Please provide:
1. ShardingSphere version and SQLServer JDBC driver version
2. Routed SQL and parameter list with props.sql-show: true (including
rewritten SQL)
3. Full stack trace showing the innermost exception origin (e.g.,
PaginationContext/ParameterBuilder)
4. MyBatis layer parameter order/count (logs or debug), and whether
extra ? conditions exist
5. Deployment topology (JDBC vs Proxy) and SQLServer version
(OFFSET/FETCH support)
- Meanwhile, to follow sharding best practices and reduce rewrite
complexity, include the sharding key type in the WHERE clause, and compare
single-shard vs multi-shard behavior.
--
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]
