SQLite

SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1(a int);
sqlite> CREATE TABLE t2(a int);
sqlite> CREATE TABLE t3(a int, b int);
sqlite> INSERT INTO t1 VALUES('abc');
sqlite> INSERT INTO t3 VALUES('abc', 'def');
sqlite> SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 USING(a);
abc||
sqlite> SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.a=t2.a;
abc|||
sqlite> SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.a=t1.a;
abc||abc|def

Also, the following should not fail:

SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1(w,x);
sqlite> CREATE TABLE t2(x,y);
sqlite> CREATE TABLE t3(w,z);
sqlite> SELECT * FROM t1 JOIN t2 USING(x) JOIN t3 USING(w);
Error: cannot join using column w - column not present in both tables

dan added on 2009-12-09 16:27:56:
These problems have been present since at least 3.5.0.

drh added on 2009-12-09 16:42:59:
Here is an example of the same problem using NATURAL JOIN.

  CREATE TABLE t1(a,x,y);
  INSERT INTO t1 VALUES(1,91,92);
  INSERT INTO t1 VALUES(2,93,94);
  CREATE TABLE t2(b,y,z);
  INSERT INTO t2 VALUES(3,92,93);
  INSERT INTO t2 VALUES(4,94,95);
  CREATE TABLE t3(c,x,z);
  INSERT INTO t3 VALUES(5,91,93);
  INSERT INTO t3 VALUES(6,99,95);
  
  SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

The result set in the final query above includes two "x" columns. In all cases above, the problem is that USING and NATURAL JOIN are only looking for columns in the one table to the immediate left of the join operator, but they should (apparently) be looking at all tables to the left of the join operator and giving precedence to the left-most table.

drh added on 2009-12-09 18:23:30:
Fixed by [b558e96f0a]