SQLite

View Ticket
Login
Plaintext
2009-12-09
18:23 • Fixed ticket [f74beaab]: Problem with 3-way joins and the USING clause plus 2 other changes (artifact: 04c1a6e9 user: drh)
18:22
Added test cases for the multi-way USING and NATURAL JOIN fix. Ticket [f74beaabde]. (check-in: 0b34ab25 user: drh tags: trunk)
17:36
The USING clause and NATURAL JOIN look at all tables to the left when searching for a match, not just the one table to the immediate left. Tables further to the left are preferred. Fix for ticket [f74beaabde]. Still need to add test cases to complete the ticket. (check-in: b558e96f user: drh tags: trunk)
16:42 • Ticket [f74beaab] Problem with 3-way joins and the USING clause status still Open with 1 other change (artifact: 2901c6ec user: drh)
16:27 • Ticket [f74beaab]: 1 change (artifact: ae81c40e user: dan)
16:26 • Ticket [f74beaab]: 3 changes (artifact: 13f47c66 user: dan)
16:24 • New ticket [f74beaab]. (artifact: ec5d8d05 user: dan)
Ticket Hash: f74beaabde9d153ca905e0377dc50feb90bdd46b
Title: Problem with 3-way joins and the USING clause
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2009-12-09 18:23:30
Version Found In: 3.6.21
Description:
In SQLite, the first SELECT query below is equivalent to the second. But it should be equivalent to the third. 
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]