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? :-)

The issue is depend on `NEWID()` not on `varbianry`. But it is must be fixed in 2012. — Hamlet Hakobyan, Jan 24 '14 at 11:04
It may be related to `NEWID` being capable of being non-deterministic, but you can see from the successfully sorting alternatives that `VarBinary` has something to do with it. (Tag added nevertheless.) — Mark Hurd, Jan 24 '14 at 11:13
@HamletHakobyan It is definitely not fixed in 2012: you can see the query plans on SEDE. — Mark Hurd, Jan 24 '14 at 11:17
Try to move the subquery from `from`clause to `select`. See this: SELECT b.a ,b.x As ryx ,RAND(b.x) As yx FROM (SELECT x.a, (SELECT CONVERT(varbinary, NEWID()) As x) x FROM (values (1), (1), (1), (2), (5), (1), (6)) as x(a)) b ORDER BY ryx — Hamlet Hakobyan, Jan 24 '14 at 11:21
Thanks for the workarounds. I note a few myself in the SEDE query. I'm looking for confirmation this is just a bug. — Mark Hurd, Jan 24 '14 at 11:43
newid() is buggy. This is bug i found on far 2005 year. `select a from (select abs(checksum(newid())%10) a from sysobjects) b group by a order by 1` This was return repeating value. — Hamlet Hakobyan, Jan 24 '14 at 11:55

score 1 · Accepted Answer · edited Jan 18 '21 at 12:31

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.

edited Jan 18 '21 at 12:31

Community

1
1

answered Jan 24 '14 at 11:08

usr

168,620
35
240
369

Note that you _don't_ actually just get one evaluation multiple times, but it may be the query plan thought that was the case. – Mark Hurd Jan 24 '14 at 11:14
Not sure what you're saying. I just tried this on SEDE (http://data.stackexchange.com/stackoverflow/query/162627/testing-sub-select-order-failure). This particular way of writing the query forces a correct query plan. – usr Jan 24 '14 at 11:21
I was referring to my original query that is not sorted. – Mark Hurd Jan 24 '14 at 11:26

score 0 · Answer 2 · edited Jan 18 '21 at 12:32

0

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.

edited Jan 18 '21 at 12:32

Community

1
1

answered Jan 25 '14 at 02:46

Mark Hurd

10,665
10
68
101

If you look at my previous answer (http://stackoverflow.com/posts/21331107/revisions) you'll see that I also mentioned this. Unfortunately, the question is not about fixing this issue as I later noticed :( I think my answer proves that it is a bug though. The only question is whether it will be recognized by the team as such. – usr Jan 27 '14 at 18:48
@usr You may note I used your outer `SELECT ... ORDER BY` formulation in the bug report. – Mark Hurd Jan 28 '14 at 00:13
The result of my Connect submission is "won't fix". – Mark Hurd Aug 06 '16 at 04:06

Order By VarBinary expression not sorted

2 Answers2