question

virtualjosh avatar image
virtualjosh asked

Does UNION ALL have an ORDER BY Bug?

Is this a Bug in the ORDER BY? The UNION behaves as expected, whereas the UNION ALL does not. I know UNION has an additional step where it gets rid of duplicates; but I can't understand why they sort differently. Does anyone have an explanation for this? -- UNION ALL ORDER BY Bug SELECT SPACE(1) AS [sp], 1 UNION ALL SELECT SPACE(2), 2 UNION ALL SELECT SPACE(5), 5 UNION ALL SELECT SPACE(4), 4 UNION ALL SELECT SPACE(0), 0 ORDER BY [sp] SELECT SPACE(1) AS [sp], 1 UNION SELECT SPACE(2), 2 UNION SELECT SPACE(5), 5 UNION SELECT SPACE(4), 4 UNION SELECT SPACE(0), 0 ORDER BY [sp] ![alt text][1] [1]: /storage/temp/2163-union-all-order-by-bug.jpg
tsqlunionunion-allorder
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I know the solution to this is use an integer as a row number, instead of whitespace. It is not a big deal to fix, but the question is more out of curiosity. What is going on and why is it not behaving as the UNION?
0 Likes 0 ·
This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
I seem to recall that SQL Server ignores trailing spaces for the purposes of most comparison operations, i.e. 'abc' = 'abc '. If you check the execution plan, this holds out - no sort is performed. SQL recognises that each column 1 value is effectively the same for comparision purposes and so just runs a constant scan. I suspect that the sort order for the union query is being enforced by having to merge join each item.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

`+1` yep, sort is the by product of the merge join
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
SPACE() returns varchar, and varchar ignores trailing spaces for comparison purposes (as @iainrobertson correctly states) select len(space(10)) -- => returns 0 so the rows all have the 'same' value in column `sp`. Since no order is specified beyond this column, then the rows can, and will be returned in any order.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.