Thread: " ::= " grammar rule not accepted by
Postgres
<p><font face="sans-serif" size="2">This expression is (I think) a well-formed query, but is rejected by Postgresql 8.4 (tis a table name).</font><br /><br /><font face="sans-serif" size="2"> t t1 NATURAL JOIN t t2;</font><br /><br /><font face="sans-serif"size="2">Here is the relevant parts of grammar (with some optional elements omitted for simplicity): </font><br/><br /><font face="sans-serif" size="2"> <query expression> ::= <query expression body></font><br/><br /><font face="sans-serif" size="2"> <query expression body> ::= <joined table></font><br/><br /><font face="sans-serif" size="2"> <joined table> ::= <natural join></font><br /><br/><font face="sans-serif" size="2"> <natural join> ::= <table reference> NATURAL JOIN <table primary></font><br/><br /><font face="sans-serif" size="2"> <table reference> ::= <table primary></font><br/><br /><font face="sans-serif" size="2"> <table primary> ::= <table or query name> [ <correlationname> ]</font><br /><br /><font face="sans-serif" size="2"> <table or query name> ::= <table name></font><br/><br /><font face="sans-serif" size="2">Nor is this use of <joined table> accepted in the <queryexpression> form of a <with list element>:</font><br /><br /><font face="sans-serif" size="2"> WITH twAS ( t t1 NATURAL JOIN t t2 ) TABLE tw; </font><br /><br /><font face="sans-serif" size="2">This leads me to believe thatthe unrecognized grammar rule (in SQL99 standard terms) is</font><br /><br /><font face="sans-serif" size="2"> <queryexpression body> ::= <joined table></font><br /><br /><font face="sans-serif" size="2">There are, of course,other ways to express the operation, including</font><br /><font face="sans-serif" size="2"> SELECT * FROM (t t1 NATURALJOIN t t2);</font><br /><font face="sans-serif" size="2">But I am curious why the first expression is not accepted,besides the immediately obvious "it's not in the Postgres version of the SQL grammar."</font><br /><br /><font face="sans-serif"size="2">Clem Dickey</font>
Clem Dickey <dickeycl@us.ibm.com> writes:
> This expression is (I think) a well-formed query, but is rejected by
> Postgresql 8.4 (t is a table name).
> t t1 NATURAL JOIN t t2;
Hmm ... I think you are looking at SQL92 or SQL99. The later versions
of the spec don't seem to permit <joined table> to be used that way.
In particular, in SQL:2003 and SQL:2007 I don't see any derivation path
from <query expression> to <joined table>. So while we possibly could
support this, it's legacy syntax, and I doubt there's enough interest to
bother.
[ digs a bit more... ] Ah, here we are: Annex E of SQL:2003 calls out
various incompatibilities from SQL:99, notably
6) In ISO/IEC 9075-2:1999, a <query expression body>, <queryterm>, or <query primary> could consist of a <joinedtable>.
Noneof those three elements can consist of a <joinedtable> in this edition of ISO/IEC 9075.
regards, tom lane
On 06/15/2011 10:59 PM, Tom Lane wrote: > Clem Dickey<dickeycl@us.ibm.com> writes: >> This expression is (I think) a well-formed query, but is rejected by >> Postgresql 8.4 (t is a table name). > >> t t1 NATURAL JOIN t t2; > > Hmm ... I think you are looking at SQL92 or SQL99. > [ digs a bit more... ] Ah, here we are: Annex E of SQL:2003 calls out > various incompatibilities from SQL:99, notably > > 6) In ISO/IEC 9075-2:1999, a<query expression body>,<query > term>, or<query primary> could consist of a<joined > table>. None of those three elements can consist of a<joined > table> in this edition of ISO/IEC 9075. Thanks for the research. I was indeed reading from SQL99, which was current the last time I used SQL. I'll have to spring for a newer standard (or the equivalent).