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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
