I decided to look into this after all. For starters, the subselect example you give doesn't work in PG 7.1.2, nor does it work in Oracle.
Also, this doesn't work:
select f.i from foo f union select f.i from foo f order by f.i;
but this does:
select f.i from foo f union select f.i from foo f order by i;
Note that the "order by" in your ecommerce example references the column using the table alias name, which is wrong (Oracle and PG 7.1.2 believe it is wrong, and my reading of
Date and Darwin makes me believe both are right). The alia table names ("range variables") don't percolate upwards from the UNION clauses to the surrounding SELECT.
So ... what did you upgrade from? PG 7.0.* to PG 7.1.*? If so, I think you're running into a bug in PG 7.0 that was worked-around by our writing a non-standard query, that now correctly breaks in PG 7.1.*.
Something like that ... could you change the order by and see if that fixes the problem?
And tell me exactly what versions you upgraded from-to?
