apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.95k stars 6.74k forks source link

SQL parsing error #24047

Closed qingdaoheze closed 3 months ago

qingdaoheze commented 1 year ago

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

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

Parsing SQL successfully

Actual behavior

Parsing SQL error

Reason analyze (If you can)

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

I can use the generated query below to execute on sql client successfully. Preparing:

select t.id, GROUP_CONCAT(g.name SEPARATOR ',') as groups from big_customer_team t inner join big_customer_team_group tg on t.id = tg.user_id inner join big_customer_group g on g.id = tg.group_id group by t.id

Sharding config:

rules:
  - !READWRITE_SPLITTING # 配置读写分离规则
    dataSources:
      ds-0:
        type: Static
        props:
          write-data-source-name: ds-0
          read-data-source-names: ds-1
          loadBalancerName: roundRobin
    loadBalancers:
      roundRobin:
        type: ROUND_ROBIN
props:
  sql-show: true

This is the error raised.

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax

    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    at com.sun.proxy.$Proxy366.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
    at com.sun.proxy.$Proxy371.select(Unknown Source)
    at com.abc.crm.vip.web.mapper.TeamMapperTest.triggerArchiveOldCampaigns(TeamMapperTest.java:28)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
    at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:186)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy451.prepare(Unknown Source)
    at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92)
    at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:155)
    at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:90)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
    ... 39 more
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
    at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2214)
    at com.google.common.cache.LocalCache.get(LocalCache.java:4053)
    at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:4057)
    at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4986)
    at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4992)
    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:180)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:149)
    at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:80)
    at com.p6spy.engine.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:118)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
    at com.sun.proxy.$Proxy169.prepareStatement(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
    ... 61 more
Caused by: 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.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3628)
    at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2336)
    at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2295)
    at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2208)
    ... 79 more

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

RaigorJiang commented 1 year ago

@qingdaoheze What database protocol are you using? MySQL or PostgreSQL or someone else?

qingdaoheze commented 1 year ago

mariadb

qingdaoheze commented 1 year ago
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
                         <version>2.5.4</version>
        </dependency>
terrymanu commented 3 months ago

The SQL parser is improved in each version, please test the latest version.