oulexue opened a new issue, #14026:
URL: https://github.com/apache/shardingsphere/issues/14026
### Which version of ShardingSphere did you use?
5.0.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
select sob.region_id regionId, sob.id,sob.sob_name
sobName,min(settle_accounts_date)minSettleMonth,max(settle_accounts_date)
maxSettleMonth,count(sob.id) cm,sob.accounting_criterion_type
accountingCriterionType,sob.sob_enable_date sobEnableDate,
GROUP_CONCAT(concat(settle_accounts_date,'~',record.create_date))
voucherDates,
t1.totalVoucher,
t1.totalVoucherCur from tb_finance_sob sob left join
tb_finance_settle_accounts_record record on sob.id=record.sob_id
and settle_accounts_date >= sob.sob_enable_date and
settle_accounts_date <= ?
left join (
SELECT
sum(case when date_format(v.voucher_date,'%Y-%m') BETWEEN
sob.sob_enable_date and now() then 1 end)totalVoucher,
sum(case when date_format(v.voucher_date,'%Y-%m')
=date_format(now(),'%Y-%m') then 1 end)totalVoucherCur,
sob.id sob_id
FROM
tb_finance_sob sob left join tb_finance_voucher v on
sob.id=v.sob_id
WHERE 1=1
and region_id = ?
GROUP BY
sob.id
)t1 on sob.id=t1.sob_id
where 1=1
and sob.region_id = ?
group by sob.id
having cm>0
limit ?,?
### Actual behavior
Caused by: java.sql.SQLException: Error while preparing statement
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
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at sun.reflect.GeneratedMethodAccessor145.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.$Proxy462.query(Unknown Source)
at sun.reflect.GeneratedMethodAccessor145.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.$Proxy462.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 org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)
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)
... 114 more
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 8,
column 24 to line 8, column 58: No match found for function signature
date_format(<CHARACTER>, <CHARACTER>)
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.type.SqlTypeUtil.deriveType(SqlTypeUtil.java:178)
at
org.apache.calcite.sql.type.InferTypes.lambda$static$0(InferTypes.java:47)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:2054)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:2022)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:2062)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:2042)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:459)
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.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3380)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3362)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3427)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3371)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3644)
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)
... 144 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match
found for function signature date_format(<CHARACTER>, <CHARACTER>)
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)
... 188 more
, 1343370642506567682, 2021-12-10 11:20:44.882]
### Reason analyze (If you can)
### 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).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
