You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/04/08 02:37:03 UTC
[GitHub] [shardingsphere] wayinit commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format
wayinit commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1092385344
@strongduanmu
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](http://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](http://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](http://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](http://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(<TIMESTAMP>, <CHARACTER>)
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(<TIMESTAMP>, <CHARACTER>)
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 :
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org