Unknown column 'MICROSECOND' in 'unknown clause'

[chenguo-design]

Which version of ShardingSphere did you use?

5.5.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Actual behavior

The following error occurred：
org.apache.shardingsphere.infra.exception.kernel.metadata.ColumnNotFoundException: Unknown column 'MICROSECOND' in 'unknown clause'. at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.lambda$findInputColumnSegment$3(ColumnSegmentBinder.java:161) at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.findInputColumnSegment(ColumnSegmentBinder.java:160) at org.apache.shardingsphere.infra.binder.segment.expression.impl.ColumnSegmentBinder.bind(ColumnSegmentBinder.java:82) at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:81) at org.apache.shardingsphere.infra.binder.segment.expression.impl.FunctionExpressionSegmentBinder.bind(FunctionExpressionSegmentBinder.java:52) at org.apache.shardingsphere.infra.binder.segment.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:84) at org.apache.shardingsphere.infra.binder.segment.assign.AssignmentSegmentBinder.bindValue(AssignmentSegmentBinder.java:73) at org.apache.shardingsphere.infra.binder.segment.assign.AssignmentSegmentBinder.bind(AssignmentSegmentBinder.java:57) at org.apache.shardingsphere.infra.binder.statement.dml.UpdateStatementBinder.lambda$bind$0(UpdateStatementBinder.java:55) at java.util.Optional.ifPresent(Optional.java:159) at org.apache.shardingsphere.infra.binder.statement.dml.UpdateStatementBinder.bind(UpdateStatementBinder.java:55) at org.apache.shardingsphere.infra.binder.statement.dml.UpdateStatementBinder.bind(UpdateStatementBinder.java:43) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:88) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:72) at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:63) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:207) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:172) at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:94) at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:235) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:981) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:328) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:333) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.lambda$execute$0(JdbcTemplateStorageAccessor.java:117) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.execute(JdbcTemplateStorageAccessor.java:117) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.updateRecord(JdbcTemplateStorageAccessor.java:82) at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:93) at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:65)

Reason analyze (If you can)

my project is spring project, i used @Scheduler and @SchedulerLock annotation in my project, which will automatically generate statements like 'UPDATE shedlock SET lock_until = TIMESTAMPADD(MICROSECOND, ?, UTC_TIMESTAMP(3)), locked_at = UTC_TIMESTAMP(3), locked_by = ? WHERE name = ? AND lock_until <= UTC_TIMESTAMP(3) ::: [120000000, cg, TestJob]', and shardingshpre-jdbc misidentify the 'MICROSECOND' as a column, then throw this exception

Example codes for reproduce this issue (such as a github link).

[strongduanmu]

Hi @chenguo-design, thank you for your feedback, can you provide your configuration yaml?

[chenguo-design]

Hi @chenguo-design, thank you for your feedback, can you provide your configuration yaml?

the configuration yaml is below, for privacy reasons, i delete the content in tables and bindingTables

  ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: $${sharding.config.driverClassName::}
    jdbcUrl: $${sharding.config.ds.jdbcUrl::}
    username: $${sharding.config.ds.username::}
    password: $${sharding.config.ds.password::}
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: $${sharding.config.maxPoolSize::}
    minPoolSize: $${sharding.config.minPoolSize::}
  ds_his:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: $${sharding.config.driverClassName::}
    jdbcUrl: $${sharding.config.ds_his.jdbcUrl::}
    username: $${sharding.config.ds_his.username::}
    password: $${sharding.config.ds_his.password::}
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: $${sharding.config.maxPoolSize::}
    minPoolSize: $${sharding.config.minPoolSize::}
rules:
- !SINGLE
  tables:
    - ds.*
- !SHARDING
  tables:
  bindingTables:
  defaultDatabaseStrategy:
    standard:
      shardingColumn: archived
      shardingAlgorithmName: default_database_inline
  shardingAlgorithms:
    default_database_inline:
      type: INLINE
      props:
        algorithm-expression: ds${archived==0?'':'_his'}
props:
  sql-show: ${spring.db.show-sql:false}

[strongduanmu]

@chenguo-design Please format the config with markdown.

[chenguo-design]

The database I am using is mysql， and i found that using oracle does not have this issue（because oracle does not use the timestampadd() function）