On Sat, Oct 15, 2011 at 11:46 PM, Yuriy Kaminskiy <yum...@mail.ru> wrote:
7> 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;
> }
> }
I tried this patch and it does appear to fix the issue, but I'm also
getting occasional segfaults in lookupName after applying it. Haven't
built a debug version of SQLite yet to determine exactly where.
Jeremy
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
