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