linghengqian opened a new issue, #16857: URL: https://github.com/apache/shardingsphere/issues/16857
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? - 5.1.1 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? - ShardingSphere-JDBC ### Expected behavior - Suppose I have the following YAML configuration to configure `MySQL 5.6`. ```yaml spring: shardingsphere: schema: name: "sharding_db" mode: type: Memory datasource: names: ds-0 ds-0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2b8 username: root password: 123456 rules: sharding: tables: t_order: actual-data-nodes: ds-0.t_order_$->{[20220410..20220415]} tableStrategy: standard: sharding-column: create_date sharding-algorithm-name: lingh-interval sharding-algorithms: lingh-interval: type: INTERVAL props: datetime-pattern: "yyyy-MM-dd HH:mm:ss" datetime-lower: "2022-04-10 00:00:01" datetime-upper: "2022-04-15 23:59:59" sharding-suffix-pattern: "_yyyyMMdd" datetime-interval-amount: 1 datetime-interval-unit: "DAYS" ``` - The table structure of the `t_order` logical table is similar to the following. ```sql create table `t_order_20220410` ( `id` bigint not null comment 'id', `create_time` datetime not null comment 'create_time' ); insert into `t_order_20220410` (id, create_time) values (1, '2022-04-10 07:00:01'), (2, '2022-04-10 11:00:01'), (3, '2022-04-10 17:00:01'); ``` - I want to call an interface method in `Mybatis` that executes the following SQL. ```java @Component public interface TOrderMapper { @Select("select distinct id from t_order where create_date between date_sub(now(), interval 1 hour) and now()") List<Long> selectIdInLastOneHour(); } ``` - This is the normal SQL to fetch all the unique ids in the last 1 hour, I think it should be executed normally. ### Actual behavior -This throws an exception similar to the following. ```shell 2022-04-15 15:34:55.210 DEBUG 12516 --- [nio-7622-exec-1] o.s.web.servlet.DispatcherServlet : Failed to complete request: org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 ### The error may exist in com/lingh/mapper/TOrderMapper.java (best guess) ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: select distinct id from t_order where create_date between date_sub(now(), interval 1 hour) and now() ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 2022-04-15 15:34:55.219 ERROR 12516 --- [nio-7622-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 ### The error may exist in com/lingh/mapper/TOrderMapper.java (best guess) ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: select distinct id from t_order where create_date between date_sub(now(), interval 1 hour) and now() ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1] with root cause java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->{[20220410,20220411,20220412,20220413,20220414' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na] at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:412) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1] at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:408) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:97) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:82) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49) ~[shardingsphere-infra-executor-5.1.1.jar:5.1.1] at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:153) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1] at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:142) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1] at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:376) ~[shardingsphere-jdbc-core-5.1.1.jar:5.1.1] at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.9.jar:3.5.9] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_322] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_322] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_322] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_322] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.7.jar:2.0.7] at com.sun.proxy.$Proxy253.selectList(Unknown Source) ~[na:na] at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.7.jar:2.0.7] at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145) ~[mybatis-3.5.9.jar:3.5.9] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86) ~[mybatis-3.5.9.jar:3.5.9] at com.sun.proxy.$Proxy256.selectIdInLastOneHour(Unknown Source) ~[na:na] ``` ### Reason analyze (If you can) - It looks like ShardingSphere is generating the syntax tree incorrectly, but I'm not familiar with this one. - Another situation is that the properties of printing sql seem to be invalid for me, and the new version has changed? ```yaml spring.shardingsphere.props.sql-slow: true ``` ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. - `select distinct id from t_order where create_date between date_sub(now(), interval 1 hour) and now()` ### Example codes for reproduce this issue (such as a github link). - It is difficult for me to show the use of MySQL functions like `now()` with `H2Database`, because in fact similar configuration is dynamically updated via `DistSQL`. -- 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]
