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;
}