Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.10.2
-
None
-
None
Description
Try this query:
SELECT a, ROW_NUMBER() OVER ()
|
FROM (
|
SELECT 'a' a UNION ALL
|
SELECT 'c' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'c' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'b' a
|
) t;
|
It seems to perform a sort, resulting in:
| a | ROW_NUMBER() OVER () |
| a | 1 |
| b | 2 |
| b | 3 |
| b | 4 |
| b | 5 |
| c | 6 |
| c | 7 |
On the other hand, when adding a dummy ORDER BY clause, then the lexical order is preserved:
SELECT a, ROW_NUMBER() OVER (ORDER BY NULL)
|
FROM (
|
SELECT 'a' a UNION ALL
|
SELECT 'c' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'c' a UNION ALL
|
SELECT 'b' a UNION ALL
|
SELECT 'b' a
|
) t;
|
Resulting in:
| a | ROW_NUMBER() OVER (ORDER BY NULL) |
| a | 1 |
| c | 2 |
| b | 3 |
| b | 4 |
| c | 5 |
| b | 6 |
| b | 7 |
I would expect both executions to not touch the ordering of the derived table contents, from a performance perspective. I'm aware that both results are technically correct. I just wanted to make sure there's no performance penalty.