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:05:48 UTC

[GitHub] [shardingsphere] wayinit opened a new issue, #16650: Errors will be reported when using functions in multi table associated queries, like date_format

wayinit opened a new issue, #16650:
URL: https://github.com/apache/shardingsphere/issues/16650

   ### Which version of ShardingSphere did you use?
   ShardingSphere proxy 5.1.0
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-proxy
   
   ### Expected behavior
   show the result
   ### Actual behavior
   got an error
   ### Reason analyze (If you can)
   Errors will be reported when using functions in multi table associated queries, but correct in one table queries.
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   got an error:
   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');
   no error:
   SELECT tmp.id FROM tb_mem_personal tmp where DATE_FORMAT(tmp.kyc_submit_time, '%Y%m%d') = date_format(now(), '%Y%m%d');
   
   


-- 
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.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1092547503

   @wayinit This seems to be a bug in calcite parsing, we will fix this in a later version.


-- 
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

[GitHub] [shardingsphere] tianhao960 commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
tianhao960 commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1245013229

   hi, @strongduanmu , I'd take to try this.


-- 
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

[GitHub] [shardingsphere] tianhao960 commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
tianhao960 commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1248874458

   hi, @wayinit , I've tested on master branch and not reproduce the behavior you describe. could you try the new release like 5.2.0?
   ![1663298463916](https://user-images.githubusercontent.com/1155952/190549818-a3136248-b42c-4491-9642-5cc16fe8479b.jpg)
   


-- 
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

[GitHub] [shardingsphere] strongduanmu commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1246079208

   @tianhao960 Great, I just assign this issue to you.


-- 
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

[GitHub] [shardingsphere] github-actions[bot] commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1272350088

   Hello , this issue has not received a reply for several days.
   This issue is supposed to be closed.


-- 
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

[GitHub] [shardingsphere] github-actions[bot] closed issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format
URL: https://github.com/apache/shardingsphere/issues/16650


-- 
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

[GitHub] [shardingsphere] strongduanmu commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16650:
URL: https://github.com/apache/shardingsphere/issues/16650#issuecomment-1092374651

   @wayinit Can you provide the exception stack info?


-- 
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

[GitHub] [shardingsphere] wayinit commented on issue #16650: Errors will be reported when using functions in multi table associated queries, like date_format

Posted by GitBox <gi...@apache.org>.
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