From: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pggeneral <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: order by + union (was: query syntax change?) |
Date: | 2001-07-06 18:49:00 |
Message-ID: | 3B46081C.36306C02@austin.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-07-06 18:49:37 | Re: Vacuum and Transactions |
Previous Message | Mihai Gheorghiu | 2001-07-06 18:47:39 | Number of days |