Re: Re: [GENERAL] A rare error

"Kevin O'Gorman" <kogorman(at)pacbell(dot)net> writes:
> This just occurred to me: how would you sort the results of this query?
> The path of least resistance from the way things work now would be most
> non-obvious: put the ORDER BY on the leftmost query. It looks like this
> (SELECT * INTO newtable FROM table1 ORDER BY field1) UNION (SELECT * FROM
> table2);
> And I have to say that's about the ugliest construct I've seen in
> a pretty ugly language.

No. This is not SQL92: the spec is perfectly definite that it does not
allow such a construct. What it allows is

SELECT ...foo... UNION SELECT ...bar... ORDER BY baz

and here the ORDER BY is to be interpreted as ordering the results of
the UNION, not the results of the righthand sub-SELECT. This is one
of the cases that you'll need to be careful to get right when
rejiggering the syntax.

Purely as an implementation issue, the current gram.y code drills down
to find the leftmost sub-SELECT and attaches the outer-level ORDER BY
clause to that Select node. analyze.c later extracts the ORDER BY and
attaches it to a top-level Query node that doesn't correspond to any
node existing in the gram.y output. That's all behind the scenes,
however, and shouldn't be exposed to the tender eyes of mere mortal
users.

AFAICS, the input
(SELECT * FROM table1 ORDER BY field1) UNION (SELECT * FROM table2);
should either be rejected (as current sources and all prior releases
would do) or else treat the ORDER BY as ordering the leftmost subselect
before it feeds into the UNION. There is no point in such an ORDER BY
by itself, since UNION will feel free to reorder the tuples --- but
OTOH something like
(SELECT ... ORDER BY ... LIMIT 1) UNION (SELECT ...)
seems entirely sensible and useful to me.

In short: there is a considerable difference between

(SELECT ...foo... UNION SELECT ...bar...) ORDER BY baz

SELECT ...foo... UNION (SELECT ...bar... ORDER BY baz)

(SELECT ...foo... ORDER BY baz) UNION SELECT ...bar...

and any attempt to allow ORDER BY on subqueries will have to be
careful to keep these straight. This may well mean that you need
to rejigger the output structures of gram.y as well as the grammar
itself.

regards, tom lane