Order By VarBinary expression not sorted

Question

While attempting to generate a seeded random ordering I noted this issue, now replicated both in SQL Server 2008 R2 (RTM) and on SEDE, i.e. SQL Server 2012 (SP1):

When you ORDER BY a VarBinary sub-SELECT field the ordering does not occur.

In the query plan (of SEDE above) you can see that there is no SORT for:

SELECT [id]
  ,y.x As ryx
  ,RAND(y.x) As yx
FROM #Test, (SELECT CONVERT(varbinary, NEWID()) As x) y
ORDER BY ryx

where as there is for all the other variations I tried (and you can see them in the SEDE query, as well as if you edit them to ORDER BY yx).

I've had a look at MSDN and only confirmed VarBinary use MACHINE collation when indexed.

Is this just a bug or a poorly documented feature? :-)

Answer 1

It is a bug.

Create Table #Test(Id Int NOT NULL)
 INSERT Into #Test VALUES(1)
 INSERT Into #Test VALUES(2)

SELECT @@VERSION

SELECT *
FROM (
    SELECT [id]
        ,y.x As ryx
        ,RAND(y.x) As yx
    FROM #Test, (SELECT CONVERT(varbinary,NEWID()) As x) y
) x
ORDER BY ryx --order by outside!

See https://data.stackexchange.com/stackoverflow/query/162636/testing-sub-select-order-failure?opt.textResults=true&opt.withExecutionPlan=true

Example results:

id          ryx
----------- -----------------------------------
1           0xC15FAED68C9A134882A2C977C46F1B8D --wrong order
2           0x532169D935535543BE0E0B24CA5D04FB --wrong order

In this query the order by is clearly on the outside. It should not matter in what way the derived table x is generated. The order by must apply. The rows are returned in unsorted order.

Report it to Microsoft Connect. It is an optimizer bug.