liquanjin opened a new issue #6996:
URL: https://github.com/apache/shardingsphere/issues/6996
### Which version of ShardingSphere did you use?
4.0.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
> ShardingSphere-JDBC
### Expected behavior
When i used a same sharding value can work, regardless i used Precompiled
statement sql or not Precompiled statement sql.
### Actual behavior
Sharding inner checkSubqueryShardingValues fail. throw a exception .
**java.lang.IllegalStateException: Sharding value must same with subquery**
``` log
org.mybatis.spring.MyBatisSystemException: nested exception is
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.IllegalStateException:
Sharding value must same with subquery.
### The error may exist in
**/**/**/order/dal/service/impl/mapper/StoreItemMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,item_name,item_code,store_id,status FROM
test_store_Item WHERE (id IN (select id from test_store_Item where store_id
= 1006) AND store_id IN (?))
### Cause: java.lang.IllegalStateException: Sharding value must same with
subquery.
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:78)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at **.sun.proxy.$Proxy171.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at
**.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
at
**.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
at
**.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61)
at **.sun.proxy.$Proxy200.selectList(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at **.sun.proxy.$Proxy201.selectList(Unknown Source)
at
**.**.**.order.impl.OrderServiceImpl.testSubQuery(OrderServiceImpl.java:185)
at
**.**.**.order.impl.OrderServiceImpl$$FastClassBySpringCGLIB$$3541015a.invoke(<generated>)
at
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at
**.**.**.order.impl.OrderServiceImpl$$EnhancerBySpringCGLIB$$569c60c1.testSubQuery(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:893)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:798)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
**.**.**.starter.filter.CustomSpanFilter.doFilter(CustomSpanFilter.java:81)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.cloud.sleuth.instrument.web.ExceptionLoggingFilter.doFilter(ExceptionLoggingFilter.java:50)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at brave.servlet.TracingFilter.doFilter(TracingFilter.java:86)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)
at
org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at
org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)
at
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.IllegalStateException:
Sharding value must same with subquery.
### The error may exist in
**/**/**/order/dal/service/impl/mapper/StoreItemMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,item_name,item_code,store_id,status FROM
test_store_Item WHERE (id IN (select id from test_store_Item where store_id
= 1006) AND store_id IN (?))
### Cause: java.lang.IllegalStateException: Sharding value must same with
subquery.
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 130 **mon frames omitted
Caused by: java.lang.IllegalStateException: Sharding value must same with
subquery.
at **.google.**mon.base.Preconditions.checkState(Preconditions.java:456)
at
org.apache.shardingsphere.core.route.router.sharding.ShardingRouter.checkSubqueryShardingValues(ShardingRouter.java:150)
at
org.apache.shardingsphere.core.route.router.sharding.ShardingRouter.route(ShardingRouter.java:109)
at
org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:63)
at
org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:59)
at
org.apache.shardingsphere.core.BaseShardingEngine.executeRoute(BaseShardingEngine.java:85)
at
org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:68)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:249)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:190)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at **.sun.proxy.$Proxy266.query(Unknown Source)
at
**.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
... 136 **mon frames omitted
```
### Reason analyze (If you can)
By i debug finded,when used't Precompiled method (${store_id}) define
parameter .
ShardingSphere-JDBC inner method
ConditionValueCompareOperatorGenerator.generate get ListRouteValue result
incorrect .
Return ListRouteValue's value Collection element DataType is
java.lang.Integer. (sharding value parameter in java.lang.Integer range )
In WhereClauseShardingConditionEngine.createShardingConditions method ,
> Collection<SubqueryPredicateSegment> subqueryPredicateSegments =
sqlStatement.findSQLSegments(SubqueryPredicateSegment.class);
statement build'result , sharding value DataType is java.lang.Integer.
But my sharding value column real DataType is **java.lang.Long**.
So ShardingSphere-JDBC throw a Exception. Because
ShardingRouter.checkSubqueryShardingValues fail.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
very easy .
1. configuration a sharding table, sharding value DataType is Long .
2. used twice sharding value as sql parameter in statement sql .
3. two parameter used different method define, used #{} and ${}
``` java
/**
* normal work
**/
@Select("select * from test_store_Item where store_id = #{store_id} and
id in (select id from test_store_Item where store_id = #{store_id} )")
public List<StoreItemEntity> searchItemListBySubQuery(@Param("store_id")
Long storeId);
/**
* throw Exception
**/
@Select("select * from test_store_Item where store_id = #{store_id} and
id in (select id from test_store_Item where store_id = ${store_id} )")
public List<StoreItemEntity>
searchItemListBySubQueryTwo(@Param("store_id") Long storeId);
```
### Example codes for reproduce this issue (such as a github link).
reproduce the behavior is very easy, so not need Example codes.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
