You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/09/25 04:24:03 UTC
[GitHub] [shardingsphere] yy-Eddy opened a new issue #7597: 未分片的情况下语法不支持
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #7597: 未分片的情况下语法不支持
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7597:
URL: https://github.com/apache/shardingsphere/issues/7597#issuecomment-698721406
@yy-Eddy For english only, other languages will not be accepted.
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #7597: 未分片的情况下语法不支持
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7597:
URL: https://github.com/apache/shardingsphere/issues/7597#issuecomment-698721406
@yy-Eddy For english only, other languages will not be accepted.
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #7597: 未分片的情况下语法不支持
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #7597:
URL: https://github.com/apache/shardingsphere/issues/7597
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #7597: 未分片的情况下语法不支持
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #7597:
URL: https://github.com/apache/shardingsphere/issues/7597
----------------------------------------------------------------
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:
users@infra.apache.org