From dickeycl@us.ibm.com Wed Jun  15 22:12:49 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 44CE4B5DBC9
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 15 Jun 2011 19:12:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 69663-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 15 Jun 2011 22:12:43 +0000 (UTC)
X-Greylist: delayed 00:22:28.289531 by SQLgrey-1.7.6
Received: from e8.ny.us.ibm.com (e8.ny.us.ibm.com [32.97.182.138])
	by mail.postgresql.org (Postfix) with ESMTP id DF486B5DBC8
	for <pgsql-sql@postgresql.org>; Wed, 15 Jun 2011 19:12:42 -0300 (ADT)
Received: from d01relay06.pok.ibm.com (d01relay06.pok.ibm.com [9.56.227.116])
	by e8.ny.us.ibm.com (8.14.4/8.13.1) with ESMTP id p5FLcoRa026343
	for <pgsql-sql@postgresql.org>; Wed, 15 Jun 2011 17:38:50 -0400
Received: from d01av05.pok.ibm.com (d01av05.pok.ibm.com [9.56.224.195])
	by d01relay06.pok.ibm.com (8.13.8/8.13.8/NCO v10.0) with ESMTP id
	p5FLoB6a1708160
	for <pgsql-sql@postgresql.org>; Wed, 15 Jun 2011 17:50:12 -0400
Received: from d01av05.pok.ibm.com (loopback [127.0.0.1])
	by d01av05.pok.ibm.com (8.14.4/8.13.1/NCO v10.0 AVout) with ESMTP id
	p5FLoBJ3023171
	for <pgsql-sql@postgresql.org>; Wed, 15 Jun 2011 17:50:11 -0400
Received: from d01ml605.pok.ibm.com (d01ml605.pok.ibm.com [9.56.227.91])
	by d01av05.pok.ibm.com (8.14.4/8.13.1/NCO v10.0 AVin) with ESMTP id
	p5FLoB2e023168
	for <pgsql-sql@postgresql.org>; Wed, 15 Jun 2011 17:50:11 -0400
Subject: "<query expression body> ::= <joined table>" grammar rule not
	accepted by Postgres
X-KeepSent: 650DD52B:2DDBF653-852578B0:00699C93;
 type=4; name=$KeepSent
To: pgsql-sql@postgresql.org
X-Mailer: Lotus Notes Release 8.5.2 August 10, 2010
Message-ID: 
 <OF650DD52B.2DDBF653-ON852578B0.00699C93-882578B0.0077F2AD@us.ibm.com>
From: Clem Dickey <dickeycl@us.ibm.com>
Date: Wed, 15 Jun 2011 14:50:09 -0700
X-MIMETrack: Serialize by Router on D01ML605/01/M/IBM(Build
	V853_CD4_03082011|March 08, 2011) at 06/15/2011 17:50:11
MIME-Version: 1.0
Content-type: multipart/alternative;
	Boundary="0__=0ABBF223DFFA1A038f9e8a93df938690918c0ABBF223DFFA1A03"
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201106/58
X-Sequence-Number: 35524

--0__=0ABBF223DFFA1A038f9e8a93df938690918c0ABBF223DFFA1A03
Content-type: text/plain; charset=US-ASCII


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;

Here is the relevant parts of grammar (with some optional elements omitted
for simplicity):

  <query expression> ::= <query expression body>

  <query expression body> ::= <joined table>

  <joined table> ::= <natural join>

  <natural join> ::= <table reference> NATURAL JOIN <table primary>

  <table reference> ::= <table primary>

  <table primary> ::= <table or query name> [ <correlation name> ]

  <table or query name> ::= <table name>

Nor is this use of <joined table> accepted in the <query expression> form
of a <with list element>:

  WITH tw AS ( t t1 NATURAL JOIN t t2 ) TABLE tw;

This leads me to believe that the unrecognized grammar rule (in SQL99
standard terms) is

 <query expression body> ::= <joined table>

There are, of course, other ways to express the operation, including
  SELECT * FROM (t t1 NATURAL JOIN t t2);
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."

Clem Dickey
--0__=0ABBF223DFFA1A038f9e8a93df938690918c0ABBF223DFFA1A03
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline

<html><body>
<p><font size="2" face="sans-serif">This expression is (I think) a well-formed query, but is rejected by Postgresql 8.4 (t is a table name).</font><br>
<br>
<font size="2" face="sans-serif">  t t1 NATURAL JOIN t t2;</font><br>
<br>
<font size="2" face="sans-serif">Here is the relevant parts of grammar (with some optional elements omitted for simplicity): </font><br>
<br>
<font size="2" face="sans-serif">  &lt;query expression&gt; ::= &lt;query expression body&gt;</font><br>
<br>
<font size="2" face="sans-serif">  &lt;query expression body&gt; ::= &lt;joined table&gt;</font><br>
<br>
<font size="2" face="sans-serif">  &lt;joined table&gt; ::= &lt;natural join&gt;</font><br>
<br>
<font size="2" face="sans-serif">  &lt;natural join&gt; ::= &lt;table reference&gt; NATURAL JOIN &lt;table primary&gt;</font><br>
<br>
<font size="2" face="sans-serif">  &lt;table reference&gt; ::= &lt;table primary&gt;</font><br>
<br>
<font size="2" face="sans-serif">  &lt;table primary&gt; ::= &lt;table or query name&gt; [ &lt;correlation name&gt; ]</font><br>
<br>
<font size="2" face="sans-serif">  &lt;table or query name&gt; ::= &lt;table name&gt;</font><br>
<br>
<font size="2" face="sans-serif">Nor is this use of &lt;joined table&gt; accepted in the &lt;query expression&gt; form of a &lt;with list element&gt;:</font><br>
<br>
<font size="2" face="sans-serif">  WITH tw AS ( t t1 NATURAL JOIN t t2 ) TABLE tw; </font><br>
<br>
<font size="2" face="sans-serif">This leads me to believe that the unrecognized grammar rule (in SQL99 standard terms) is</font><br>
<br>
<font size="2" face="sans-serif"> &lt;query expression body&gt; ::= &lt;joined table&gt;</font><br>
<br>
<font size="2" face="sans-serif">There are, of course, other ways to express the operation, including</font><br>
<font size="2" face="sans-serif">  SELECT * FROM (t t1 NATURAL JOIN t t2);</font><br>
<font size="2" face="sans-serif">But I am curious why the first expression is not accepted, besides the immediately obvious &quot;it's not in the Postgres version of the SQL grammar.&quot;</font><br>
<br>
<font size="2" face="sans-serif">Clem Dickey</font></body></html>
--0__=0ABBF223DFFA1A038f9e8a93df938690918c0ABBF223DFFA1A03--




From tgl@sss.pgh.pa.us Thu Jun  16 05:59:19 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id DA6A5B5DBC0
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Jun 2011 02:59:19 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 94235-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Jun 2011 05:59:13 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 5288FB5D8BD
	for <pgsql-sql@postgresql.org>; Thu, 16 Jun 2011 02:59:13 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p5G5xCuL019450;
	Thu, 16 Jun 2011 01:59:12 -0400 (EDT)
To: Clem Dickey <dickeycl@us.ibm.com>
cc: pgsql-sql@postgresql.org
Subject: Re: "<query expression body> ::= <joined table>" grammar rule not
	accepted by Postgres
In-reply-to: 
 <OF650DD52B.2DDBF653-ON852578B0.00699C93-882578B0.0077F2AD@us.ibm.com>
References: 
 <OF650DD52B.2DDBF653-ON852578B0.00699C93-882578B0.0077F2AD@us.ibm.com>
Comments: In-reply-to Clem Dickey <dickeycl@us.ibm.com>
	message dated "Wed, 15 Jun 2011 14:50:09 -0700"
Date: Thu, 16 Jun 2011 01:59:12 -0400
Message-ID: <19449.1308203952@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201106/59
X-Sequence-Number: 35525

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>, <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.

			regards, tom lane



From dickeycl@us.ibm.com Thu Jun  16 19:37:43 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 275EEB5DC25
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Jun 2011 16:37:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 10978-01-6 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Jun 2011 19:37:36 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by mail.postgresql.org (Postfix) with ESMTP id 7C62BB5DC45
	for <pgsql-sql@postgresql.org>; Thu, 16 Jun 2011 16:37:09 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.14.4/8.14.4) with ESMTP id p5GJb8iI010763
	for <pgsql-sql@postgresql.org>; Thu, 16 Jun 2011 16:37:08 -0300 (ADT)
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.14.4/8.14.4/Submit) id p5GJ7uAx071633
	for pgsql-sql@postgresql.org; Thu, 16 Jun 2011 16:07:56 -0300 (ADT)
	(envelope-from news)
From: Clem Dickey <dickeycl@us.ibm.com>
X-Newsgroups: pgsql.sql
Subject: Re: "<query expression body> ::= <joined table>" grammar rule
	not accepted by Postgres
Date: Thu, 16 Jun 2011 12:07:56 -0700
Organization: Hub.Org Networking Services
Lines: 20
Message-ID: <itdkac$25u0$1@news.hub.org>
References: 
 <OF650DD52B.2DDBF653-ON852578B0.00699C93-882578B0.0077F2AD@us.ibm.com>
	<19449.1308203952@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc14 Thunderbird/3.1.10
In-Reply-To: <19449.1308203952@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.51 tagged_above=-5 required=5 tests=BAYES_05=-0.5,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201106/62
X-Sequence-Number: 35528

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).