sharding-jdbc 5.0.0 配置多数据源,分片表关联表查询,不走分片表

sharding-jdbc 5.0.0 配置了2个数据源(ds0,ds1),分片表关联表(left join )查询,不走分片表并异常,单数据源sql执行正常,请问有解决办法吗?
分片表:a_account_attention ,分片键: create_time
执行sql:

 SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
        where a.create_time between #{startTime} and #{endTime}

日志如下:
Logic SQL:

ShardingSphere-SQL - log,74 - Logic SQL: SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?  ::: [2022-01-12 00:00:00, 2022-01-12 23:59:59]

Actual SQL:

ShardingSphere-SQL - log,74 - Actual SQL: ds0 ::: SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ? ::: [2022-01-12 00:00:00, 2022-01-12 23:59:59]

异常提示:

org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: java.sql.SQLException: Error while preparing statement [SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?]
### The error may exist in file [D:\DevCodeHome\gb-manage-os\target\classes\cn\greenbon\statistics\dao\TerminalDataMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT         IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,         IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,         IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount         FROM         a_account_attention a         left join operator b on b.id = a.operator_id          WHERE  a.create_time between ? and ?
### Cause: java.sql.SQLException: Error while preparing statement [SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?]
; uncategorized SQLException; SQL state [null]; error code [0]; Error while preparing statement [SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?]; nested exception is java.sql.SQLException: Error while preparing statement [SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy351.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:108)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
	at com.sun.proxy.$Proxy739.countFans(Unknown Source)
	at cn.greenbon.statistics.service.impl.TerminalDataServiceImpl.count(TerminalDataServiceImpl.java:92)
	at cn.greenbon.statistics.service.impl.TerminalDataServiceImpl.lambda$countTerminalData$0(TerminalDataServiceImpl.java:54)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Error while preparing statement [SELECT
        IFNULL( SUM( CASE WHEN a.type = 1 THEN 1 ELSE 0 END ), 0 ) AS bigFans,
        IFNULL( SUM( CASE WHEN a.type = 2 THEN 1 ELSE 0 END ), 0 ) AS littleFans,
        IFNULL( SUM( CASE WHEN a.is_new = 1 THEN 1 ELSE 0 END ), 0 ) AS newAccount
        FROM
        a_account_attention a
        left join operator b on b.id = a.operator_id
         WHERE  a.create_time between ? and ?]
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:218)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:101)
	at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
	at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.execute(OriginalFilterableExecutor.java:84)
	at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:77)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeFederatedQuery(ShardingSpherePreparedStatement.java:223)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:293)
	at sun.reflect.GeneratedMethodAccessor334.invoke(Unknown Source)
	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.$Proxy813.execute(Unknown Source)
	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.GeneratedMethodAccessor333.invoke(Unknown Source)
	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.$Proxy811.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 com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:163)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:90)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	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.GeneratedMethodAccessor392.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:426)
	... 10 more
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 2, column 9 to line 2, column 66: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:902)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5271)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1953)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:326)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6257)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6244)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1867)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1852)
	at org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:133)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6257)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6244)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1867)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1852)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:461)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4414)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3657)
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1098)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1069)
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:247)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1044)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:750)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:585)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:251)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:215)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:647)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229)
	... 41 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506)
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:600)
	... 76 more

sharding-jdbc 配置:

# 数据源配置
dataSources:
  ds0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/gb_manage_os?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&useOldAliasMetadataBehavior=true
    username: root
    password: 123456

  ds1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/gb_wechat_os?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&useOldAliasMetadataBehavior=true
    username: root
    password: 123456

# 分表规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表
    a_account_associate:
      # 真实分片表
      actualDataNodes: ds1.a_account_associate_${2020..2022}0${1..4}
      # 分表策略
      tableStrategy:
        standard:
          # 分片键
          shardingColumn: create_time
          # 分片算法关联
          shardingAlgorithmName: a_account_associate_inline
    # 逻辑表
    a_account_attention:
      # 真实分片表
      actualDataNodes: ds1.a_account_attention_${2020..2022}0${1..4}
      # 分表策略
      tableStrategy:
        standard:
          # 分片键
          shardingColumn: create_time
          # 分片算法关联
          shardingAlgorithmName: a_account_attention_inline

  # 分片算法配置
  shardingAlgorithms:
    a_account_associate_inline:
      # 分片算法类型
      type: INTERVAL
      # 分片算法属性配置
      props:
        datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
        datetime-lower: '2020-01-01 00:00:00'
        datetime-upper: '2022-12-31 23:59:59'
        sharding-suffix-pattern: 'yyyyQQ'
        datetime-interval-amount: '3'
        datetime-interval-unit: 'months'
    a_account_attention_inline:
      # 分片算法类型
      type: INTERVAL
      # 分片算法属性配置
      props:
        datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
        datetime-lower: '2020-01-01 00:00:00'
        datetime-upper: '2022-12-31 23:59:59'
        sharding-suffix-pattern: 'yyyyQQ'
        datetime-interval-amount: '3'
        datetime-interval-unit: 'months'
# sharding-jdbc 属性配置
props:
  # 是否打印sql输出控制台
  sql-show: true
  # 日志中打印简单风格的 SQL
  # sql-simple: true
  # 查询请求在每个数据库实例中所能使用的最大连接数
  max-connections-size-per-query: 4
  # 程序启动和更新时,是否检查分片元数据的结构一致性
  check-table-metadata-enabled: false
  # 在程序启动和更新时,是否检查重复表
  check-duplicate-table-enabled: false
  # 是否解析 SQL 注释
  sql-comment-parse-enabled: false
  # 是否开启 federation 查询
  sql-federation-enabled: true

如果这两张表分片关系一致的话,可以考虑增加绑定表配置,这样就不用走 查询优化的逻辑。查询优化目前是实验性功能,还在快速迭代。

两表不是依靠主键进行关联也可以?

只要路由关系完全一致就可以配置绑定表。
https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/table/#绑定表

京ICP备2021015875号