Cause: java.lang.IllegalStateException: Can not find owner from table.异常排查
这是一个省市区三级查询的SQL,需要认证成功才能查。cm_authorized_region_info 是认证表 bm_region_def 是基础的省市区信息表
原SQL:
SELECT
p.rd_id AS p_id,
p.rd_short_name AS p_name,
c.rd_id AS c_id,
c.rd_name AS c_name,
z.rd_id AS z_id,
z.rd_name AS z_name
FROM
(SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) p,
(SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) c,
(SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) z
WHERE
p.rd_id= c.rd_parent_id
AND c.rd_id= z.rd_parent_id
AND p.rd_is_delete = 0
AND p.ari_is_delete = 0
AND c.rd_is_delete = 0
AND c.ari_is_delete = 0
AND z.rd_is_delete = 0
AND z.ari_is_delete = 0
AND p.rd_type = 1
- navicat执行正常，在项目中跑就会出现异常信息（可能跟多数据源框架有关系）：
### SQL: SELECT p.rd_id AS p_id, p.rd_short_name AS p_name, c.rd_id AS c_id, c.rd_name AS c_name, z.rd_id AS z_id, z.rd_name AS z_name FROM (SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) p, (SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) c, (SELECT * FROM cm_authorized_region_info,bm_region_def WHERE ari_id = rd_id) z WHERE p.rd_id= c.rd_parent_id AND c.rd_id= z.rd_parent_id AND p.rd_is_delete = 0 AND p.ari_is_delete = 0 AND c.rd_is_delete = 0 AND c.ari_is_delete = 0 AND z.rd_is_delete = 0 AND z.ari_is_delete = 0 AND p.rd_type = ?
### Cause: java.lang.IllegalStateException: Can not find owner from table.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
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:433)
... 38 more
Caused by: java.lang.IllegalStateException: Can not find owner from table.
at org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext.findTableNameFromSQL(TablesContext.java:96)
at org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext.findTableName(TablesContext.java:68)
at org.apache.shardingsphere.sharding.route.engine.condition.engine.WhereClauseShardingConditionEngine.createRouteValueMap(WhereClauseShardingConditionEngine.java:101)
at org.apache.shardingsphere.sharding.route.engine.condition.engine.WhereClauseShardingConditionEngine.createShardingConditions(WhereClauseShardingConditionEngine.java:89)
at org.apache.shardingsphere.sharding.route.engine.condition.engine.WhereClauseShardingConditionEngine.createShardingConditions(WhereClauseShardingConditionEngine.java:73)
at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.getShardingConditions(ShardingRouteDecorator.java:82)
at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:62)
at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91)
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:64)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
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:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 44 more
原因：
有子查询时会生成一个临时表，然后select 后面的字段就是临时表的全部字段，where之所以不会因为通过匿名点字段报错可能是因为where条件是在临时表创建出来之前执行的，group by或者order by是在临时表创建出来之后执行的，这个时候临时表实际并不存在了所以找不到
来自于：https://blog.csdn.net/wcy1900353090/article/details/106442091
修改之后，正常：
SELECT
p.rd_id AS p_id,
p.rd_short_name AS p_name,
c.rd_id AS c_id,
c.rd_name AS c_name,
z.rd_id AS z_id,
z.rd_name AS z_name
FROM
(SELECT * FROM yld_organization_info.cm_authorized_region_info inner join yld_organization_info.bm_region_def on ari_id = rd_id where rd_type = 1 and rd_is_delete = 0 and ari_is_delete = 0) p
INNER JOIN
(SELECT * FROM yld_organization_info.cm_authorized_region_info inner join yld_organization_info.bm_region_def on ari_id = rd_id where ari_is_delete = 0 and rd_is_delete = 0) c on p.rd_id= c.rd_parent_id
INNER JOIN
(SELECT * FROM yld_organization_info.cm_authorized_region_info inner join yld_organization_info.bm_region_def on ari_id = rd_id where ari_is_delete = 0 and rd_is_delete = 0) z on c.rd_id= z.rd_parent_id
