v8v6 opened a new issue, #36361:
URL: https://github.com/apache/shardingsphere/issues/36361
版本：
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.3.2</version>
</dependency>
sql：
`SELECT DATE_FORMAT(dr.date, '%Y-%m-%d') AS date,
t.staff_name AS staffName,
t.staff_id AS staffId,
t.department_name AS departmentName
FROM (SELECT date FROM JSON_TABLE(
'["2025-08-01","2025-08-02","2025-08-03","2025-08-04"]',
'$[*]' COLUMNS(
date DATE PATH '$'
)
) AS dates) dr
join (select sc.staff_id, sc.staff_name,dc.department_name
from department_config dc
inner join department_staff_config dsc on dc.department_id =
dsc.department_id
inner join staff_config sc on dsc.staff_id = sc.staff_id
where sc.staff_state = 1`
error：
`no viable alternative at input
'SELECTCOUNT(*)FROM(SELECTDATE_FORMAT(dr.date,'%Y-%m-%d')ASdate,t.staff_nameASstaffName,t.staff_idASstaffId,t.department_nameASdepartmentNameFROM(SELECTdateFROMJSON_TABLE'
at line 5, position 31, near [@41,235:244='JSON_TABLE',<346>,5:31]
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:97)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at com.sun.proxy.$Proxy139.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:119)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:84)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:155)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:92)
at com.sun.proxy.$Proxy212.queryStaffPageList(Unknown Source)
at
com.soyoo.lms.user.mapper.StaffConfigMapperTest.testQueryStaffPageList(StaffConfigMapperTest.java:34)
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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
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:366)
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$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
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$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
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:232)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing
statement. Cause:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax: SELECT COUNT(*) FROM (SELECT DATE_FORMAT(dr.date,
'%Y-%m-%d') AS date,
t.staff_name AS staffName,
t.staff_id AS staffId,
t.department_name AS departmentName
FROM (SELECT date FROM JSON_TABLE(
'["2025-08-01","2025-08-02","2025-08-03","2025-08-04"]',
'$[*]' COLUMNS(
date DATE PATH '$'
)
) AS dates) dr
join (select sc.staff_id, sc.staff_name,dc.department_name
from department_config dc
inner join department_staff_config dsc on dc.department_id =
dsc.department_id
inner join staff_config sc on dsc.staff_id = sc.staff_id
where sc.staff_state = 1 ) t
order by dr.date desc) TOTAL, no viable alternative at input
'SELECTCOUNT(*)FROM(SELECTDATE_FORMAT(dr.date,'%Y-%m-%d')ASdate,t.staff_nameASstaffName,t.staff_idASstaffId,t.department_nameASdepartmentNameFROM(SELECTdateFROMJSON_TABLE'
at line 5, position 31, near [@41,235:244='JSON_TABLE',<346>,5:31]
at
org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:99)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:60)
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.apache.ibatis.plugin.Invocation.proceed(Invocation.java:61)
at
com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
at com.sun.proxy.$Proxy314.prepare(Unknown Source)
at
org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:90)
at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:64)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
at
com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:135)
at
com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:75)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
at com.sun.proxy.$Proxy312.query(Unknown Source)
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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy312.query(Unknown Source)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
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:425)
... 41 more
Caused by:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax: SELECT COUNT(*) FROM (SELECT DATE_FORMAT(dr.date,
'%Y-%m-%d') AS date,
t.staff_name AS staffName,
t.staff_id AS staffId,
t.department_name AS departmentName
FROM (SELECT date FROM JSON_TABLE(
'["2025-08-01","2025-08-02","2025-08-03","2025-08-04"]',
'$[*]' COLUMNS(
date DATE PATH '$'
)
) AS dates) dr
join (select sc.staff_id, sc.staff_name,dc.department_name
from department_config dc
inner join department_staff_config dsc on dc.department_id =
dsc.department_id
inner join staff_config sc on dsc.staff_id = sc.staff_id
where sc.staff_state = 1 ) t
order by dr.date desc) TOTAL, no viable alternative at input
'SELECTCOUNT(*)FROM(SELECTDATE_FORMAT(dr.date,'%Y-%m-%d')ASdate,t.staff_nameASstaffName,t.staff_idASstaffId,t.department_nameASdepartmentNameFROM(SELECTdateFROMJSON_TABLE'
at line 5, position 31, near [@41,235:244='JSON_TABLE',<346>,5:31]
at
org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:68)
at
org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:47)
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.LocalLoadingCache.lambda$newMappingFunction$2(LocalLoadingCache.java:145)
at
com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2406)
at
java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1853)
at
com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2404)
at
com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2387)
at
com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:108)
at
com.github.benmanes.caffeine.cache.LocalLoadingCache.get(LocalLoadingCache.java:56)
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:205)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:171)
at
org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:87)
at
com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at
com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
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.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:53)
at com.sun.proxy.$Proxy316.prepareStatement(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:88)
at
org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:90)
... 73 more`
--
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-unsubscr...@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
