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
