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 (<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?
... 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
