You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "daiqiang (via GitHub)" <gi...@apache.org> on 2024/12/13 08:20:29 UTC
[I] table's alias is the keyword [shardingsphere]
daiqiang opened a new issue, #34039:
URL: https://github.com/apache/shardingsphere/issues/34039
## Bug Report
**For English only**, other languages will not accept.
my sql parsing failure
`select ti.user_name from my_user ti where user_name = 'xxx'`
it throws a Execption:
`org.apache.shardingsphere.infra.exception.dialect.exception.syntax.sql.DialectSQLParsingException: You have an error in your SQL syntax: no viable alternative at input 'ti'
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.getException(ShardingSphereSQLParserEngine.java:66)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:59)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.parseSQL(ShardingSpherePreparedStatement.java:164)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:145)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:117)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:141)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
at com.sun.proxy.$Proxy70.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at com.sun.proxy.$Proxy71.sql_s14(Unknown Source)`
### Which version of ShardingSphere did you use?
shardingSphere-5.5.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Which DB
oracle
### Expected behavior
parse SQL sucess
### Actual behavior
`org.apache.shardingsphere.infra.exception.dialect.exception.syntax.sql.DialectSQLParsingException: You have an error in your SQL syntax: no viable alternative at input 'ti'`
### Reason analyze (If you can)
I analyzed this bug , I fined the word "ti" is the keyword in "oralce/Keyword.g4",but I don't know how the Keyword.g4 file affects SQL parsing. In order for the SQL to be parsed correctly, I had to remove the keyword "ti" from the Keyword.g4 file.I have handled many such SQLs in the same way. until I discovered that this is not a good way.
please somebody who can tell me, if don't remove keyword "ti" or don't modify my table's alias, what can I do to resovle this question?
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
skip
### Example codes for reproduce this issue (such as a github link).
`select ti.user_name from my_user ti where user_name = 'xxx'`
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #34039: table's alias is the keyword
URL: https://github.com/apache/shardingsphere/issues/34039
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-4556421682
Hi @daiqiang, thanks for the follow-up.
This issue has already been fixed by #34945: https://github.com/apache/shardingsphere/pull/34945
The fix was merged on 2025-03-25 as commit `41c12ef678465c29a396f438dfbe39a36206806b` and is included in ShardingSphere 5.5.3. From the project side, I also verified on current master that the reported SQL can be parsed successfully:
```sql
select ti.user_name from my_user ti where user_name = 'xxx';
```
Based on the current evidence, there is no further code action needed for the original 5.5.1 issue. Please upgrade to 5.5.3 or a later version and verify it in your environment. If the same SQL still fails on 5.5.3+, please provide a new minimal reproducible case based on that version, including the exact ShardingSphere version, configuration, SQL, and full exception stack.
Otherwise, I suggest closing this issue as fixed by #34945. The `status: volunteer wanted` label can also be removed, because this is no longer an open implementation task unless a new reproduction is provided on a fixed 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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "daiqiang (via GitHub)" <gi...@apache.org>.
daiqiang commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-2540850029
I analyzed this bug , I fined the word "ti" is the keyword in "oralce/Keyword.g4",but I don't know how the Keyword.g4 file affects SQL parsing. In order for the SQL to be parsed correctly, I had to remove the keyword "ti" from the Keyword.g4 file.I have handled many such SQLs in the same way. until I discovered that this is not a good way.
please somebody who can tell me, if don't remove keyword "ti" or don't modify my table's alias, what can I do to resovle this question?
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "daguimu (via GitHub)" <gi...@apache.org>.
daguimu commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-4306139174
Hi, I took a look at this on current master (commit 6ad057d4). It appears that `TI` is already included in the `unreservedWord` rule in `parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4` (around line 85), and `alias` resolves through `identifier → unreservedWord`, so `select ti.user_name from my_user ti where user_name = 'xxx'` should parse on master without modifying `Keyword.g4`.
Could you verify whether this still reproduces on a recent master build or 5.5.3+? If it is already fixed I would be happy to open a small PR adding dedicated keyword-as-alias regression cases to `select.xml` (covering `ti`, `tab`, `type`, `time`, etc.) to guard against future regressions. If it still fails, a minimal repro on current master would help narrow down what is missing.
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "daiqiang (via GitHub)" <gi...@apache.org>.
daiqiang commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-4332642496
> Hi, I took a look at this on current master (commit [6ad057d](https://github.com/apache/shardingsphere/commit/6ad057d4b436bbe0abf6c98a4314a40652d65d94)). It appears that `TI` is already included in the `unreservedWord` rule in `parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4` (around line 85), and `alias` resolves through `identifier → unreservedWord`, so `select ti.user_name from my_user ti where user_name = 'xxx'` should parse on master without modifying `Keyword.g4`.
>
> Could you verify whether this still reproduces on a recent master build or 5.5.3+? If it is already fixed I would be happy to open a small PR adding dedicated keyword-as-alias regression cases to `select.xml` (covering `ti`, `tab`, `type`, `time`, etc.) to guard against future regressions. If it still fails, a minimal repro on current master would help narrow down what is missing.
It should have been fixed in version 5.5.3. However, I haven't tested it in practice, as I no longer have a local environment available and cannot perform verification for now.
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "daiqiang (via GitHub)" <gi...@apache.org>.
daiqiang commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-2567209825
> Hi @daiqiang , did you solve the problem? You can try to add back quotes (`) around the keywords.
yes，I try to use quotes to solve this problem，but it‘s not a right way，the SQL still throws an error.
I found baseRule.g4 , this file has so many keyword . I add keyword in baseRule.g4，then my SQL can work actually。 it’s like a whiteList.
--
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
Re: [I] table's alias is the keyword [shardingsphere]
Posted by "yx9o (via GitHub)" <gi...@apache.org>.
yx9o commented on issue #34039:
URL: https://github.com/apache/shardingsphere/issues/34039#issuecomment-2567198198
Hi @daiqiang , did you solve the problem? You can try to add back quotes (`) around the keywords.
--
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