Hello,

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 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
Fri, 7 May 2004 10:22:25 +0900
Jonathan Scott <jwscott@vanten.com>
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


Fri, 07 May 2004 09:46:41 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN
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

Thu, 06 May 2004 22:26:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN 
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
Fri, 7 May 2004 11:58:55 +0900
Jonathan Scott <jwscott@vanten.com>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN
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

Thu, 06 May 2004 23:08:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN 
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





Fri, 07 May 2004 11:54:38 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN
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



Fri, 7 May 2004 07:46:29 -0400 (EDT)
"Andrew Dunstan" <andrew@dunslane.net>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN
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

Fri, 07 May 2004 16:14:54 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN
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

Fri, 07 May 2004 17:19:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN 
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

Fri, 07 May 2004 17:37:20 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: psql 7.3.4 disagrees with NATURAL CROSS JOIN