chengh1 opened a new issue #11793: URL: https://github.com/apache/shardingsphere/issues/11793
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.0.0-RC1-SNAPSHOT ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior When bindingTable for associated query and they are not related by sharding key, they should use shardingFederatedRoutingEngine to routing. If we want to treat them as binding tables, their association key should also be sharding key. ### Actual behavior When bindingTable for associated query and they are not related by sharding key, they use ShardingStandardRoutingEngine to routing, this will cause some eligible data can't be querying out because of some table associations are missing. ### Reason analyze (If you can)  When selecting the routing engine, ShardingStandardRoutingEngineis is selected only by judging that all tables are binding tables, but they should be judged whether associated by sharding key, if they are associated by sharding key , ShardingStandardRoutingEngine should be selected, otherwise ShardingFederatedRoutingEngine should be selected . ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. 1. there are two binding tables t_order and t_order_item, their sharding key are order_id , they also have a column user_id . 2. execute this sql： ``` select t1.* from t_order t1 join t_order_item t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); ``` if value 10 routing to sharding 0， value 11 routing to sharding 1, they are binding tables ,so will use ShardingStandardRoutingEngineis to routing , and the rewrite sql will be: ``` select t1.* from t_order_0 t1 join t_order_item_0 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); select t1.* from t_order_1 t1 join t_order_item_1 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); ``` but if t_order_0 and t_order_item_1 also have the same user_id value, the data can't be querying out. the number of correct rewrite sql should be 4, they are: ``` select t1.* from t_order_0 t1 join t_order_item_0 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); select t1.* from t_order_0 t1 join t_order_item_1 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); select t1.* from t_order_1 t1 join t_order_item_0 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); select t1.* from t_order_1 t1 join t_order_item_1 t2 on t1.user_id = t2.user_id where t1.order_id in (10, 11,); ``` **conclusion: if we want to treat them as binding tables, their association key should also be sharding key.** ### Example codes for reproduce this issue (such as a github link). -- 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]
