Thread: PostgreSQL 8.0 Beta 4 - Qualified ORDER BY column name not working on UNION query

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

Scott Eade <seade@backstagetech.com.au> writes:
> 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

This isn't a bug.  The output columns of the UNION don't belong to any
particular table anymore; they can only be referenced by column number
or unqualified column name.

            regards, tom lane