Use date_format in Query about more Related tables issue.

[wayinit]

SQL:
SELECT tmp.id,
tmk.tb_mem_no mem_no,
tmk.tb_kyc_flag kyc_flag
FROM tb_mem_personal tmp,
tb_mem_kyc tmk,
tb_country tc
WHERE tmk.id = tmp.tb_kyc_id
AND tc.tb_country_code = tmk.tb_credentials_city
AND DATE_FORMAT(tmp.kyc_submit_time, '%Y%m%d') = date_format(now(), '%Y%m%d');
exception stack info:
02:36:06.193 [Connection-6844-ThreadExecutor] DEBUG org.apache.shardingsphere.proxy.frontend.mysql.command.MySQLCommandExecutorFactory - Execute packet type: COM_QUERY, value: MySQLComQueryPacket(sql=/* ApplicationName=DataGrip 2021.3.4 / SELECT tmp.id,
tmk.tb_mem_no mem_no,
tmk.tb_kyc_flag kyc_flag
FROM tb_mem_personal tmp,
tb_mem_kyc tmk,
tb_country tc
WHERE tmk.id = tmp.tb_kyc_id
AND tc.tb_country_code = tmk.tb_credentials_city
AND DATE_FORMAT(tmp.kyc_submit_time, '%Y%m%d') = date_format(now(), '%Y%m%d'))
02:36:06.194 [Connection-6844-ThreadExecutor] DEBUG org.apache.calcite.sql.parser - Reduced tmk.id = tmp.tb_kyc_id AND tc.tb_country_code = tmk.tb_credentials_city AND DATE_FORMAT(tmp.kyc_submit_time, '%Y%m%d') = date_format(now(), '%Y%m%d')
02:36:06.195 [Connection-6844-ThreadExecutor] ERROR org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask - Exception occur:
java.sql.SQLException: Error while preparing statement [/ ApplicationName=DataGrip 2021.3.4 */ SELECT tmp.id,
tmk.tb_mem_no mem_no,
tmk.tb_kyc_flag kyc_flag
FROM tb_mem_personal tmp,
tb_mem_kyc tmk,
tb_country tc
WHERE tmk.id = tmp.tb_kyc_id
AND tc.tb_country_code = tmk.tb_credentials_city
AND DATE_FORMAT(tmp.kyc_submit_time, '%Y%m%d') = date_format(now(), '%Y%m%d')]
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.federation.executor.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:143)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:120)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:67)
at org.apache.shardingsphere.proxy.backend.text.data.impl.SchemaAssignedDatabaseBackendHandler.execute(SchemaAssignedDatabaseBackendHandler.java:55)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:62)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:96)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:69)
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:750)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 9, column 5 to line 9, column 46: No match found for function signature DATE_FORMAT(, )
at sun.reflect.GeneratedConstructorAccessor89.newInstance(Unknown Source)
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:2062)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4342)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4334)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3647)
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)
... 14 common frames omitted
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature DATE_FORMAT(, )
at sun.reflect.GeneratedConstructorAccessor88.newInstance(Unknown Source)
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)
... 47 common frames omitted
02:36:06.196 [epollEventLoopGroup-3-4] DEBUG org.apache.shardingsphere.db.protocol.codec.PacketCodec - Write to client 0e9d588e :

[tristaZero]

@wayinit Hello which release are you testing?

[ideban]

hello we use latest master branch, but the problem is still happen