Re: [I] Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes. [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #23764:
URL: https://github.com/apache/shardingsphere/issues/23764#issuecomment-3632472713

   ##  Problem Understanding
   
     - Scenario: ShardingSphere-Proxy PostgreSQL/openGauss. SQL: INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08'). The sharding key is order_id, passed with ?::int4. Execution throws “Please
       check your sharding conditions…”.
   
   ##  Root Cause
   
     - PostgreSQL type casting wraps the placeholder as a TypeCastExpression. The current sharding condition extraction only recognizes bare ParameterMarkerExpression/literals and does not unwrap TypeCastExpression, so the sharding-key placeholder is not recognized.
       ShardingConditions becomes empty and is treated as a potential multi-node conflict, resulting in an error.
   
   ##  Analysis
   
     - The gap is expression unwrapping: TypeCastExpression contains the actual placeholder, but condition generation does not pass it through, losing the parameter index. If we unwrap TypeCastExpression and propagate the parameter index, sharding conditions will be built
       correctly.
     - Proposed fix plan:
         1. In expression extraction/condition generation, add handling for TypeCastExpression: if it wraps a ParameterMarkerExpression, extract that placeholder and its index, then continue the normal path.
         2. Add tests:
             - Parse/extraction layer: cover “placeholder wrapped by ::int4” and ensure parameter indices stay in order.
             - Routing layer: for an INSERT containing ?::int4, assert the sharding condition picks up the sharding key parameter and ShardingConditions is non-empty with correct routing (one case each for PostgreSQL and openGauss).
         3. Reuse existing common expression-unwrapping helpers if available to avoid duplication.
   
   ##  Conclusion
   
     - This is a defect where type casting wraps the placeholder and hides it from sharding condition extraction. The fix is to unwrap TypeCastExpression to pass through the parameter and index, plus add coverage. 
   
   Community contributors are warmly invited to submit a PR implementing the above plan and tests—thank you for your help!


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org