You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "TeslaCN (via GitHub)" <gi...@apache.org> on 2023/01/28 05:55:03 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue, #23764: Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes.
TeslaCN opened a new issue, #23764:
URL: https://github.com/apache/shardingsphere/issues/23764
## Bug Report
### Which version of ShardingSphere did you use?
198f1517a51cb898f9c826dabd222524f9a4a39e
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy PostgreSQL E2E tbl
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Add new case to
`test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml`
```xml
<test-case sql="INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08')" db-types="PostgreSQL,openGauss" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting,sharding_governance">
<assertion parameters="1:int, 1:int, insert:String" expected-data-file="insert_for_order_1.xml" />
<assertion parameters="2:int, 2:int, insert:String" expected-data-file="insert_for_order_2.xml" />
</test-case>
```
### Expected behavior
No error occurred.
### Actual behavior
```
[ERROR] 2023-01-28 13:18:18.706 [main] o.a.s.t.e.f.watcher.E2EWatcher - Error case: assertExecute[proxy: tbl -> PostgreSQL -> Placeholder -> INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08')], message: org.postgresql.util.PSQLException: ERROR: Please check your sharding conditions `ShardingConditions(conditions=[ShardingCondition(values=[], startIndex=0)], sqlStatementContext=org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext@33f0358d, rule=org.apache.shardingsphere.sharding.rule.ShardingRule@50e2f8cd, subqueryContainsShardingCondition=true)` to avoid same record in table `t_order` routing to multiple data nodes.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.executeForPreparedStatement(GeneralDMLE2EIT.java:96)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.assertExecute(GeneralDMLE2EIT.java:79)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
[ERROR] 2023-01-28 13:18:18.706 [main] o.a.s.t.e.f.watcher.E2EWatcher - Error case: assertExecute[proxy: tbl -> PostgreSQL -> Placeholder -> INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08')], message: org.postgresql.util.PSQLException: ERROR: Please check your sharding conditions `ShardingConditions(conditions=[ShardingCondition(values=[], startIndex=0)], sqlStatementContext=org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext@33f0358d, rule=org.apache.shardingsphere.sharding.rule.ShardingRule@50e2f8cd, subqueryContainsShardingCondition=true)` to avoid same record in table `t_order` routing to multiple data nodes.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.executeForPreparedStatement(GeneralDMLE2EIT.java:96)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.assertExecute(GeneralDMLE2EIT.java:79)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
org.postgresql.util.PSQLException: ERROR: Please check your sharding conditions `ShardingConditions(conditions=[ShardingCondition(values=[], startIndex=0)], sqlStatementContext=org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext@33f0358d, rule=org.apache.shardingsphere.sharding.rule.ShardingRule@50e2f8cd, subqueryContainsShardingCondition=true)` to avoid same record in table `t_order` routing to multiple data nodes.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.executeForPreparedStatement(GeneralDMLE2EIT.java:96)
at org.apache.shardingsphere.test.e2e.engine.dml.GeneralDMLE2EIT.assertExecute(GeneralDMLE2EIT.java:79)
```
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes. [shardingsphere]
Posted by "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
Re: [I] Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes. [shardingsphere]
Posted by "Phixsura (via GitHub)" <gi...@apache.org>.
Phixsura commented on issue #23764:
URL: https://github.com/apache/shardingsphere/issues/23764#issuecomment-4471190753
PR #38698.
Root cause: in `InsertClauseShardingConditionEngine.createShardingCondition`, the `instanceof SimpleExpressionSegment / CommonExpressionSegment / isNowExpression` chain has no branch for `TypeCastExpression` (a `ComplexExpressionSegment`). A PostgreSQL `?::int4` cast on the sharding key therefore falls through silently and the sharding value list ends up empty, which routes the INSERT to every shard.
Fix unwraps nested `TypeCastExpression` to its underlying expression before the instanceof chain. Added three regression tests covering a TypeCast over a parameter marker, over a literal, and nested casts. Also enabled the `?::int4` case in `e2e-dml-insert.xml` that was previously commented out with `TODO Fix #23764`.
`ConditionValue` / `WhereClauseShardingConditionEngine` have the same shape of gap for WHERE-clause casts; reported scenario is INSERT only, follow-up PR for the WHERE path.
--
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
[GitHub] [shardingsphere] TeslaCN commented on issue #23764: Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes.
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23764:
URL: https://github.com/apache/shardingsphere/issues/23764#issuecomment-1408008025
https://github.com/apache/shardingsphere/blob/43685611007ae059a0d4798f25e766105348c788/test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml#L24-L28
--
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
Re: [I] Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes. [shardingsphere]
Posted by "terrymanu (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
terrymanu closed issue #23764: Error: Please check your sharding conditions ... to avoid same record in table `t_order` routing to multiple data nodes.
URL: https://github.com/apache/shardingsphere/issues/23764
--
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