Hariomlokhande-coder opened a new issue, #40128: URL: https://github.com/apache/shardingsphere/issues/40128
## Bug Report ### Which version of ShardingSphere did you use? master @ 7cf1c001013 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC. I only tried JDBC, but this is in routing, so Proxy is probably affected too. ### Expected behavior `SELECT order_id FROM t_order WHERE order_id IN (1, 2 + 2)` returns 1 and 4. ### Actual behavior Only 1 comes back, with no error. The same happens with `IN (1, ABS(-4))` and with a prepared `IN (?, ? + 0)` bound to 1 and 4. It isn't only reads either: `UPDATE t_order SET user_id = user_id + 1000 WHERE order_id IN (1, 2 + 2)` reports 1 updated row and leaves order 4 untouched. `order_id = 2 + 2` or `IN (2 + 2)` on their own return 4 correctly, since those go to every shard. ### Reason analyze (If you can) `ConditionValueInOperatorGenerator` skips IN items it can't evaluate and builds the sharding condition from the remaining ones, so the statement is routed only to the shard of `1`. When none of the items can be evaluated it produces no condition and the statement goes to all shards, which is why the single-expression cases work. For `=`, `ConditionValueCompareOperatorGenerator` already returns no condition when the value can't be evaluated, and IN should do the same as soon as one item can't be. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. With `jdbc/src/test/resources/config/driver/driver-fixture-h2-mysql.yaml` (two data sources, `t_order` sharded by `order_id` with HASH_MOD, sharding-count 2): ```sql CREATE TABLE t_order (order_id INT PRIMARY KEY, user_id INT); INSERT INTO t_order (order_id, user_id) VALUES (1, 101), (2, 102), (3, 103), (4, 104); SELECT order_id FROM t_order WHERE order_id IN (1, 2 + 2); -- returns only 1 ``` ### Example codes for reproduce this issue (such as a github link). The SQL above against that fixture. I'll open a PR with a fix. -- 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]
