Re: order by + union (was: query syntax change?)

Peter Eisentraut wrote:
>
> 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.

Thanks, that makes sense.

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

Looks like order by + union was enabled at least in 7.0.3, fwiw...

emsdb=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

emsdb=# drop table mytable;
from mytable t
where t.name isnull
    union all
select t.*
from mytable t
where t.name notnull

order by id;

DROP
emsdb=# create table mytable (id integer not null, name varchar);
CREATE
emsdb=# insert into mytable values (1,'not-null');
INSERT 31802775 1
emsdb=# insert into mytable values (3,null);
INSERT 31802776 1
emsdb=# insert into mytable values (2,'not-null');
INSERT 31802777 1
emsdb=#
emsdb=# select t.*
emsdb-# from mytable t
emsdb-# where t.name isnull
emsdb-#     union all
emsdb-# select t.*
emsdb-# from mytable t
emsdb-# where t.name notnull
emsdb-#
emsdb-# order by id;
 id |   name
----+----------
  1 | not-null
  2 | not-null
  3 |
(3 rows)