terrymanu commented on issue #29539:
URL:
https://github.com/apache/shardingsphere/issues/29539#issuecomment-3495544132
Root Cause Analysis
Conclusion: This is a design limitation of ShardingSphere, not a bug
Through analysis of ShardingSphere source code, the issue occurs in
ShardingSelectRouteContextChecker.java:38-40:
if (selectStatement.getCombine().isPresent() &&
!isRouteToSingleDataSource(routeContext)) {
throw new
SelectMultipleDataSourcesWithCombineException(selectStatement.getCombine().get().getCombineType().name());
}
Technical Reasons:
1. Routing Conflict: Single table t_order routes to a single data source,
while sharded table t_order_his routes to specific shard nodes based on time
conditions
2. Multi-Data Source Limitation: UNION ALL queries need to route to
multiple data sources, which violates ShardingSphere's architectural constraints
3. Consistency Guarantee: This limitation ensures the correctness and
consistency of complex query results
Solutions Following ShardingSphere Best Practices
Solution 1: Application-Level Merging (Most Recommended)
// Execute queries separately
List<Order> currentOrders = jdbcTemplate.query(
"SELECT * FROM t_order WHERE create_time BETWEEN ? AND ?",
params, orderMapper);
List<Order> historyOrders = jdbcTemplate.query(
"SELECT * FROM t_order_his WHERE create_time BETWEEN ? AND ?",
params, orderMapper);
// Merge at application level
List<Order> allOrders = new ArrayList<>();
allOrders.addAll(currentOrders);
allOrders.addAll(historyOrders);
Advantages:
- Fully aligns with ShardingSphere design philosophy
- Controllable performance, can be optimized separately
- Easy to maintain and debug
Solution 2: Convert Single Table to Broadcast Table
Add broadcast table rules in sharding configuration:
sharding:
broadcast-tables:
- t_order
Applicable Scenarios:
- t_order has relatively small data volume
- Frequent JOIN queries with sharded tables are needed
- Data update frequency is not high
Advantages:
- Supports UNION ALL queries
- Good query performance
- Simple configuration
Not Recommended Solutions
SQL Federation Engine
Although ShardingSphere provides SQL Federation functionality to handle
cross-data-source complex queries, it:
- Is still in experimental stage, with insufficient stability
- May have performance issues and unknown risks
- Not recommended for production environments
Information Needed from User
To provide the most suitable solution recommendations, users should
understand:
1. Data Volume:
- Approximately how much data does the t_order table contain?
- What is the data distribution pattern of the t_order_his table?
2. Query Characteristics:
- How frequent are these UNION ALL queries?
- What are the performance requirements for queries?
3. Business Requirements:
- Can t_order be converted to a broadcast table?
- Is application-level merging acceptable?
Summary
This issue reflects ShardingSphere's trade-offs in distributed database
architecture design: to ensure data consistency and query correctness, it
restricts the use of certain complex SQL statements. It is recommended that
users choose application-level merging or broadcast table solutions based
on specific business scenarios, as this both follows ShardingSphere's design
philosophy and ensures system stability and maintainability.
--
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]
