The query below has three ORDER BY clauses. When I execute it with the uncommented clause an error results. Some further commentary is included in the comments below. I would expect the uncommented clause to produce the same result as the other two commented out clauses.
-- Qualified ORDER BY column name not working on UNION query (simple example).
SELECT TURBINE_USER.USER_ID FROM TURBINE_USER WHERE TURBINE_USER.USER_ID = 1
UNION
SELECT TURBINE_USER.USER_ID FROM TURBINE_USER WHERE TURBINE_USER.USER_ID = 2
-- The following works.
--ORDER BY 1 ASC
-- The following does not work when I would perhaps expect it to (it certainly works when a non-UNION query is used).
ORDER BY TURBINE_USER.USER_ID ASC
-- The following also works as I would expect it to unless more than one USER_ID column appears in the column list.
--ORDER BY USER_ID ASC
-- For the case that does not work the message is:
--
-- NOTICE: adding missing FROM-clause entry for table "turbine_user"
--
-- ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns
-- I haven't tried a case where the sort column (in this case USER_ID) appears more than once in the column list.
I will not be surprised at all if the above is expected behaviour, in which case I apologise for wasting your time.
Incidentally, the error message that occurs if I EXPLAIN the above query is as above but with an additional line that seems out of place to me:
Query inserted one rows with OID 0.
Also, if I EXPLAIN this query with one of the working ORDER BY clauses it produces a result, but if I select the ANALYSE option (I am using the pgAdmin III that comes bundled with the Beta 4 Windows installer) I get something completely nonsensical:
ERROR: syntax error at or near "ROLLBACK" at character 897
Thanks,
Scott
-- Scott Eade Backstage Technologies Pty. Ltd. http://www.backstagetech.com.au
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
