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