ShardingJDBC 5.5.0 with Spring Boot 3.3.13: In-Memory Merge Query Not Triggering for Pagination (Returns 20 Instead of 5 Records)

[jiananxu]

Environment

• Spring Boot Version: 3.3.13
• ShardingJDBC Version: 5.5.0
• Database: Dameng Database (Oracle-like syntax)
• Sharding Configuration: 4 tables with pagination rules

Problem Description

When executing pagination queries using Spring Data's PageRequest, the expected in-memory merge query does not trigger, resulting in incorrect pagination behavior.

Code Example:

PageRequest page = PageRequest.of(0, 5, Sort.by(Sort.Direction.DESC, "id"));
Page<BusinessLog> certs = businessLogDao.findAll(page);

Expected Result:

• Should return 5 records (as specified in page size)
• Results should be merged from all 4 sharded tables and sorted by ID in descending order

Actual Result:

• Returns 20 records (5 from each of the 4 shards, without proper merging)
• No in-memory merge operation appears to be executed

Key Symptoms

1. Pagination size is ignored - returns 20 records instead of 5
2. Sorting may not be properly applied across all shards
3. No evidence of in-memory result merging

Questions & Assistance Needed

1. Configuration Issues

• Are there specific configurations required to enable in-memory merging for pagination in ShardingJDBC 5.5.0?
• Could there be compatibility issues with Dameng database?

2. Manual Triggering

• Is there a way to manually trigger or force in-memory merge queries?
• Are there specific APIs or configurations to enforce result merging?

3. Troubleshooting Guidance

• What would be the recommended approach to debug this issue?
• Are there known limitations with Spring Data JPA repository methods and ShardingJDBC pagination?

4. Database Specifics

• Could the Dameng database (Oracle-like) require special handling?
• Are there dialect configurations or compatibility settings needed?

Thank you for your assistance!

[terrymanu]

Root Cause

1. Database Compatibility Limitation: ShardingSphere primarily supports mainstream databases such as MySQL, PostgreSQL, Oracle, and SQL Server. Although Dameng Database uses Oracle syntax, there are differences in
   underlying implementation and dialect support.
2. Pagination Dialect Adaptation: Different databases have different pagination SQL syntax implementations. ShardingSphere needs to implement specific dialect support for each database, and currently there is no
   pagination dialect implementation for Dameng Database.

Recommended Solutions

Use Supported Databases: Consider migrating to officially supported databases such as PostgreSQL or Oracle.

Thank you for your interest and use of ShardingSphere

[terrymanu]

Here is some additional information.
SphereEx DBPlusEngine, an enterprise-grade product developed based on the core code of ShardingSphere, has significantly enhanced capabilities in DaMeng database support and other areas.

Company website: https://www.sphere-ex.com/
For a comparison between the open-source and enterprise editions: https://www.sphere-ex.cn/comparison/

If you are interested, you can find their contact information on the company website to communicate with them.

[jiananxu]

I am using Oracle 19c and have the same issue.

[terrymanu]

Please forgive my guess, but based on the current situation, I'm afraid I can't help you resolve this issue.

[jiananxu]

I found another solution.
Use native SQL for pagination
thanks