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.

Answer 2

On the way to adding this to Connect, I note NEWID() is dangerous in expressions, as it is "by design" that expressions are re-evaluated every time they're accessed, not just once per "local" row.

This is further discussed here, where it is explained the designers of SQL Server decided to go with optimisation over accuracy (the opposite of most programming language compilers).

So, before I see an actual result from my Connect submission, I'm guessing this is the result of slightly over-optimising when the conversion to VarBinary is in the "wrong" place.

Specifically, this works:

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

where as usr's query in his answer doesn't.