use mysql reserved word as a column name in hibernate with ShardingSphere-JDBC cause an exception

[lzbjut]

Bug Report

Which version of ShardingSphere did you use?

5.1.2 and 5.4.1

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

ShardingSphere-JDBC

Expected behavior

get the query result

Actual behavior

get the exception: You have an error in your SQL syntax

Reason analyze (If you can)

when use reserved words in sql, whether use it directly or after dot ,shardingsphere-jdbc sql parser throw an exception,but the jdbc can support after dot

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

select ruleitem0_.condition as conditio221 from ruleitem ruleitem0 where ruleitem0_.type=? throws an exception: Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax

this is not consistent with the result when using jdbc instead also this query can be execute on mysql server directly

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

[terrymanu]

Can you provide full stack trace?

[lzbjut]

Can you provide full stack trace?

org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax

at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:65)
at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:46)
at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:47)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:48)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:41)
at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:30)
at com.github.benmanes.caffeine.cache.BoundedLocalCache$BoundedLocalLoadingCache.lambda$new$0(BoundedLocalCache.java:3366)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2039)
at java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1853)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2037)
at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2020)
at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:112)
at com.github.benmanes.caffeine.cache.LocalLoadingCache.get(LocalLoadingCache.java:67)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:47)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:58)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:179)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:148)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:86)

[lzbjut]

this issue is similar with https://github.com/apache/shardingsphere/issues/18385 this sql can be execute correctly by using jdbc directly or connect mysql directly,i think these result should be consistent this problem is trigger by hibernate when an column is named "condition" when the hibernate sql generators a sql like select ruleitem0_.condition as conditio221 from ruleitem ruleitem0 where ruleitem0_.type=?

[terrymanu]

condition is Reserved Word, please around quoting with it.

FYI: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

[lzbjut]

but the problem is, some of my old project use only hibernate ,and column "condition" work fine, when i update my project to shardingsphere-jdbc,i encountered the problem that i can "resolve" using quote.

but is that means i have to check every column i used in my project before using shardingsphere?

or why shardingsphere cannot support sql like tableName. reservedWord ?which it can be supported by jdbc,like this

    HikariDataSource dataSource = createHikariDataSource();
    PreparedStatement preparedStatement1 = dataSource.getConnection().prepareStatement("select table.id  , table.condition from table  where table.type=?");
    preparedStatement1.setString(1,"item");
    preparedStatement1.executeQuery();

[terrymanu]

What we can do is to follow the standard rules by the official documentation.

[lzbjut]

but when connect mysql directly it can work as well

[terrymanu]

I found the problem, when SQL select condition from xxx which can not execute, but if owner added (for example: select xxx.condition from xxx), the sql can execute success on MySQL.

[terrymanu]

The parser engine need to be improve here, does any want to pick up this task?

[AbnerHuang2]

please assign to me. @terrymanu

[strongduanmu]

@AbnerHuang2 Assigned.

[lzbjut]

does this means it only support reserved word:"condition" but dont support others?

[AbnerHuang2]

does this means it only support reserved word:"condition" but dont support others?

there are some reserved words had supported. since there are so many reserved words, we can not test all by now.
actually it is rare to use reserved words in production environment. and Shardingsphere has already covered most scene.