Обсуждение: BUG #5506: Error in the grammar of de joins

The following bug has been logged online:

Bug reference:      5506
Logged by:          Fernando Cano
Email address:      fcano@uniovi.es
PostgreSQL version: 8.4
Operating system:   Ubuntu 9.04
Description:        Error in the grammar of de joins
Details:

This sentences are valid with your grammar but generate an error.

create table t1 ( id_t1 smallint, name text);
create table t2 ( id_t2 smallint, name text);

select * from natural join using (id) ;
select * from t1 natural cross join t2;
select * from natural cross join using (id) ;
select * from t1 join t2;
select t1  natural left join t2;

I have problems when I explain to my students the sintax of the joins.

I use this  sintax, but I'm not sure:

from_item  {CROSS | NATURAL join_type }  JOIN  from_item
from_item join_type JOIN from_item [ ON join_condition | USING ( join_column
[, ...] ) ]

where join_type is:
     [ INNER ] | {LEFT |RIGHT | FULL} [ OUTER ]

"Fernando Cano" <fcano@uniovi.es> writes:
> This sentences are valid with your grammar but generate an error.

> create table t1 ( id_t1 smallint, name text);
> create table t2 ( id_t2 smallint, name text);

> select * from natural join using (id) ;
> select * from t1 natural cross join t2;
> select * from natural cross join using (id) ;
> select * from t1 join t2;
> select t1  natural left join t2;

Uh, no, they're *not* valid with our grammar --- that's why you're
getting errors.  They're not valid according to the SQL standard
either, so I'm not sure exactly what your point is.

> I have problems when I explain to my students the sintax of the joins.

The SQL92 standard defines join syntax like this:

         <joined table> ::=
                <cross join>
              | <qualified join>
              | <left paren> <joined table> <right paren>

         <cross join> ::=
              <table reference> CROSS JOIN <table reference>

         <qualified join> ::=
              <table reference> [ NATURAL ] [ <join type> ] JOIN
                <table reference> [ <join specification> ]

         <join specification> ::=
                <join condition>
              | <named columns join>

         <join condition> ::= ON <search condition>

         <named columns join> ::=
              USING <left paren> <join column list> <right paren>

         <join type> ::=
                INNER
              | <outer join type> [ OUTER ]
              | UNION

         <outer join type> ::=
                LEFT
              | RIGHT
              | FULL

         <join column list> ::= <column name list>

The restriction that NATURAL can't appear with ON or USING isn't
in this syntax diagram, though; it's explained in the text.

            regards, tom lane