Thread: query syntax change?

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

Anyone understand why?

Regards,
Ed Loehr

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