terrymanu commented on issue #28014:
URL:
https://github.com/apache/shardingsphere/issues/28014#issuecomment-3636832166
## Issue Understanding
- On Proxy 5.4.0 with sharding, a LEFT JOIN between students and
student_skill (join on st_id, select IFNULL(ss.skill,'')) returns skill as
empty for all rows in sharding_db, while native MariaDB shows correct skill
values.
- Config: databases sharded by user_id (ds_${user_id % 2}); students
sharded by order_id; student_skill sharded by order_item_id; the two tables are
declared as bindingTables. Sample data uses user_id=1 for students and
user_id=2 for student_skill.
## Root Cause
- Binding tables require the same sharding key/algorithm and co-located
sharding key values for joined rows. Here the DB sharding key is the same
(user_id), but the sample data has different user_id values, so students rows
are on ds_1 while student_skill rows are on
ds_0. Binding still forces routing to a single shard, so the join sees
no matching right-table data and skill is empty.
- The table sharding keys also differ (order_id vs order_item_id), which
violates the binding-table requirement of identical sharding keys/algorithms,
increasing misrouting risk.
## Analysis
- With no sharding key condition in the join, binding makes the join reuse
the main table’s routing; because the two tables’ data are not co-located, the
routed shard lacks matching student_skill rows, yielding empty skill.
- Removing binding or ensuring co-location would make the join hit the
correct shards and match native results. This is a configuration/data
misalignment, not a LEFT JOIN execution bug.
## Conclusion
- The missing skill values are caused by sharding rule/data not satisfying
binding-table constraints; it is not a Proxy LEFT JOIN bug.
- Recommendations:
1. Only declare bindingTables when both tables use the same sharding
key/algorithm and business data keeps the key values aligned;
2. Fix rules/data so user_id aligns across both tables; if co-location
cannot be guaranteed, remove binding or mark the right table as broadcast;
3. Enable sql-show: true to inspect actual routed SQL and target
shards to verify routing.
--
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]
