can not find owner from table报错处理

can not find owner from table报错处理

处理方法

直接上处理方法，想看排查过程可以看下一节，欢迎指正
出错sql

 select
        req.*
        from
        (select * from xxx.table where status > 0
        )req
        order by mTime desc, cTime desc

改正报错方法：将req.*改为各个需要的字段名

 select
        req.id as id,
        req.name as name,
        req.status as status,
        req.m_time as mTime,
        req.c_time as cTime
        from
        (select * from xxx.table where status > 0
        )req
        order by mTime desc, cTime desc

解决

排查过程

声明：使用shardingsphere 4.1.1
三个sql对比：
第一个sql：

    select req.* from xxx.table req
    order by m_time desc, c_time desc

第二个sql：

 select
        req.id as id,
        req.name as name,
        req.status as status,
        req.m_time as mTime,
        req.c_time as cTime
        from
        (select * from xxx.table where status > 0
        )req
        order by mTime desc, cTime desc

第三个sql：

 select
        req.*
        from
        (select * from xxx.table where status > 0
        )req
        order by mTime desc, cTime desc

shardingshpere执行过程：

        if (sqlStatement instanceof SelectStatement) {
            return new SelectStatementContext(schemaMetaData, sql, parameters, (SelectStatement) sqlStatement);
        }

均为select语句，所以都会走SelectStatementContext的构造方法

    public SelectStatementContext(final SchemaMetaData schemaMetaData, final String sql, final List<Object> parameters, final SelectStatement sqlStatement) {
        super(sqlStatement);
        tablesContext = new TablesContext(sqlStatement.getSimpleTableSegments());
        groupByContext = new GroupByContextEngine().createGroupByContext(sqlStatement);
        orderByContext = new OrderByContextEngine().createOrderBy(sqlStatement, groupByContext);
        projectionsContext = new ProjectionsContextEngine(schemaMetaData).createProjectionsContext(sql, sqlStatement, groupByContext, orderByContext);
        paginationContext = new PaginationContextEngine().createPaginationContext(sqlStatement, projectionsContext, parameters);
        containsSubquery = containsSubquery();
    }

其中的createProjectionsContext

    public ProjectionsContext createProjectionsContext(final String sql, final SelectStatement selectStatement, final GroupByContext groupByContext, final OrderByContext orderByContext) {
        ProjectionsSegment projectionsSegment = selectStatement.getProjections();
        Collection<Projection> projections = getProjections(sql, selectStatement.getSimpleTableSegments(), projectionsSegment);
        ProjectionsContext result = new ProjectionsContext(projectionsSegment.getStartIndex(), projectionsSegment.getStopIndex(), projectionsSegment.isDistinctRow(), projections);
        result.getProjections().addAll(getDerivedGroupByColumns(projections, groupByContext, selectStatement));
        result.getProjections().addAll(getDerivedOrderByColumns(projections, orderByContext, selectStatement));
        return result;
    }

其中有关于projectionts的生成和对于groupby和orderby的处理

关于projections的生成中,三个sql
sql1:1个shortHandProjectionSegment
sql2:n（n的数目与select列数目相同）个columProjectionSegment
sql3:1个shortHandProjectionSegment

        if (projectionSegment instanceof ShorthandProjectionSegment) {
            return Optional.of(createProjection(tableSegments, (ShorthandProjectionSegment) projectionSegment));
        }
        if (projectionSegment instanceof ColumnProjectionSegment) {
            return Optional.of(createProjection((ColumnProjectionSegment) projectionSegment));
        }

    private Collection<Projection> getDerivedOrderColumns(final Collection<Projection> projections, 
                                                          final Collection<OrderByItem> orderItems, final DerivedColumn derivedColumn, final SelectStatement selectStatement) {
        Collection<Projection> result = new LinkedList<>();
        int derivedColumnOffset = 0;
        for (OrderByItem each : orderItems) {
            if (!containsProjection(projections, each.getSegment(), selectStatement)) {
                result.add(new DerivedProjection(((TextOrderByItemSegment) each.getSegment()).getText(), derivedColumn.getDerivedColumnAlias(derivedColumnOffset++)));
            }
        }
        return result;
    }

在验证是否已经存在projection的函数，即containProjection中

    private boolean containsItemWithoutOwnerInShorthandProjections(final Collection<Projection> projections, final OrderByItemSegment orderItem, final SelectStatement selectStatement) {
        if (!(orderItem instanceof ColumnOrderByItemSegment)) {
            return false;
        }
        if (!((ColumnOrderByItemSegment) orderItem).getColumn().getOwner().isPresent()) {
            for (ShorthandProjection each : getQualifiedShorthandProjections(projections)) {
                if (isSameProjection(each, (ColumnOrderByItemSegment) orderItem, selectStatement)) {
                    return true;
                }
            }
        }
        return false;
    }

for (ShorthandProjection each : getQualifiedShorthandProjections(projections))
在此函数中，第二个sql在getQualifiedShorthandProjections函数中，返回了一个空的集合，所以不用进入isSameProjection函数，而第一个和第三个sql都会进入isSameProjection函数。
因为第二个sql是columProjectionSegment，在each instanceof ShorthandProjection为false

    private Collection<ShorthandProjection> getQualifiedShorthandProjections(final Collection<Projection> projections) {
        Collection<ShorthandProjection> result = new LinkedList<>();
        for (Projection each : projections) {
            if (each instanceof ShorthandProjection && ((ShorthandProjection) each).getOwner().isPresent()) {
                result.add((ShorthandProjection) each);
            }
        }
        return result;
    }

第一个和第三个sql都会进入到isSameProjection函数中

    private boolean isSameProjection(final ShorthandProjection shorthandProjection, final ColumnOrderByItemSegment orderItem, final SelectStatement selectStatement) {
        Preconditions.checkState(shorthandProjection.getOwner().isPresent());
        SimpleTableSegment tableSegment = find(shorthandProjection.getOwner().get(), selectStatement);
        return schemaMetaData.containsColumn(tableSegment.getTableName().getIdentifier().getValue(), orderItem.getColumn().getIdentifier().getValue());
    }

在find函数中

    private SimpleTableSegment find(final String tableNameOrAlias, final SelectStatement selectStatement) {
        for (SimpleTableSegment each : selectStatement.getSimpleTableSegments()) {
            if (tableNameOrAlias.equalsIgnoreCase(each.getTableName().getIdentifier().getValue()) || tableNameOrAlias.equals(each.getAlias().orElse(null))) {
                return each;
            }
        }
        throw new IllegalStateException("Can not find owner from table.");
    }

getSimpleTableSegments函数中

    public Collection<SimpleTableSegment> getSimpleTableSegments() {
        Collection<SimpleTableSegment> result = new LinkedList<>();
        for (TableReferenceSegment each: tableReferences) {
            result.addAll(each.getTables());
        }
        return result;
    }

getTables函数中，由于第一个sql为SimpleTableSegment，第三个sql为SubqueryTableSegment，所以在if (tableFactor.getTable() instanceof SimpleTableSegment)中一个为true，一个为false。所以第三个抛出异常

    public Collection<SimpleTableSegment> getTables() {
        Collection<SimpleTableSegment> tables = new LinkedList<>();
        if (null != tableFactor) {
            if (tableFactor.getTable() instanceof SimpleTableSegment) {
                tables.add((SimpleTableSegment) tableFactor.getTable());
            }
        }
        
        if (!joinedTables.isEmpty()) {
            for (JoinedTableSegment each : joinedTables) {
                if (null != each.getTable()) {
                    tables.add((SimpleTableSegment) each.getTable());
                }
            }
        }
        return tables;
    }