yy-Eddy opened a new issue #7597:
URL: https://github.com/apache/shardingsphere/issues/7597
## Bug Report
项目是spring + mybatis， 集成ShardingSphere-JDBC后，发现有一个sql运行报错 (语句在最后面 ,请忽略sql写的烂
嘿嘿) ；
复现顺序:
当我把dataSoruce切换为ShardingSphere-JDBC时,sql会被拦截解析, 但是解析异常 [在order by解析那里抛出来的].
如果还是默认用mybatis的数据源则无问题.
sql语句中的表未进行任何分库分表，读写分离等配置
个人总结:
在未进行设置分库分表下 语句解析和mybatis出现了不兼容
### Which version of ShardingSphere did you use?
版本是: 4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
使用的: ShardingSphere-JDBC
sql是:
SELECT r2.* FROM
(
SELECT
r1.*,ncb.base_name,ncb.base_photo,
if
(newest is null,1,
if((UNIX_TIMESTAMP('2020-09-25 11:59:11') -
UNIX_TIMESTAMP(newest)) > r1.cooling_time,1,2)
) as isCooling
FROM
(
SELECT
(
SELECT
count(id)
FROM
user_tags
WHERE
user_id = 18960
AND
FIND_IN_SET(tags_id, nc.game_tags_id)
) ut_count,
(
SELECT
count(id)
FROM
npc_receive
WHERE
user_id = 18960
AND npc_card_id = nc.id
AND
str_to_date(receive_time, '%Y-%m-%d') = str_to_date('2020-09-25 11:59:11',
'%Y-%m-%d')
) AS receive_daily,
(
SELECT
count(id)
FROM
npc_receive
WHERE
user_id = 18960
AND npc_card_id
= nc.id
) AS receive_total,
(
SELECT
count(id)
FROM
npc_receive
WHERE
npc_card_id =
nc.id
) AS receive_card_total,
(
SELECT
receive_time
FROM
npc_receive
WHERE
user_id = 18960
AND npc_card_id
= nc.id
ORDER BY
receive_time
desc
LIMIT 1
) AS newest,
nc.id,
nc.npc_base_id,
nc.daily_times,
nc.total_times,
nc.card_end_time,
nc.cooling_time,
nc.total_card_num
FROM
npc_card nc
WHERE
npc_base_id IN (
3
,
4
,
5
,
6
,
9
)
AND card_start_time < '2020-09-25
11:59:11'
AND card_end_time > '2020-09-25
11:59:11'
) AS r1 left join npc_base ncb on
r1.npc_base_id = ncb.id
WHERE
r1.ut_count > 0
AND r1.receive_daily < r1.daily_times
AND r1.receive_total < r1.total_times
AND r1.receive_card_total < r1.total_card_num
) as r2
where r2.isCooling = 1
ORDER BY
r2.card_end_time DESC,
r2.id asc
LIMIT 0,10
错误的堆栈信息是:
Cause: java.lang.IllegalStateException: Can not find owner from table.
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
at sun.reflect.GeneratedMethodAccessor399.invoke(Unknown Source)
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:355)
... 60 more
Caused by: java.lang.IllegalStateException: Can not find owner from table.
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.find(ProjectionsContextEngine.java:197)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.findShorthandProjection(ProjectionsContextEngine.java:139)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsItemWithOwnerInShorthandProjections(ProjectionsContextEngine.java:135)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsItemInShorthandProjection(ProjectionsContextEngine.java:121)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsProjection(ProjectionsContextEngine.java:105)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.getDerivedOrderColumns(ProjectionsContextEngine.java:96)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.getDerivedOrderByColumns(ProjectionsContextEngine.java:88)
at
org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.createProjectionsContext(ProjectionsContextEngine.java:71)
at
org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.<init>(SelectStatementContext.java:99)
at
org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:103)
at
org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:87)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:99)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
at
org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
at
org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
at
org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
at
org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
