Re: query syntax change?

Ed Loehr writes:

> This query works in 7.0.3...
>
> SELECT p.*, e.id AS "employee_id", e.ref_name,
> e.business_line_id, e.record_status_id AS "emp_record_status_id"
> >FROM person p, employee e
> WHERE e.person_id = p.id
>
> UNION ALL
>
> SELECT p.*, NULL AS "employee_id", NULL AS "ref_name",
> NULL AS "business_line_id", NULL AS "emp_record_status_id"
> >FROM person p
> WHERE NOT EXISTS (SELECT id FROM employee WHERE person_id = p.id)
> ORDER BY p.sortable_last_name;
>
> but in 7.1.2 it reports the following error:
>
> ERROR: Relation 'p' does not exist

There wording of the error message isn't the greatest, but the cause is
that the "p" is not visible to the ORDER BY. Consider, what if the "p" in
the two union branches where different tables? The SQL-legal namespace in
ORDER BY is the column aliases of the output columns in the select list,
so that would be "sortable_last_name" (chosen as default due to lack of
alias), "employee_id", "ref_name", etc. In non-unioned queries we can be
a little more lax about this because the semantics are clear.

Btw., order by + union doesn't work prior to 7.1 anyway.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter