Hi,
please note the test cases below - it should be self-explanatory
i am on MonetDB v11.15.7 (Feb2013-SP2), i run the test before the
upgrade with the same results
please advice
thanks a lot
milan
test case 1, no ORDER BY - correct
----------------------------------
WITH SAWITH0 AS ( select 'a' as c1,
'b' as c2,
'c' as c3,
1 as c4 )
select 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5
from SAWITH0 D1
+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+
test case 2, add ORDER BY - wrong
result (repeats the 1st value for all columns)
--------------------------------------------------------------------------------
WITH SAWITH0 AS ( select 'a' as c1,
'b' as c2,
'c' as c3,
1 as c4 )
select 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5
from SAWITH0 D1
order by 5, 4, 3, 2
--order by c5,c4,c3,c2
+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+======+======+======+======+======+
| 0 | a | a | a | a |
+------+------+------+------+------+
Note that the result is the same even if you use the column alias
instead of the ord.num.
test case 3, use completely different alias with
order by - correct
-------------------------------------------------------------------
WITH SAWITH0 AS ( select 'a' as c1,
'b' as c2,
'c' as c3,
1 as c4 )
select 0 as c1,
D1.c1 as a2,
D1.c2 as a3,
D1.c3 as a4,
D1.c4 as a5
from SAWITH0 D1
order by 5, 4, 3, 2
+------+------+------+------+------+
| c1 | a2 | a3 | a4 | a5 |
+======+======+======+======+======+
| 0 | a | b | c | 1 |
+------+------+------+------+------+