Обсуждение: Re: [GENERAL] A rare error
pgsql-hackers-owner@hub.org wrote:
>
> "Kevin O'Gorman" <kogorman@pacbell.net> writes:
> 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.
I'm headed in that direction. I've been calling it 'subquery'.
>
> > 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.
All that being said, I'm not sure enough notice has been taken of one
aspect of the changes already in place, and likely to become more
pronounced. It may be okay with everybody, but I don't want it to be
a big surprise: queries may no longer begin with SELECT, but instead
with an arbitrary number of left parens. In some cases, the semantics
gets lost in the syntax. Consider:
(SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2);
Notice the INTO? Doesn't this seem like an odd place for it, in what
appears to be a subordinate query? Where else would it go? How would
it grab you in an expression with five or more levels of parens?
How about five levels of parens and a complicated targetlist before
you get to the INTO?
What I'm suggesting is that the parens be allowed only on the right
hand side of the set operations. How does that strike you?
>
> regards, tom lane
--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder: mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html
"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change" -- Alfred North Whitehead
Kevin O'Gorman wrote:
>
> pgsql-hackers-owner@hub.org wrote:
> >
> > "Kevin O'Gorman" <kogorman@pacbell.net> writes:
> > 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.
>
> I'm headed in that direction. I've been calling it 'subquery'.
>
> >
> > > 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.
>
> All that being said, I'm not sure enough notice has been taken of one
> aspect of the changes already in place, and likely to become more
> pronounced. It may be okay with everybody, but I don't want it to be
> a big surprise: queries may no longer begin with SELECT, but instead
> with an arbitrary number of left parens. In some cases, the semantics
> gets lost in the syntax. Consider:
>
> (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2);
>
> Notice the INTO? Doesn't this seem like an odd place for it, in what
> appears to be a subordinate query? Where else would it go? How would
> it grab you in an expression with five or more levels of parens?
> How about five levels of parens and a complicated targetlist before
> you get to the INTO?
>
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.
> What I'm suggesting is that the parens be allowed only on the right
> hand side of the set operations. How does that strike you?
Anyway, that's the direction I'm going in now, but as always, I solicit
comments.
>
> >
> > regards, tom lane
>
> --
> Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
> Permanent e-mail forwarder: mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
> At school: mailto:kogorman@cs.ucsb.edu
> Web: http://www.cs.ucsb.edu/~kogorman/index.html
> Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html
>
> "There is a freedom lying beyond circumstance,
> derived from the direct intuition that life can
> be grounded upon its absorption in what is
> changeless amid change"
> -- Alfred North Whitehead
--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder: mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html
"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change" -- Alfred North Whitehead
> (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2); Possibly a silly (and definitely not standards-conformant) suggestion: Maybe grammar should be amended to allow for (SELECT * FROM table1) UNION (SELECT * FROM table2) INTO newtable i.e. union_expr:(select_expr) union (union_expr) [into into_table] > Notice the INTO? Doesn't this seem like an odd place for it, in what > appears to be a subordinate query? Where else would it go? How would > it grab you in an expression with five or more levels of parens? > How about five levels of parens and a complicated targetlist before > you get to the INTO? > > What I'm suggesting is that the parens be allowed only on the right > hand side of the set operations. How does that strike you? > > > > > regards, tom lane > >
"Kevin O'Gorman" <kogorman@pacbell.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
"Kevin O'Gorman" <kogorman@pacbell.net> writes:
> All that being said, I'm not sure enough notice has been taken of one
> aspect of the changes already in place, and likely to become more
> pronounced. It may be okay with everybody, but I don't want it to be
> a big surprise: queries may no longer begin with SELECT, but instead
> with an arbitrary number of left parens.
That's no surprise, because it's been true for a long time. It's
certainly true in the 6.5 grammar, which is the oldest I have on hand.
> In some cases, the semantics gets lost in the syntax. Consider:
> (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2);
> Notice the INTO? Doesn't this seem like an odd place for it, in what
> appears to be a subordinate query? Where else would it go? How would
> it grab you in an expression with five or more levels of parens?
> How about five levels of parens and a complicated targetlist before
> you get to the INTO?
Agreed, it's pretty ugly. This one is only partially SQL92's fault,
since it defines SELECT ... INTO for just a limited context:
<select statement: single row> ::= SELECT [ <set quantifier> ] <select list> INTO
<selecttarget list> <table expression>
(<select target list> here appears to mean a list of local variables in
a calling program, a la ECPG, and doesn't really have anything to do
with the table-destination semantics that Postgres puts on the
construct. But I digress.) The above restricted form of SELECT does
not admit UNION/INTERSECT/EXCEPT constructs at the top level. Postgres
has generalized this to allow INTO <target> in a UNION/etc construct,
which means the word SELECT is not necessarily going to be the very
first thing you see. We do require the INTO to be in the leftmost
primitive SELECT, so the only thing you can really see in front of
"SELECT <selectlist> INTO" is some number of left parentheses. To me
the potential hairiness of the <selectlist> seems like a much bigger
readability issue than the leading parens --- but we got that part of
the syntax straight from SQL92.
> What I'm suggesting is that the parens be allowed only on the right
> hand side of the set operations. How does that strike you?
Will not do, first because EXCEPT is not symmetric, and second because
SQL92 does not describe any such limitation.
regards, tom lane
Tom Lane wrote: > > > One thing I noticed that may surprise: the "%left UNION" and such that > > appear in the source don't seem to do anything. I think our syntax > > doesn't look like operators to yacc, and I suspect it's the opt_all > > that's doing it. That part of yacc I don't understand. > > Hmm, that should work. My reading of the bison manual is that the > precedence of a production is taken from the rightmost terminal symbol > in the production, so > > | select_clause UNION opt_all select_clause > | select_clause INTERSECT opt_all select_clause > | select_clause EXCEPT opt_all select_clause > > should have the correct relative precedences. > > Don't you get shift/reduce errors if you remove those precedence specs? > I'd expect the <select_clause> grammar to be ambiguous without operator > precedence specs ... > > regards, tom lane Yah. I would have thought so too. However, when I comment out the two %left lines (being careful not to dusturb line numbers) I get the absolutely identical gram.c output. So at least for those two things the associativity does nothing at all. I'm inclined to leave them commented out, so they don't mislead. Of course, I was pretty sure the syntax there was unambiguous in any case, so I'm not surprised there's no error; come to think of it, maybe that's why %left has no effect. There has to be something going on, because if I comment out the next line (the one with JOIN in it), I suddenly get 32 shift/reduce errors. This brings up another point. I'm still very new at reading the SQL92 spec, so I need help being sure I've got it right. If we're going to want precedence for these operators, I can do it in the syntax, and it's only a little work. I don't see precedence in SQL92; set operations seem to be left associative of equal priority. Be careful what you ask for, you'll likely get it. And appropos of another comment you made, when we decide how it's going to be, we should have a bunch more things put in the regression tests, not just UNIONs, to make sure it doesn't change unnoticed. ++ kevin -- Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net Permanent e-mail forwarder: mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org At school: mailto:kogorman@cs.ucsb.edu Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead
"Kevin O'Gorman" <kogorman@pacbell.net> writes:
>> Don't you get shift/reduce errors if you remove those precedence specs?
>> I'd expect the <select_clause> grammar to be ambiguous without operator
>> precedence specs ...
> Yah. I would have thought so too. However, when I comment out the
> two %left lines (being careful not to dusturb line numbers) I get the
> absolutely identical gram.c output. So at least for those two things
> the associativity does nothing at all. I'm inclined to leave them commented
> out, so they don't mislead.
Not to put too fine a point on it, but are you talking about the
original grammar or your modified one? Your modified one is erroneous
because it will always associate successive UNION/INTERSECT/EXCEPT
operators left-to-right; this does not meet the SQL spec which insists
that INTERSECT binds more tightly than the other two. Given that, I'm
not surprised that the precedences have no effect.
> I don't see precedence in SQL92; set operations
> seem to be left associative of equal priority.
Better take another look at the <query expression>, <query term>,
<query primary> hierarchy then...
regards, tom lane
> Not to put too fine a point on it, but are you talking about the > original grammar or your modified one? Your modified one is erroneous > because it will always associate successive UNION/INTERSECT/EXCEPT > operators left-to-right; this does not meet the SQL spec which insists > that INTERSECT binds more tightly than the other two. Given that, I'm > not surprised that the precedences have no effect. > > > I don't see precedence in SQL92; set operations > > seem to be left associative of equal priority. > > Better take another look at the <query expression>, <query term>, > <query primary> hierarchy then... Is there something here to patch? Hmm, I don't see anything... I will come back later. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026