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!
Tom Lane wrote:
> girgen(at)partitur(dot)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);
> 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;
> (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;
> (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
In response to
pgsql-bugs by date
|Next:||From: pgsql-bugs||Date: 2001-04-23 10:37:24|
|Subject: Compile file if using readline-4.2 (PostGres 7.1)|
|Previous:||From: pgsql-bugs||Date: 2001-04-23 02:18:00|
|Subject: jdbc2 fails to getDate|