SQLite

In SQLite, the first SELECT query below is equivalent to the second. But it should be equivalent to the third.

<verbatim>
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
</verbatim>

Also, the following should not fail:

<verbatim>
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
</verbatim>

<hr><i>dan added on 2009-12-09 16:27:56:</i><br>
These problems have been present since at least 3.5.0.


<hr><i>drh added on 2009-12-09 16:42:59:</i><br>
Here is an example of the same problem using NATURAL JOIN.

<verbatim>
  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;
</verbatim>

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.

<hr><i>drh added on 2009-12-09 18:23:30:</i><br>
Fixed by [b558e96f0a]