Jeremy Evans wrote: > 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 ){
out of paranoia: + assert( i>0 ); + assert( pItem->jointype!=0 ); (but both should be completely impossible) >> + /* 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; fwiw, I think both "continue;" can be replaced with "break;" >> } >> } >> + 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. Thanks for testing. Have no idea what can trigger sigsegv here. Patch applied with any rejects/offsets/fuzziness? `make test` shown no problem. `make fulltest` got some problems: prepare.analyze3-1.1.8... Expected: [999 999 499500] Got: [2000 0 499500] prepare.analyze3-1.1.9... Expected: [999 999 499500] Got: [2000 0 499500] ... 11 errors out of 2630788 tests Failures on these tests: prepare.analyze3-1.1.8 prepare.analyze3-1.1.9 prepare.analyze3-1.2.8 prepare.analyze3-1.2.9 prepare.analyze3-1.3.8 prepare.analyze3-1.3.9 prepare.analyze3-2.4 prepare.analyze3-2.6 prepare.analyze3-2.7 prepare.analyze3-2.8 prepare.analyze3-2.9 ... but they fails for me even with patch reverted. And no segfaults. Running few simple tests under valgrind have not produced anything suspicious. > Haven't built a debug version of SQLite yet to determine exactly where. I think SQL statement triggering segv would be enough. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users