Thread: psql 7.3.4 disagrees with NATURAL CROSS JOIN
Hello, I have discovered a problem with psql 7.3.4 where it does not seem to like statements containing "NATURAL CROSS JOIN". Ihave a test that I have created that will show the problem. Please have a look at it, give it a try, and let me know ifthere is a problem with the program or with the operator. ;) Just in case, I am running PG 7.3.4 on a SuSE 9.0 x86 box. Thanks, Jonathan Scott -- Jonathan Scott, Programmer, Vanten K.K. jwscott@vanten.com Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267
Attachment
> I have discovered a problem with psql 7.3.4 where it does not seem to like statements containing "NATURAL CROSS JOIN".I have a test that I have created that will show the problem. Please have a look at it, give it a try, and let me knowif there is a problem with the program or with the operator. ;) Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql grammar does not appear to allow it. Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Just a note for the hackers, Jonathan (I think :) ) talked to me about
> this on the irc channel - we couldn't figure this one out. Seems that
> pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql
> grammar does not appear to allow it.
Hm. The syntax NATURAL CROSS JOIN is specifically disallowed by SQL99
and our parser (see attached SQL99 excerpt). If pg_dump produces that
in a view dump then that's a bug, but this test case doesn't let me see
it happen, because the parser rejects the given view definition. Do
you happen to have the original input that created the view?
regards, tom lane
<joined table> ::= <cross join> | <qualified join> | <natural join>
| <union join>
<cross join> ::= <table reference> CROSS JOIN <table primary>
<qualified join> ::= <table reference> [ <join type> ] JOIN <table reference> <join
specification>
<natural join> ::= <table reference> NATURAL [ <join type> ] JOIN <table primary>
...
<join type> ::= INNER | <outer join type> [ OUTER ]
<outer join type> ::= LEFT | RIGHT | FULL
Tom, I have included a pg_dump of the schema that causes this problem. If you take out the word "CROSS" from my source files,it should load just fine. If you then pg_dump it, in there you should find "CROSS". Jonathan Scott On Thu, 06 May 2004 22:26:13 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > Just a note for the hackers, Jonathan (I think :) ) talked to me about > > this on the irc channel - we couldn't figure this one out. Seems that > > pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql > > grammar does not appear to allow it. > > Hm. The syntax NATURAL CROSS JOIN is specifically disallowed by SQL99 > and our parser (see attached SQL99 excerpt). If pg_dump produces that > in a view dump then that's a bug, but this test case doesn't let me see > it happen, because the parser rejects the given view definition. Do > you happen to have the original input that created the view? > > regards, tom lane > > > <joined table> ::= > <cross join> > | <qualified join> > | <natural join> > | <union join> > > <cross join> ::= > <table reference> CROSS JOIN <table primary> > > <qualified join> ::= > <table reference> [ <join type> ] JOIN <table reference> > <join specification> > > <natural join> ::= > <table reference> NATURAL [ <join type> ] JOIN <table primary> > > ... > > <join type> ::= > INNER > | <outer join type> [ OUTER ] > > <outer join type> ::= > LEFT > | RIGHT > | FULL > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Jonathan Scott, Programmer, Vanten K.K. jwscott@vanten.com Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267
Attachment
Jonathan Scott <jwscott@vanten.com> writes:
> I have included a pg_dump of the schema that causes this problem. If
> you take out the word "CROSS" from my source files, it should load
> just fine. If you then pg_dump it, in there you should find "CROSS".
Indeed, I had just come to the conclusion that this test in ruleutils.c
is bogus:
case JOIN_INNER: if (j->quals) appendContextKeyword(context,
"NATURALJOIN ", -PRETTYINDENT_JOIN,
PRETTYINDENT_JOIN, 0); else appendContextKeyword(context, "NATURAL CROSS JOIN
", -PRETTYINDENT_JOIN,
PRETTYINDENT_JOIN,0); break;
and that it should just print NATURAL JOIN either way. The code looks
significantly different in older versions, but the fundamental bug has
been there since the OUTER JOIN support was first committed nearly four
years ago. You get some kind of gold star for being the first to find
it ...
regards, tom lane
Christopher Kings-Lynne wrote: > Just a note for the hackers, Jonathan (I think :) ) talked to me about > this on the irc channel - we couldn't figure this one out. Seems that Exist a postgres irc server? If yes may I know the server and port ? Regards Gaetano Mendola
Gaetano Mendola said: > Christopher Kings-Lynne wrote: >> Just a note for the hackers, Jonathan (I think :) ) talked to me about >> this on the irc channel - we couldn't figure this one out. Seems >> that > > Exist a postgres irc server? If yes may I know the server and port ? > > irc://irc.freenode.net/postgresql (One of the things I put on pgfoundry's home page is a list of what I think are useful links for developers, including this link, Google archives, current docs, CVSweb, and so on) cheers andrew
Tom Lane wrote: >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > >>Just a note for the hackers, Jonathan (I think :) ) talked to me about >>this on the irc channel - we couldn't figure this one out. Seems that >>pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql >>grammar does not appear to allow it. >> >> > >Hm. The syntax NATURAL CROSS JOIN is specifically disallowed by SQL99 >and our parser (see attached SQL99 excerpt). If pg_dump produces that >in a view dump then that's a bug, but this test case doesn't let me see >it happen, because the parser rejects the given view definition. Do >you happen to have the original input that created the view? > > regards, tom lane > > >[snip excerpt from gram.y] > > During the irc discussion I discovered that. But the CREATE TABLE page in the docs appears to suggest that it is legal. That should be fixed. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> During the irc discussion I discovered that. But the CREATE TABLE page
> in the docs appears to suggest that it is legal. That should be fixed.
Where exactly? I see
For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON
join_condition,or USING (join_column [, ...]). See below for the meaning. For CROSS JOIN, none of these clauses may
appear.
regards, tom lane
Tom Lane wrote:
>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>During the irc discussion I discovered that. But the CREATE TABLE page
>>in the docs appears to suggest that it is legal. That should be fixed.
>>
>>
>
>Where exactly? I see
>
> For the INNER and OUTER join types, a join condition must be specified,
> namely exactly one of NATURAL, ON join_condition, or USING (join_column
> [, ...]). See below for the meaning. For CROSS JOIN, none of these
> clauses may appear.
>
>
>
>
Dammit, I meant SELECT, from which you took that quote. And you're
right. I missed that. Sorry.
I saw
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
and then
join_type
One of
*
[ INNER ] JOIN
*
LEFT [ OUTER ] JOIN
*
RIGHT [ OUTER ] JOIN
*
FULL [ OUTER ] JOIN
*
CROSS JOIN
I should have read further
cheers
andrew