search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.79k stars 6.7k forks source link

5.3.1 select sql when order by throw NPE #32400

Closed Jpbdaidl closed 1 month ago

Jpbdaidl commented 1 month ago

Question

When my sql has order by field, the following error will appear, but I remove the order by, the query is normal, I use the version is:

org.yaml snakeyaml 1.33

​

org.apache.shardingsphere shardingsphere-jdbc-core 5.3.1

org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.3 snakeyaml org.yaml
linghengqian commented 1 month ago

the following error will appear

Jpbdaidl commented 1 month ago

error log:

SQL: SELECT COUNT(*) FROM plus_aim_scode_log WHERE stats_time = ?

Cause: java.lang.NullPointerException

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)
... 65 common frames omitted

Caused by: java.lang.NullPointerException: null at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:135) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitive(GroupByMemoryMergedResult.java:125) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:73) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:53) at org.apache.shardingsphere.infra.merge.result.impl.memory.MemoryMergedResult.(MemoryMergedResult.java:52) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.(GroupByMemoryMergedResult.java:56) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.getGroupByMergedResult(ShardingDQLResultMerger.java:128) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:93) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:71) at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:81) at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:71) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:575) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:521) at com.zaxxer.hikari.pool.ProxyStatement.getResultSet(ProxyStatement.java:214) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getResultSet(HikariProxyPreparedStatement.java) 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:69) at com.sun.proxy.$Proxy292.getResultSet(Unknown Source) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:237) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:187) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) 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 com.sun.proxy.$Proxy285.query(Unknown Source) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134) at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy284.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ... 71 common frames omitted

linghengqian commented 1 month ago

It sounds like there is a parsing problem with the antlr4 file written by an old version of shardingsphere. You need to use shardingsphere 5.5.0 for testing. If the problem still exists, you need to provide a git with a minimal reproducible unit test.

Jpbdaidl commented 1 month ago

I also reported an error after upgrading to 5.5.0, the table does not exist, but I configured this logical table again。 192.169.1.177 [DataService] 2024-08-05 11:02:46.792 ERROR [,6480658a9438c854,6480658a9438c854,true] 38996 --- [nio-7010-exec-1] drc.common.config.GlobalExceptionHandler:118 : nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'plus_aim_scode_log' does not exist.

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'plus_aim_scode_log' does not exist. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) at com.sun.proxy.$Proxy142.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) at com.sun.proxy.$Proxy143.selectByListPage(Unknown Source) at com.montnets.insertdb.controller.TaskController.testselectpage(TaskController.java:195) 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:205) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:898) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:809) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1072) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:965) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) at javax.servlet.http.HttpServlet.service(HttpServlet.java:529) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at brave.servlet.TracingFilter.doFilter(TracingFilter.java:68) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at brave.servlet.TracingFilter.doFilter(TracingFilter.java:87) at org.springframework.cloud.sleuth.instrument.web.LazyTracingFilter.doFilter(TraceWebServletAutoConfiguration.java:139) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1794) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'plus_aim_scode_log' does not exist. at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) 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.Invocation.proceed(Invocation.java:49) at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:83) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy284.prepare(Unknown Source) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:94) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:68) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134) at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy283.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) 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) ... 65 common frames omitted Caused by: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'plus_aim_scode_log' does not exist. at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:151) at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:148) at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:84) at org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:55) at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.lambda$bind$1(SelectStatementBinder.java:60) at java.util.Optional.map(Optional.java:215) at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:60) at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:48) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:82) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:72) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:63) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:207) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:172) at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:94) at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) 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.ConnectionLogger.invoke(ConnectionLogger.java:55) at com.sun.proxy.$Proxy289.prepareStatement(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) ... 90 common frames omitted

linghengqian commented 1 month ago

The problem turns out to be that you didn't specify what database this is in your issue. You always need to provide a git with minimal reproducible unit tests. The database can either be created using h2database or testcontainers-java, which saves time for both you and me.

terrymanu commented 1 month ago

The issue involves other third-party dependencies, but our focus is solely on ShardingSphere itself. Since ShardingSphere implements the JDBC interface, standard applications should be functional. We wish to allocate more effort towards enhancing the current version, and therefore will no longer handle such issues. Please read the documentation or provide more effective information when submitting an issue.