Essential column SQL orders

Question

I found that there should be an error in the query when it was copied to another (supposedly identical) database, and this failed. It looks something like this:

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 

The “error” is that TableTwo does not have a column named columnTwo (used in the ORDER BY clause), but it works fine. At least this is done in one of the databases, and the other complains. But I'm sure it doesn't have TableTwo.columnTwo.

It might be worth mentioning that TableOne has a column named columnTwo.

This is an easy fix, but it pushes me that it has existed for so long without any problems. Any idea what could go on? (Or more information that I could give?)

Answer 1

WHEN binds a column reference in an ORDER BY list to columns defined in a SELECT list, fuzzy columns are ignored and column prefixes are sometimes ignored. This can cause the result set to return in unexpected order. For example, an ORDER BY clause with a single two-part column (.) That is used as a column reference 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 ORDER BY. The sort operation is not performed on the specified source column (x.c1) as expected; instead, it occurs on the derivative c1 that is specified in the request. The execution plan for this query shows that the values ​​for the derived column are calculated first and that the calculated values ​​are sorted

Source - "MSDN"