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@gmx.net http://funkturm.homeip.net/~peter

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)

Ed Loehr <eloehr@austin.rr.com> writes:

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...

Nonetheless, it had bugs crawling out of it everywhere ... try more
complex test cases, and pay attention to whether you actually get
the right answer (like the right sort order, distinct/not distinct,
etc). Cross-datatype cases tended to coredump, too.

I believe this mess is all fixed as of 7.1.

regards, tom lane