Yuriy Kaminskiy wrote:
> 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.
Looked at code, I think I found this bug origin.
resolve.c:
static int lookupName(
Parse *pParse, /* The parsing context */
const char *zDb, /* Name of the database containing table, or NULL */
const char *zTab, /* Name of table containing column, or NULL */
const char *zCol, /* Name of the column. */
NameContext *pNC, /* The name context used to resolve the name */
Expr *pExpr /* Make this EXPR node point to the selected column */
){
[...]
if( i<pSrcList->nSrc-1 ){
if( pItem[1].jointype & JT_NATURAL ){
/* If this match occurred in the left table of a natural join,
** then skip the right table to avoid a duplicate match */
pItem++;
i++;
}else if( (pUsing = pItem[1].pUsing)!=0 ){
/* If this match occurs on a column that is in the USING clause
** of a join, skip the search of the right table of the join
** to avoid a duplicate match there. */
int k;
for(k=0; k<pUsing->nId; k++){
if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){
pItem++;
i++;
break;
}
}
}
}
=== cut ===
So it looks if column was in USING... but only in *one next join*.
And won't check/skip joins *after* that.
It also won't handle properly interleaved joins -
FROM a JOIN b USING(j) JOIN c USING(i) JOIN d USING(i).
Patch attached, shortly tested, seems work, please review carefully (this is my
first [serious] patch for sqlite :-))
With patch applied,
CREATE TABLE a(i,j);
CREATE TABLE b(j);
CREATE TABLE c(i);
CREATE TABLE d(j);
SELECT 5 FROM a JOIN b USING (j) JOIN c USING (i) JOIN d USING(j)
WHERE j=5 ORDER BY i, j; -- works
SELECT 5 FROM a NATURAL JOIN b NATURAL JOIN c NATURAL JOIN d
WHERE j=5 ORDER BY i, j; -- works
SELECT 5 FROM a JOIN b ON a.j=b.j JOIN c USING (i) JOIN d USING(j)
WHERE j=5 ORDER BY i, j; -- fails
Error: ambiguous column name: j
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
