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
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?
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.
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.
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.
16 People are following this question.
