search

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.94k stars 6.74k forks source link

SQL cannot use Order By to throw An NPE exception on sharding-column #13075

Closed linghengqian closed 3 years ago

linghengqian commented 3 years ago

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?

5.0.0-beta

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

ShardingSphere-JDBC

Expected behavior

The following sql is executed normally.

SELECT tb_result3_id, posid, datadate, inputdate, abnormal
FROM tb_result3_sharding_sphere
WHERE posid = ?
    AND datadate >= ?
    AND datadate < ?
ORDER BY datadate ASC
LIMIT 1

The following is the configuration of application.yml.

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        name: shardingshphere
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?&serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
        maxActive: 400
        initialSize: 1
        maxWait: 6000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20
        asyncInit: true
        filters: stat
        use-global-datasource-stat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    rules:
      sharding:
        tables:
          tb_result3_sharding_sphere:
            actual-data-nodes: ds0.tb_result3_sharding_sphere_$->{0..366}
            table-strategy:
              standard:
                sharding-column: datadate
                sharding-algorithm-name: tb-result3-class-based
        sharding-algorithms:
          tb-result3-class-based:
            type: CLASS_BASED
            props:
              strategy: STANDARD
              algorithmClassName: com.sg8000.config.TbResult3TestShardingAlgorithm
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
  datasource:
    druid:
      web-stat-filter:
        enabled: true
        exclusions: "*.js,*.css,/druid/*"
      stat-view-servlet:
        enabled: true
        url-pattern: "/druid/*"
        login-username: admin
        login-password: 123456
        allow: ""
      filter:
        stat:
          enabled: true
        slf4j:
          enabled: true
          statement-create-after-log-enabled: false
          statement-close-after-log-enabled: false
          result-set-open-after-log-enabled: false
          result-set-close-after-log-enabled: false

The following is the corresponding class of algorithmClassName mentioned in application.yml.

package com.sg8000.config;

import com.google.common.collect.Range;
import lombok.Getter;
import lombok.Setter;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.text.DecimalFormat;
import java.text.ParsePosition;
import java.time.Duration;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Properties;

@Getter
public final class TbResult3ShardingTestAlgorithm implements StandardShardingAlgorithm<Comparable<?>> {
    private static final String DATE_TIME_LOWER_KEY = "datetime-lower";
    private static final String DATE_TIME_UPPER_KEY = "datetime-upper";
    private static final String SHARDING_SECONDS_KEY = "sharding-seconds";
    private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss[.S]");
    private static final String tbResult3ShardingPrefix = "tb_result3_sharding_sphere_";
    @Setter
    private Properties props = new Properties();
    private static final LocalDateTime dateTimeLower = LocalDateTime.parse("2021-01-01 00:00:00", DATE_TIME_FORMAT);
    private static final long shardingSeconds = 86400L;
    private static final int autoTablesAmount = 366;

    @Override
    public void init() {
    }

    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) {
        String tableNameSuffix = String.valueOf(doSharding(parseDate(shardingValue.getValue())));
        for (String each : availableTargetNames) {
            if (each.endsWith(tbResult3ShardingPrefix + tableNameSuffix)) {
                return each;
            }
        }
        return null;
    }

    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Comparable<?>> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        int firstPartition = getFirstPartition(shardingValue.getValueRange());
        int lastPartition = getLastPartition(shardingValue.getValueRange());
        for (int i = firstPartition; i <= lastPartition; i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(tbResult3ShardingPrefix + i)) {
                    result.add(each);
                    break;
                }
                if (result.size() == availableTargetNames.size()) {
                    return result;
                }
            }
        }
        return result;
    }

    private int doSharding(final long shardingValue) {
        String position = new DecimalFormat("0.00").format((float) shardingValue / shardingSeconds);
        return Math.min(Math.max(0, (int) Math.ceil(Float.parseFloat(position))), autoTablesAmount - 1);
    }

    private int getFirstPartition(final Range<Comparable<?>> valueRange) {
        return valueRange.hasLowerBound() ? doSharding(parseDate(valueRange.lowerEndpoint())) : 0;
    }

    private int getLastPartition(final Range<Comparable<?>> valueRange) {
        return valueRange.hasUpperBound() ? doSharding(parseDate(valueRange.upperEndpoint())) : autoTablesAmount - 1;
    }

    private long parseDate(final Comparable<?> shardingValue) {
        LocalDateTime dateValue = LocalDateTime.from(DATE_TIME_FORMAT.parse(shardingValue.toString(), new ParsePosition(0)));
        return Duration.between(dateTimeLower, dateValue).toMillis() / 1000;
    }

    @Override
    public String getType() {
        return null;
    }
}

Actual behavior

An NPE exception was thrown during execution.

java.lang.NullPointerException: null
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables(OrderByValue.java:74) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitive(OrderByValue.java:64) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.<init>(OrderByValue.java:58) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.orderResultSetsToQueue(OrderByStreamMergedResult.java:56) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.<init>(OrderByStreamMergedResult.java:50) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:87) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:65) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:87) ~[shardingsphere-infra-merge-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:77) ~[shardingsphere-infra-merge-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:379) ~[shardingsphere-jdbc-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:324) ~[shardingsphere-jdbc-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:238) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:188) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.7.jar:3.5.7]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64) ~[mybatis-3.5.7.jar:3.5.7]
    at com.sun.proxy.$Proxy306.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.7.jar:3.5.7]
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
    at com.sun.proxy.$Proxy305.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.7.jar:3.5.7]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.sun.proxy.$Proxy201.selectList(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.sun.proxy.$Proxy228.selectList(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.core.mapper.BaseMapper.selectOne(BaseMapper.java:174) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at java.lang.invoke.MethodHandle.invokeWithArguments(MethodHandle.java:627) ~[na:1.8.0_302]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$DefaultMethodInvoker.invoke(MybatisMapperProxy.java:162) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.sun.proxy.$Proxy228.selectOne(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sun.proxy.$Proxy229.selectOne(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.getOne(ServiceImpl.java:202) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.extension.service.IService.getOne(IService.java:239) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl.historySpecificChartDrawing(TbResult3AOImpl.java:48) ~[classes/:na]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl$$FastClassBySpringCGLIB$$3b2cb3b7.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50) ~[dynamic-datasource-spring-boot-starter-3.4.1.jar:3.4.1]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl$$EnhancerBySpringCGLIB$$443a9619.historySpecificChartDrawing(<generated>) ~[classes/:na]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl.historySpecificChartDrawing(TbResult3IngressServiceImpl.java:40) ~[classes/:na]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl$$FastClassBySpringCGLIB$$be459328.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheInterceptor.lambda$invoke$0(CacheInterceptor.java:54) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.invokeOperation(CacheAspectSupport.java:366) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:421) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:345) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheInterceptor.invoke(CacheInterceptor.java:64) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl$$EnhancerBySpringCGLIB$$aebea285.historySpecificChartDrawing(<generated>) ~[classes/:na]
    at com.sg8000.controller.TbResultIngressController.historySpecificChartDrawing(TbResultIngressController.java:35) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681) ~[tomcat-embed-core-9.0.53.jar:4.0.FR]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.53.jar:4.0.FR]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:87) ~[knife4j-spring-3.0.3.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124) ~[druid-1.2.8.jar:1.2.8]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1726) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_302]

Reason analyze (If you can)

Then I tested the Order By DESC version and the Oreder By non-shard key SQL, which also threw an NPE exception.

SELECT tb_result3_id, posid, datadate, inputdate, abnormal
FROM tb_result3_sharding_sphere
WHERE posid = ?
    AND datadate >= ?
    AND datadate < ?
ORDER BY datadate DESC
LIMIT 1
SELECT posid, datadate, inputdate, abnormal
FROM tb_result3_sharding_sphere
WHERE posid = ?
    AND datadate >= ?
    AND datadate < ?
ORDER BY posid ASC
LIMIT 1

Take the error message with order by as a non-sharding key as an example.

java.lang.NullPointerException: null
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables(OrderByValue.java:74) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitive(OrderByValue.java:64) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.<init>(OrderByValue.java:58) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.orderResultSetsToQueue(OrderByStreamMergedResult.java:56) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.<init>(OrderByStreamMergedResult.java:50) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:87) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:65) ~[shardingsphere-sharding-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:87) ~[shardingsphere-infra-merge-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:77) ~[shardingsphere-infra-merge-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:379) ~[shardingsphere-jdbc-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:324) ~[shardingsphere-jdbc-core-5.0.0-beta.jar:5.0.0-beta]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:238) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:188) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.7.jar:3.5.7]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64) ~[mybatis-3.5.7.jar:3.5.7]
    at com.sun.proxy.$Proxy306.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.7.jar:3.5.7]
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
    at com.sun.proxy.$Proxy305.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.7.jar:3.5.7]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.sun.proxy.$Proxy201.selectList(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.sun.proxy.$Proxy228.selectList(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.core.mapper.BaseMapper.selectOne(BaseMapper.java:174) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at java.lang.invoke.MethodHandle.invokeWithArguments(MethodHandle.java:627) ~[na:1.8.0_302]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$DefaultMethodInvoker.invoke(MybatisMapperProxy.java:162) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
    at com.sun.proxy.$Proxy228.selectOne(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sun.proxy.$Proxy229.selectOne(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.getOne(ServiceImpl.java:202) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at com.baomidou.mybatisplus.extension.service.IService.getOne(IService.java:239) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl.historySpecificChartDrawing(TbResult3AOImpl.java:48) ~[classes/:na]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl$$FastClassBySpringCGLIB$$3b2cb3b7.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50) ~[dynamic-datasource-spring-boot-starter-3.4.1.jar:3.4.1]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sg8000.service.shardingSphere.impl.TbResult3AOImpl$$EnhancerBySpringCGLIB$$443a9619.historySpecificChartDrawing(<generated>) ~[classes/:na]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl.historySpecificChartDrawing(TbResult3IngressServiceImpl.java:40) ~[classes/:na]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl$$FastClassBySpringCGLIB$$be459328.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheInterceptor.lambda$invoke$0(CacheInterceptor.java:54) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.invokeOperation(CacheAspectSupport.java:366) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:421) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheAspectSupport.execute(CacheAspectSupport.java:345) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.cache.interceptor.CacheInterceptor.invoke(CacheInterceptor.java:64) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.10.jar:5.3.10]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.10.jar:5.3.10]
    at com.sg8000.service.impl.TbResult3IngressServiceImpl$$EnhancerBySpringCGLIB$$aebea285.historySpecificChartDrawing(<generated>) ~[classes/:na]
    at com.sg8000.controller.TbResultIngressController.historySpecificChartDrawing(TbResultIngressController.java:35) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681) ~[tomcat-embed-core-9.0.53.jar:4.0.FR]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.10.jar:5.3.10]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.53.jar:4.0.FR]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:87) ~[knife4j-spring-3.0.3.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124) ~[druid-1.2.8.jar:1.2.8]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.10.jar:5.3.10]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.10.jar:5.3.10]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1726) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.53.jar:9.0.53]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_302]

At first I thought that my configuration was wrong, but the SQL without Order By was executed normally, such as the following sentence.

SELECT tb_result3_id, posid, datadate, inputdate, abnormal
FROM tb_result3_sharding_sphere
WHERE posid = ?
    AND datadate >= ?
    AND datadate < ?
LIMIT 1

I noticed that this NPE comes from "Map<String, ColumnMetaData> columns = tableMetaData.getColumns();" of "org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables", but I'm not very much for further reasons sure. image

This SQL corresponds to a logical table like the following. According to the settings in application.yml, there are 366 such actual tables.

create table tb_result3_sharding_sphere
(
    tb_result3_id  bigint    not null primary key,
    posid          varchar(20)                              not null,
    datadate       datetime(3)                              not null,
    inputdate      datetime(3) default CURRENT_TIMESTAMP(3) not null on update CURRENT_TIMESTAMP(3),
    abnormal       varchar(20)                              null 
);

A key point may be that the actual table retrieved by ShardingJDBC based on the logical table, the column to be checked by this actual table may be 0 column.

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

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

sandynz commented 3 years ago

Hi @linghengqian , I'm not sure what's wrong for now, looks there's a mistake? tb_result3_sharding_sphere_$->{0..366} means there're 367 shards, seems not match autoTablesAmount = 366.

Could you debug and set breakpoint on OrderByValue.getOrderValuesCaseSensitiveFromTables, when NPE thrown, verify:

And could you supply a demo (and create table sql) to reproduce this issue?

linghengqian commented 3 years ago

Hi @linghengqian , I'm not sure what's wrong for now, looks there's a mistake? means there're shards, seems not match .tb_result3_sharding_sphere_$->{0..366}``367``autoTablesAmount = 366

Could you debug and set breakpoint on , when NPE thrown, verify:OrderByValue.getOrderValuesCaseSensitiveFromTables

  • is null? If it's true, why return null?tableMetaData``schema.get(tableName)
  • what's valuetableName

And could you supply a demo (and create table sql) to reproduce this issue?

Thank you for your answer, it is indeed that my configuration file is wrong. Change

spring:
  shardingsphere:
  rules:
    sharding:
      tables:
        tb_result3_sharding_sphere:
          actual-data-nodes: ds0.tb_result3_sharding_sphere_$->{0..366}

to

spring:
  shardingsphere:
  rules:
    sharding:
      tables:
        tb_result3_sharding_sphere:
          actual-data-nodes: ds0.tb_result3_sharding_sphere_$->{1..366}

The main reason is that when I created the table according to the rules of the custom sharding algorithm, tb_result3_sharding_sphere_0 was not created. The problem has been resolved, I will close this issue.