Thread: select a.name ... union select a.name ... order by a.name fails in 7.1

Palle Girgensohn (girgen@partitur.se) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
select a.name ... union select a.name ... order by a.name fails in 7.1

Long Description
Hi!

I'm not certain this is correct SQL, and I know the rewrite is
easy, but here is a difference I found between 7.0.3 and 7.1:

select a.name from users a
union
select a.name from oldusers a
order by a.userid;

ERROR:  Relation 'a' does not exist

This works fine in postgres 7.0.x

the simple rewrite is of course 'order by userid', but it is
tedious to find all places in our system where this happens. It
seems some programmers have used the above syntax a lot :(

Is it a bug, or was it a feature in 7.0?

/Palle

Sample Code
fails on 7.1, work on 7.0.3:

select a.name from users a
union
select a.name from oldusers a
order by a.userid;


modifying last line ot look like 'order by userid' fixes the problem.

No file was uploaded with this report

girgen@partitur.se writes:
> I'm not certain this is correct SQL, and I know the rewrite is
> easy, but here is a difference I found between 7.0.3 and 7.1:

> select a.name from users a
> union
> select a.name from oldusers a
> order by a.userid;

> ERROR:  Relation 'a' does not exist

It's not correct SQL, and Postgres doesn't support it.

> This works fine in postgres 7.0.x

No it doesn't.  Consider the following example (done with 7.0.*):

play=> create table foo (f1 int, f2 int);
CREATE
play=> insert into foo values (1,10);
INSERT 1021258 1
play=> insert into foo values (2,9);
INSERT 1021259 1
play=> insert into foo values (3,8);
INSERT 1021260 1
play=> select a.f1 from foo a union select a.f1 from foo a;
 f1
----
  1
  2
  3
(3 rows)

-- so far so good, but:

play=> select a.f1 from foo a union select a.f1 from foo a
play-> order by a.f2;
 f1
----
  3
  2
  1
  2
  3
(5 rows)

-- wow, ORDER BY produces a different resultset!

While this is obviously broken, the more fundamental point is that
the only sensible ordering of a UNION result is on one of the result
columns.  Otherwise the ordering isn't well-defined: if the UNION merges
equal values of "name" from the two SELECTs, which SELECT's "userid"
will get used for the sort?  So the SQL standard mandates ORDER BY
only on output column names or numbers, and we enforce that in 7.1.
Prior versions failed to notice that there was a problem, and would
do something fairly random instead :-(

> the simple rewrite is of course 'order by userid', but it is
> tedious to find all places in our system where this happens. It
> seems some programmers have used the above syntax a lot :(

They haven't thought about whether the query is reasonable.
I'd also say that they never looked closely to see if the output
they were getting was reasonable ...

            regards, tom lane

Tom,

thanks for your input. Now I have something to bash my
programmers over the head with ;-)

No, just kidding... But as you say, it is plain bad SQL, I
realize this fully now. Oddly enough, I have a SQL book that
has one or two examples with order by a.foobar. Oh well...
Thanks for the prompt answer!

/Palle

Tom Lane wrote:
>
> girgen@partitur.se writes:
> > I'm not certain this is correct SQL, and I know the rewrite is
> > easy, but here is a difference I found between 7.0.3 and 7.1:
>
> > select a.name from users a
> > union
> > select a.name from oldusers a
> > order by a.userid;
>
> > ERROR:  Relation 'a' does not exist
>
> It's not correct SQL, and Postgres doesn't support it.
>
> > This works fine in postgres 7.0.x
>
> No it doesn't.  Consider the following example (done with 7.0.*):
>
> play=> create table foo (f1 int, f2 int);
> CREATE
> play=> insert into foo values (1,10);
> INSERT 1021258 1
> play=> insert into foo values (2,9);
> INSERT 1021259 1
> play=> insert into foo values (3,8);
> INSERT 1021260 1
> play=> select a.f1 from foo a union select a.f1 from foo a;
>  f1
> ----
>   1
>   2
>   3
> (3 rows)
>
> -- so far so good, but:
>
> play=> select a.f1 from foo a union select a.f1 from foo a
> play-> order by a.f2;
>  f1
> ----
>   3
>   2
>   1
>   2
>   3
> (5 rows)
>
> -- wow, ORDER BY produces a different resultset!
>
> While this is obviously broken, the more fundamental point is that
> the only sensible ordering of a UNION result is on one of the result
> columns.  Otherwise the ordering isn't well-defined: if the UNION merges
> equal values of "name" from the two SELECTs, which SELECT's "userid"
> will get used for the sort?  So the SQL standard mandates ORDER BY
> only on output column names or numbers, and we enforce that in 7.1.
> Prior versions failed to notice that there was a problem, and would
> do something fairly random instead :-(
>
> > the simple rewrite is of course 'order by userid', but it is
> > tedious to find all places in our system where this happens. It
> > seems some programmers have used the above syntax a lot :(
>
> They haven't thought about whether the query is reasonable.
> I'd also say that they never looked closely to see if the output
> they were getting was reasonable ...
>
>                         regards, tom lane