I am running PostgreSQL 8.0 Beta 4 (Windows installer) on Windows XP Pro
SP2.
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