Re: Re: [GENERAL] A rare error

"Kevin O'Gorman" <kogorman(at)pacbell(dot)net> writes:
> Were you aware that this is legal:
> (select avg(a),b from dummy group by b) order by b;
> but this is not:
> (select avg(a),b from dummy) group by b order by b;

The reason for that is that SQL doesn't think that "order by" should
be allowed in subqueries, only in a top-level SELECT.

That restriction makes sense in pure SQL, since tuple order is
explicitly *not* part of the computational model. In the eyes of the
SQL spec, the only reason ORDER BY exists at all is for prettification
of final output.

However, once you add the LIMIT clause, queries like
SELECT * FROM foo ORDER BY bar LIMIT 1
suddenly become quite interesting and useful as subqueries
(this query gives you the whole row associated with the minimum
value of bar, which is something you can't easily get in pure SQL).

As the sources stand tonight, you can have such a query as a subquery,
but only if you hide the ORDER/LIMIT inside a view definition. You'll
get a syntax error if you try to write it in-line as a subquery.
There is no longer any good implementation reason for that; it is
solely a grammar restriction.

So I'm coming around to the idea that we should abandon the SQL
restriction and allow ORDER + LIMIT in subqueries. The trouble is
how to do it without confusing yacc.

> BTW: yacc accepts LALR grammars, which are fairly restricted.
> Thus the shift/reduce complaints and such don't mean it's
> ambiguous, just that it's pushing the envelope of the LALR
> paradigm. A lot of yacc grammars do just that, and work
> just fine, but of course you have to know what you're doing.

Right. Also, I believe it's possible that such a grammar will behave
differently depending on which yacc you process it with, which would be
bad. (We have not yet taken the step of insisting that pgsql's grammar
is bison-only, and I don't want to.) So ensuring that we get no shift/
reduce conflicts has been a shop rule around here all along.

Anyway, the bottom line of all this rambling is that if you can get
rid of the distinction between SelectStmt and select_clause altogether,
that would be fine with me. You might consider looking at whether you
can write two nonterminals: a SELECT construct that has no outer parens,
and then an additional construct

subselect: SelectStmt | '(' subselect ')'

which would be used for all the sub-select nonterminals in SelectStmt
itself.

> OTOH, maybe we don't want NOT IN (((SELECT foo FROM bar))).

If we can't do that then we're still going to get complaints, I think.
The original bug report in this thread was specifically that the thing
didn't like redundant parentheses; we should try to remove that
restriction in all contexts not just some.

regards, tom lane