ShardingSphere5.1.0 聚合查询报错

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

ShardingJdbc5.1.0 + MybatisPlus + mysql5.6

场景、问题:

分表: transaction,transaction2022,transaction2023: 分片:billing_date (这个查询不需要走分片)

已进行操作:

原始SQL:
SELECT

    SUM(IFNULL(t.amount, 0)) total,
    SUM(IFNULL(t.cash_amount, 0)) cashTotal,
    SUM(IFNULL(t.coupon_amount, 0)) couponTotal
    FROM
    billing_transaction t
    WHERE transaction_status = 'Success'
    AND transaction_time >= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s')
    AND transaction_time <= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s') 

现状:

日志执行sql:
SELECT

    SUM(IFNULL(t.amount, 0)) total,
    SUM(IFNULL(t.cash_amount, 0)) cashTotal,
    SUM(IFNULL(t.coupon_amount, 0)) couponTotal
    FROM
    billing_transaction t
    WHERE transaction_status = 'Success'
    AND transaction_time >= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s')
    AND transaction_time <= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s') UNION ALL SELECT

    SUM(IFNULL(t.amount, 0)) total,
    SUM(IFNULL(t.cash_amount, 0)) cashTotal,
    SUM(IFNULL(t.coupon_amount, 0)) couponTotal
    FROM
    billing_transaction2022 t
    WHERE transaction_status = 'Success'
    AND transaction_time >= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s')
    AND transaction_time <= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s') UNION ALL SELECT

    SUM(IFNULL(t.amount, 0)) total,
    SUM(IFNULL(t.cash_amount, 0)) cashTotal,
    SUM(IFNULL(t.coupon_amount, 0)) couponTotal
    FROM
    billing_transaction2023 t
    WHERE transaction_status = 'Success'
    AND transaction_time >= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s')
    AND transaction_time <= FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s') ::: [1648742400, 1653926399, 1648742400, 1653926399, 1648742400, 1653926399]

报错信息:

Error querying database. Cause: java.lang.NullPointerException

The error may exist in class path resource [mapper/TransactionMapper.xml]

The error may involve com.pingan.cmp.billing.mapper.TransactionMapper.getTotal

The error occurred while handling results

SQL: SELECT SUM(IFNULL(t.amount, 0)) total, SUM(IFNULL(t.cash_amount, 0)) cashTotal, SUM(IFNULL(t.coupon_amount, 0)) couponTotal FROM billing_transaction t WHERE transaction_status = ‘Success’ AND transaction_time >= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’) AND transaction_time <= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’)

Cause: java.lang.NullPointerException

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

Error querying database. Cause: java.lang.NullPointerException

The error may exist in class path resource [mapper/TransactionMapper.xml]

The error may involve com.pingan.cmp.billing.mapper.TransactionMapper.getTotal

The error occurred while handling results

SQL: SELECT SUM(IFNULL(t.amount, 0)) total, SUM(IFNULL(t.cash_amount, 0)) cashTotal, SUM(IFNULL(t.coupon_amount, 0)) couponTotal FROM billing_transaction t WHERE transaction_status = ‘Success’ AND transaction_time >= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’) AND transaction_time <= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’)

Cause: java.lang.NullPointerException

    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy329.selectOne(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
    at com.sun.proxy.$Proxy338.getTotal(Unknown Source)
    at com.pingan.cmp.billing.service.impl.TransactionServiceImpl.getTotal(TransactionServiceImpl.java:1344)
    at com.pingan.cmp.billing.service.impl.TransactionServiceImpl$$FastClassBySpringCGLIB$$9757a4f.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
    at com.pingan.cmp.billing.service.impl.TransactionServiceImpl$$EnhancerBySpringCGLIB$$69d7110d.getTotal(<generated>)
    at com.pingan.cmp.billing.controller.TransactionController.getTotal(TransactionController.java:362)
    at com.pingan.cmp.billing.controller.TransactionController$$FastClassBySpringCGLIB$$58d6e565.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)

Error querying database. Cause: java.lang.NullPointerException

The error may exist in class path resource [mapper/TransactionMapper.xml]

The error may involve com.pingan.cmp.billing.mapper.TransactionMapper.getTotal

The error occurred while handling results

SQL: SELECT SUM(IFNULL(t.amount, 0)) total, SUM(IFNULL(t.cash_amount, 0)) cashTotal, SUM(IFNULL(t.coupon_amount, 0)) couponTotal FROM billing_transaction t WHERE transaction_status = ‘Success’ AND transaction_time >= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’) AND transaction_time <= FROM_UNIXTIME(?,’%Y-%m-%d %H:%i:%s’)

Cause: java.lang.NullPointerException

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
    at sun.reflect.GeneratedMethodAccessor307.invoke(Unknown Source)
    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:433)
    ... 146 common frames omitted

Caused by: java.lang.NullPointerException: null
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:134)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitive(GroupByMemoryMergedResult.java:124)
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:51)
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.(GroupByMemoryMergedResult.java:56)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.getGroupByMergedResult(ShardingDQLResultMerger.java:112)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:82)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:63)
at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:90)
at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:80)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:446)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:396)

transaction transaction2022 transaction2023表数据都是空的

我好像也遇到这个报错,楼主解决了吗

也许更新到5.1.1再测一遍更好说明问题.

京ICP备2021015875号