SQL 按不存在的列排序

Question

我发现当一个查询被复制到另一个(假设是相同的)数据库并且它失败时，应该是查询中的错误。大概是这样

SELECT  a.columnOne ,
    b.columnOne
FROM TableOne a
INNER JOIN TableTwo b
    ON a.id = b.id
WHERE a.Value = 0
ORDER BY a.ColumnOne ,
    b.ColumnTwo

“错误”是 TableTwo 没有名为 columnTwo 的列(在 ORDER BY 子句中使用)，但它运行良好。至少它在一个数据库上是这样，另一个数据库则按原样提示。但我确定两者都没有 TableTwo.columnTwo。

可能值得一提的是，TableOne 确实有一个名为 columnTwo 的列。

这是一个简单的修复，但让我烦恼的是它已经存在了这么久，没有任何问题。知道会发生什么吗？ (或者我可以提供更多信息？)

Answer 1

WHEN binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order. For example, an ORDER BY clause with a single two-part column (.) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query. SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1 When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted

来源——“MSDN”