psql 7.3.4 disagrees with NATURAL CROSS JOIN

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 know if 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

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, "NATURAL JOIN ",
-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