18811441038 opened a new issue, #24905:
URL: https://github.com/apache/shardingsphere/issues/24905
**Dear Great Gods：**
I encountered some issues when using sharding-jdbc.
An error occurred when using "case when then end".
**sharding:**
```
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
```
**mybatis:**
```
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
```
**postgresql**
```
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.14</version>
</dependency>
```
**This problem can occur with both SQL statements, even if I assign a value
directly.**
**mybatis-mapper:**
```
<update id="updateBatch">
update r_test
<trim prefix="set" suffixOverrides=",">
<trim prefix="a_aaa = case" suffix="end,">
<foreach collection="list" item="item" >
<if test="item.aAaa != null">
when a_id = #{item.aId} then #{item.aAaa}
</if>
</foreach>
</trim>
</trim>
where
a_id in
<foreach collection="list" item="item" open="(" close=")"
separator=",">
#{item.aId}
</foreach>
</update>
<update id="updateBatch1">
UPDATE r_test as a
SET a_aaa =
CASE
WHEN a.a_id = 1 THEN
'aaa'
END
WHERE
a.a_id IN ( 1 )
</update>
```
**The following problem occurred：**
```
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@535b5665]
was not registered for synchronization because synchronization is not active
JDBC Connection
[org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@1cb974c3]
will not be managed by Spring
==> Preparing: UPDATE r_test as a SET a_aaa = CASE WHEN a.a_id = 1 THEN
'aaa' END WHERE a.a_id IN ( 1 )
==> Parameters:
Closing non transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@535b5665]
2023-03-30 18:03:44.112 ERROR 64526 --- [nio-8081-exec-1]
o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet
[dispatcherServlet] in context with path [] threw exception [Request processing
failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested
exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.NullPointerException
### The error may exist in file
[/Users/yz/Documents/work/workspace/aig-tech-reconciliation/target/classes/mapper/testMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE r_test as a SET a_aaa = CASE WHEN
a.a_id = 1 THEN 'aaa' END WHERE a.a_id IN (
1 )
### Cause: java.lang.NullPointerException] with root cause
java.lang.NullPointerException: null
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitCaseExpression(PostgreSQLVisitor.java:499)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitCaseExpression(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$CaseExpressionContext.accept(PostgreSQLStatementParser.java:8361)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitRemainSimpleExpr(PostgreSQLVisitor.java:486)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitSimpleExpr(PostgreSQLVisitor.java:408)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitSimpleExpr(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SimpleExprContext.accept(PostgreSQLStatementParser.java:6266)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitBitExpr(PostgreSQLVisitor.java:359)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitBitExpr(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$BitExprContext.accept(PostgreSQLStatementParser.java:6031)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitPredicate(PostgreSQLVisitor.java:299)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitPredicate(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$PredicateContext.accept(PostgreSQLStatementParser.java:5821)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitBooleanPrimary(PostgreSQLVisitor.java:256)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitBooleanPrimary(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$BooleanPrimaryContext.accept(PostgreSQLStatementParser.java:5595)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitExpr(PostgreSQLVisitor.java:241)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.PostgreSQLVisitor.visitExpr(PostgreSQLVisitor.java:113)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$ExprContext.accept(PostgreSQLStatementParser.java:5391)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitAssignmentValue(PostgreSQLDMLVisitor.java:192)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitAssignmentValue(PostgreSQLDMLVisitor.java:112)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$AssignmentValueContext.accept(PostgreSQLStatementParser.java:1122)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitAssignment(PostgreSQLDMLVisitor.java:184)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitAssignment(PostgreSQLDMLVisitor.java:112)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$AssignmentContext.accept(PostgreSQLStatementParser.java:937)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitSetAssignmentsClause(PostgreSQLDMLVisitor.java:167)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitSetAssignmentsClause(PostgreSQLDMLVisitor.java:112)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$SetAssignmentsClauseContext.accept(PostgreSQLStatementParser.java:985)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitUpdate(PostgreSQLDMLVisitor.java:155)
at
org.apache.shardingsphere.sql.parser.postgresql.visitor.impl.PostgreSQLDMLVisitor.visitUpdate(PostgreSQLDMLVisitor.java:112)
at
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser$UpdateContext.accept(PostgreSQLStatementParser.java:838)
at
org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
at
org.apache.shardingsphere.sql.parser.SQLParserEngine.parse0(SQLParserEngine.java:80)
at
org.apache.shardingsphere.sql.parser.SQLParserEngine.parse(SQLParserEngine.java:61)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:97)
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 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.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy102.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at
com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:54)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at
com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
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)
at com.sun.proxy.$Proxy84.update(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
at com.sun.proxy.$Proxy86.updateBatch1(Unknown Source)
at
com.aigtech.reconciliation.controller.ExchangeRateController.updateTest(ExchangeRateController.java:69)
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:878)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
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:652)
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
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.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.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:541)
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:373)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
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)
-| Servlet.service() for servlet [dispatcherServlet] in context with path
[] threw exception [Request processing failed; nested exception is
org.mybatis.spring.MyBatisSystemException: nested exception is
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.NullPointerException
### The error may exist in file
[/Users/yz/Documents/work/workspace/aig-tech-reconciliation/target/classes/mapper/testMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE r_test as a SET a_aaa = CASE WHEN
a.a_id = 1 THEN 'aaa' END WHERE a.a_id IN (
1 )
### Cause: java.lang.NullPointerException] with root cause-[TID: N/A] |-|
```
--
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]
