Shardingsphere: SQL-Parse-LimitSegment: PaginationContext.getValue(PaginationContext.java:57): Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

Created on 6 Aug 2020  路  7Comments  路  Source: apache/shardingsphere

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

4.1.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC.

Expected behavior

work on.

Actual behavior

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

The error may exist in com//mapper/Mapper.xml

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT t.agent_code FROM t_user_cbs_agent t WHERE (t.depart_date >= ? OR t.depart_date = '' OR t.depart_date IS NULL) AND (t.approve_date <= ? AND t.approve_date IS NOT NULL AND t.approve_date != '') AND t.grade_code IN ('S01', 'M04', 'M03', 'M02', 'M01', 'C99', 'A04', 'A03', 'A01') AND t.agent_code NOT IN (SELECT b.exchange_of_agent_code FROM t_user_cbs_agent b WHERE (b.exchange_agent_success_date >= ? AND b.exchange_agent_success_date <= ?) AND (b.exchange_of_agent_code != '' AND b.exchange_of_agent_code IS NOT NULL) AND b.exchange_sts = '01') LIMIT ?, ?

Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy147.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
at com.sun.proxy.$Proxy162.findAgentCodesPageListByTargetDateAndCondition(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:497)
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 com.sun.proxy.$Proxy163.findAgentCodesPageListByTargetDateAndCondition(Unknown Source)
at com.**.service.back.AssessAggrateQuotaServiceImpl.findDataList(AssessAggrateQuotaServiceImpl.java:144)
at com.**.service.back.AssessAggrateQuotaServiceImpl$$FastClassBySpringCGLIB$$c54d1342.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.**.service.back.AssessAggrateQuotaServiceImpl$$EnhancerBySpringCGLIB$$17be2515.findDataList(<generated>)
at com.**.back.AssessAggrateQuotaServiceTest.findDataList(AssessAggrateQuotaServiceTest.java:29)
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:497)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

The error may exist in com//mapper/Mapper.xml

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT t.agent_code FROM t_user_cbs_agent t WHERE (t.depart_date >= ? OR t.depart_date = '' OR t.depart_date IS NULL) AND (t.approve_date <= ? AND t.approve_date IS NOT NULL AND t.approve_date != '') AND t.grade_code IN ('S01', 'M04', 'M03', 'M02', 'M01', 'C99', 'A04', 'A03', 'A01') AND t.agent_code NOT IN (SELECT b.exchange_of_agent_code FROM t_user_cbs_agent b WHERE (b.exchange_agent_success_date >= ? AND b.exchange_agent_success_date <= ?) AND (b.exchange_of_agent_code != '' AND b.exchange_of_agent_code IS NOT NULL) AND b.exchange_sts = '01') LIMIT ?, ?

Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

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:497)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 59 more

Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer
at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.getValue(PaginationContext.java:57)
at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.(PaginationContext.java:50)
at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.LimitPaginationContextEngine.createPaginationContext(LimitPaginationContextEngine.java:38)
at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.PaginationContextEngine.createPaginationContext(PaginationContextEngine.java:48)
at org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.(SelectStatementContext.java:100)
at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:103)
at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:87)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:99)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143)
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:497)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy416.query(Unknown Source)
at com.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)
... 65 more

Reason analyze (If you can)

sql parse: Limit clause parameter index (-2) error.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

SELECT t.agent_code
FROM t_user_cbs_agent t
WHERE (t.depart_date >= ? OR t.depart_date = '' OR t.depart_date IS NULL)
AND (t.approve_date <= ? AND t.approve_date IS NOT NULL AND t.approve_date != '')
AND t.grade_code IN ('S01', 'M04', 'M03', 'M02', 'M01', 'C99', 'A04', 'A03', 'A01')
AND t.agent_code NOT IN (
SELECT b.exchange_of_agent_code
FROM t_user_cbs_agent b
WHERE ( b.exchange_agent_success_date >= ? AND b.exchange_agent_success_date <= ?)
AND (b.exchange_of_agent_code != '' AND b.exchange_of_agent_code IS NOT NULL)
AND b.exchange_sts = '01'
)
LIMIT ?, ?
;

Example codes for reproduce this issue (such as a github link).

debug var:

limit.offset: 2
limit.rowCount: 3
SelectStatement.parameterCount: 4
List parameters: [2020-05-26, 2020-06-25, 2020-05-26, 2020-06-25, 0, 20]

SQL parse bug
Source
picture xiangtao123

All 7 comments

@jingshanglu My feeling is that there are many similar issues about this exception. Could you confirm whether we fix it on our master branch?

tristaZero picture tristaZero on 6 Aug 2020

long org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.getValue(PaginationValueSegment paginationValueSegment, List parameters)

private long getValue(final PaginationValueSegment paginationValueSegment, final List<Object> parameters) {
        if (paginationValueSegment instanceof ParameterMarkerPaginationValueSegment) {
            Object obj = parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).**getParameterIndex()**);
            return obj instanceof Long ? (long) obj : (int) obj;
        } else {
            return ((NumberLiteralPaginationValueSegment) paginationValueSegment).getValue();
        }
    }
xiangtao123 picture xiangtao123 on 6 Aug 2020

@xiangtao123 It seems you found the cause, nice. :)
What you mentioned is on our master branch, didn't it? Do you think it is likely for you to fix it?
Welcome your PR for this one! If not, please tell us, we will welcome the community to give it a fix.

Best,
Trista

tristaZero picture tristaZero on 7 Aug 2020

@xiangtao123 Now, it has been fixed on master branch, new version will be ok.

jingshanglu picture jingshanglu on 18 Sep 2020
👍1

@jingshanglu Thanks for your feedback.
@xiangtao123 Hi, you can try this using themaster branch.

tristaZero picture tristaZero on 20 Sep 2020
👍1

@xiangtao123 Now, it has been fixed on master branch, new version will be ok.

That's great!

xiangtao123 picture xiangtao123 on 20 Sep 2020

@jingshanglu Thanks for your feedback.
@xiangtao123 Hi, you can try this using themaster branch.

excellent!

xiangtao123 picture xiangtao123 on 20 Sep 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

bx189303 picture bx189303  路  3Comments
hzlzz picture hzlzz  路  4Comments
GLBB picture GLBB  路  5Comments
kenny-qiao picture kenny-qiao  路  4Comments
beijixi picture beijixi  路  5Comments