example of bugs with natural join in oracle

gistfile1.sql

-- on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
CREATE TABLE left(lid INTEGER, pid INTEGER);
INSERT INTO left VALUES (1,1);
INSERT INTO left VALUES (2,2);
INSERT INTO left VALUES (3,3);
INSERT INTO left VALUES (4,4);
INSERT INTO left VALUES (5,5);

CREATE TABLE right(rid INTEGER, pid INTEGER);
INSERT INTO right VALUES (1,1);
INSERT INTO right VALUES (2,2);
INSERT INTO right VALUES (3,3);
INSERT INTO right VALUES (4,4);

CREATE TABLE there(tid INTEGER, rid INTEGER);
INSERT INTO there VALUES (1,1);
INSERT INTO there VALUES (2,2);
INSERT INTO there VALUES (3,3);

-- natural joins broken
SELECT * FROM (left NATURAL JOIN right) NATURAL JOIN there;
/* results in:
       RID        PID        LID        TID
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
*/

SELECT lid, tid FROM (left NATURAL JOIN right) NATURAL JOIN there;
/* results in:
       LID        TID
---------- ----------
         1          3
         1          2
         1          1
         2          3
         2          2
         2          1
         3          3
         3          2
         3          1
         4          3
         4          2
         4          1
*/

-- inner joins not
SELECT * FROM (left INNER JOIN right USING (pid)) INNER JOIN there USING (rid);
/* results in:
       RID        PID        LID        TID
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
*/
SELECT lid, tid FROM (left INNER JOIN right USING (pid)) INNER JOIN there USING (rid);
/* results in:
       LID        TID
---------- ----------
         1          1
         2          2
         3          3
*/