Yuriy Kaminskiy wrote:
> 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
Whoops, patch eaten by hungry ewoks. Hopefully, inlining will work better:
Subject: fix false "ambiguous column" detection in multiple JOIN USING
Instead of skipping only *next* table, we ignore matches when we have exactly
one match before and we joined to *previous* table with JOIN USING/NATURAL JOIN.
So,
CREATE TABLE a(i, j);
CREATE TABLE b(j);
CREATE TABLE c(i);
CREATE TABLE d(j);
SELECT * FROM a JOIN b USING(j) JOIN c USING(i) JOIN d USING(j)
should work properly.
The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.
Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>
Index: sqlite3-3.7.8/src/resolve.c
===================================================================
--- sqlite3-3.7.8.orig/src/resolve.c 2011-10-16 09:18:20.000000000 +0400
+++ sqlite3-3.7.8/src/resolve.c 2011-10-16 09:39:53.000000000 +0400
@@ -190,33 +190,34 @@ static int lookupName(
for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
IdList *pUsing;
- cnt++;
- pExpr->iTable = pItem->iCursor;
- pExpr->pTab = pTab;
- pMatch = pItem;
- pSchema = pTab->pSchema;
- /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
- pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
- if( i<pSrcList->nSrc-1 ){
- if( pItem[1].jointype & JT_NATURAL ){
+ if( cnt == 1 ){
+ /* We already met this name once in some previous table(s),
+ ** but... */
+ if( pItem->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 ){
+ continue;
+ }else if( (pUsing = pItem->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;
}
}
+ if( k!=pUsing->nId )
+ continue;
}
}
+ cnt++;
+ pExpr->iTable = pItem->iCursor;
+ pExpr->pTab = pTab;
+ pMatch = pItem;
+ pSchema = pTab->pSchema;
+ /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
+ pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
break;
}
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
