After being open for more than 2 years, this ticket (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) was closed by Dr. Hipp with the comment:
"The column name is ambiguous. Does it mean a.a or b.a? The result is the same either way, but I don't expect the parser to know this." Here's the SQL from the ticket: 1) CREATE TABLE a (a INTEGER); 2) CREATE TABLE b (a INTEGER); 3) CREATE TABLE c (a INTEGER); 4) SELECT * FROM a JOIN b USING (a); 5) SELECT * FROM a JOIN b USING (a) JOIN c USING (a); 6) SELECT * FROM a JOIN b USING (a) WHERE a = 1; 7) SELECT * FROM a JOIN b USING (a) JOIN c USING (a) WHERE a = 1; Note that SQLite only considers statement 7 invalid. If column a was really ambiguous, statement 6 should also be invalid, but it is not. If Dr. Hipp's statement was correct, SQLite should consider statement 6 invalid as well. The error is obviously in the predicate in statement 7 and not in the USING clause, since statement 5 is considered valid. I don't think column a in the predicate should be ambiguous in either statement 6 or 7, and it isn't on any other database I've tried that supports JOIN USING. The columns in the USING clause (<join column list> in the SQL 2003 standard) are supposed to be treated as if they were a single column shared by both tables. The SQL 2003 standard covers this in ISO/IEC 9075-2:2003 (E) Section 7.7 <joined table>, where it explains that statement 6 should be treated as: SELECT coalesce(a.a, b.a) AS a FROM a, b WHERE a.a = b.a AND a = 1 It doesn't explicitly give an example of a three table JOIN USING, but intuitively the columns in the USING clause shouldn't be ambiguous in the three table case if they aren't in the two table case. This combining of columns from multiple tables and treating them as a single column is reflected in the output of Statements 4-6, which only include a single column and not a column from each table. I would reopen the ticket to ask for an explanation, but that does not appear to be possible, which is why I'm asking here. Can someone explain whether they think SQLite's current behavior for this case is correct, and why? Jeremy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
