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