query problem in 7.2.1: serious planner issue

I cannot sort on a field that I join across tables.  Here are the examples:

If I do this:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
  AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
  AND ...
ORDER BY lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY 'lot_id' is ambiguous

And if I do this:
If I do this:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
  AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
  AND ...
ORDER BY lots.lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT
result must be on one of the result columns

Hmm, it does not like the table name reference either, so what if I rename
the result column...

If I try this:
SELECT offers.lot_id, lots.project_id, offer.lot_id AS offers_lot_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
  AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id, offer.lot_id AS offers_lot_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
  AND ...
ORDER BY offers_lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT
result must be on one of the result columns

Just for fun I did ths:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
  AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
  AND ...
ORDER BY offers_lot_id
Which of course does not work but produces the error:
Error while executing the query; ERROR: Attribute 'offer_lot_id' not found
Which distinguishes it from the previous error.

Is there a way to do what I am trying to do???


Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com