Re: [sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause
Jeremy Evans wrote: > 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 ( 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 , > 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? ... and according to sqlite documentation: === begin quote lang_select.html === For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset. This is the only difference between a USING clause and its equivalent ON constraint. === end quote === So, I agree - "a" IMO, *should not* be ambiguous - with USING/NATURAL JOIN "a" without qualifiers should expand to (only) LHS a, that is - a.a. BTW, I've seen same effect with ORDER BY, but only in specific conditions (I renamed here "a" field to "i" to reduce possible confusion with table name): 8) SELECT * FROM a JOIN b USING (i) ORDER BY i; -- works 9) SELECT 1 FROM a JOIN b USING (i) ORDER BY i; -- works 10) SELECT * FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- works 11) SELECT 1 FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS! Error: ambiguous column name: i 12) SELECT a.i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; -- FAILS! Error: ambiguous column name: i 12) SELECT a.i AS i FROM a JOIN b USING (i) JOIN c USING (i) ORDER BY i; --works Exactly same effect with GROUP BY. This is certainly bug. Either it should fail every time (in 6--11), or it should work in all cases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause
On 15-10-2011 02:26, Jeremy Evans wrote: 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. sqlite> explain query plan select * from a join b using (a) where a=1; 0|0|TABLE a 1|1|TABLE b WITH AUTOMATIC INDEX sqlite> explain query plan select * from a join b using (a) where a.a=1; 0|0|TABLE a 1|1|TABLE b WITH AUTOMATIC INDEX sqlite> explain query plan select * from a join b using (a) where b.a=1; 0|1|TABLE b 1|0|TABLE a WITH AUTOMATIC INDEX sqlite> It is indeed 'odd' that SQLite /chooses/ to pick column a from the first table in aboves first query and that it won't do it here: sqlite> explain query plan select * from a join b using (a) join c using (a) where a=1; Error: ambiguous column name: a sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause
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 ( 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 , 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
