Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | When generating WHERE clause terms internally for NATURAL and USING joins, identify the table by its position in the FROM list, not by its name or alias. Fix for [b73fb0bd64]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6fe6371175482d38ac4aeea994c7b20c |
User & Date: | dan 2009-10-19 15:52:33.000 |
References
2009-10-19
| ||
21:05 | • Fixed ticket [b73fb0bd64]: Natural self-join defect plus 2 other changes (artifact: 39153355fb user: drh) | |
Context
2009-10-19
| ||
18:11 | Remove the sqlite3_reoptimize() API. The same functionality is now provided automatically to queries prepared using prepare_v2(). (check-in: 2c50b3d5aa user: dan tags: trunk) | |
15:52 | When generating WHERE clause terms internally for NATURAL and USING joins, identify the table by its position in the FROM list, not by its name or alias. Fix for [b73fb0bd64]. (check-in: 6fe6371175 user: dan tags: trunk) | |
07:50 | Use 64-bit arithmetic in the xRead() method of asyncRead. Fix for [94c04eaadb]. (check-in: ca3e41b057 user: dan tags: trunk) | |
Changes
Changes to src/resolve.c.
︙ | ︙ | |||
389 390 391 392 393 394 395 396 397 398 399 400 401 402 | pTopNC = pTopNC->pNext; } return WRC_Prune; } else { return WRC_Abort; } } /* ** This routine is callback for sqlite3WalkExpr(). ** ** Resolve symbolic names into TK_COLUMN operators for the current ** node in the expression tree. Return 0 to continue the search down ** the tree or 2 to abort the tree walk. | > > > > > > > > > > > > > > > > > > > > > | 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 | pTopNC = pTopNC->pNext; } return WRC_Prune; } else { return WRC_Abort; } } /* ** Allocate and return a pointer to an expression to load the column iCol ** from datasource iSrc datasource in SrcList pSrc. */ Expr *sqlite3CreateColumnExpr(sqlite3 *db, SrcList *pSrc, int iSrc, int iCol){ Expr *p = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); if( p ){ struct SrcList_item *pItem = &pSrc->a[iSrc]; p->pTab = pItem->pTab; p->iTable = pItem->iCursor; if( p->pTab->iPKey==iCol ){ p->iColumn = -1; }else{ p->iColumn = iCol; pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol); } ExprSetProperty(p, EP_Resolved); } return p; } /* ** This routine is callback for sqlite3WalkExpr(). ** ** Resolve symbolic names into TK_COLUMN operators for the current ** node in the expression tree. Return 0 to continue the search down ** the tree or 2 to abort the tree walk. |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
188 189 190 191 192 193 194 | for(i=0; i<pTab->nCol; i++){ if( sqlite3StrICmp(pTab->aCol[i].zName, zCol)==0 ) return i; } return -1; } /* | > | > | < < < | | > | | | | | | < | < < | > > | | | < | | | | | < | | < < < | | | | | | | | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 | for(i=0; i<pTab->nCol; i++){ if( sqlite3StrICmp(pTab->aCol[i].zName, zCol)==0 ) return i; } return -1; } /* ** This function is used to add terms implied by JOIN syntax to the ** WHERE clause expression of a SELECT statement. The new term, which ** is ANDed with the existing WHERE clause, is of the form: ** ** (tab1.col1 = tab2.col2) ** ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is ** column iColRight of tab2. */ static void addWhereTerm( Parse *pParse, /* Parsing context */ SrcList *pSrc, /* List of tables in FROM clause */ int iSrc, /* Index of first table to join in pSrc */ int iColLeft, /* Index of column in first table */ int iColRight, /* Index of column in second table */ int isOuterJoin, /* True if this is an OUTER join */ Expr **ppWhere /* IN/OUT: The WHERE clause to add to */ ){ sqlite3 *db = pParse->db; Expr *pE1; Expr *pE2; Expr *pEq; assert( pSrc->nSrc>(iSrc+1) ); assert( pSrc->a[iSrc].pTab ); assert( pSrc->a[iSrc+1].pTab ); pE1 = sqlite3CreateColumnExpr(db, pSrc, iSrc, iColLeft); pE2 = sqlite3CreateColumnExpr(db, pSrc, iSrc+1, iColRight); pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2, 0); if( pEq && isOuterJoin ){ ExprSetProperty(pEq, EP_FromJoin); assert( !ExprHasAnyProperty(pEq, EP_TokenOnly|EP_Reduced) ); ExprSetIrreducible(pEq); pEq->iRightJoinTable = (i16)pE2->iTable; } *ppWhere = sqlite3ExprAnd(db, *ppWhere, pEq); } /* ** Set the EP_FromJoin property on all terms of the given expression. ** And set the Expr.iRightJoinTable to iTable for every term in the ** expression. ** |
︙ | ︙ | |||
314 315 316 317 318 319 320 | if( pRight->pOn || pRight->pUsing ){ sqlite3ErrorMsg(pParse, "a NATURAL join may not have " "an ON or USING clause", 0); return 1; } for(j=0; j<pLeftTab->nCol; j++){ char *zName = pLeftTab->aCol[j].zName; | | < | < | | 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 | if( pRight->pOn || pRight->pUsing ){ sqlite3ErrorMsg(pParse, "a NATURAL join may not have " "an ON or USING clause", 0); return 1; } for(j=0; j<pLeftTab->nCol; j++){ char *zName = pLeftTab->aCol[j].zName; int iRightCol = columnIndex(pRightTab, zName); if( iRightCol>=0 ){ addWhereTerm(pParse, pSrc, i, j, iRightCol, isOuter, &p->pWhere); } } } /* Disallow both ON and USING clauses in the same join */ if( pRight->pOn && pRight->pUsing ){ |
︙ | ︙ | |||
351 352 353 354 355 356 357 | ** Report an error if any column mentioned in the USING clause is ** not contained in both tables to be joined. */ if( pRight->pUsing ){ IdList *pList = pRight->pUsing; for(j=0; j<pList->nId; j++){ char *zName = pList->a[j].zName; | | > > | < < | 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 | ** Report an error if any column mentioned in the USING clause is ** not contained in both tables to be joined. */ if( pRight->pUsing ){ IdList *pList = pRight->pUsing; for(j=0; j<pList->nId; j++){ char *zName = pList->a[j].zName; int iLeftCol = columnIndex(pLeftTab, zName); int iRightCol = columnIndex(pRightTab, zName); if( iLeftCol<0 || iRightCol<0 ){ sqlite3ErrorMsg(pParse, "cannot join using column %s - column " "not present in both tables", zName); return 1; } addWhereTerm(pParse, pSrc, i, iLeftCol, iRightCol, isOuter, &p->pWhere); } } } return 0; } /* |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2639 2640 2641 2642 2643 2644 2645 | void sqlite3Vacuum(Parse*); int sqlite3RunVacuum(char**, sqlite3*); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Expr*, Expr*); void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*); void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*); Vdbe *sqlite3GetVdbe(Parse*); | < | 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 | void sqlite3Vacuum(Parse*); int sqlite3RunVacuum(char**, sqlite3*); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Expr*, Expr*); void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*); void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*); Vdbe *sqlite3GetVdbe(Parse*); void sqlite3PrngSaveState(void); void sqlite3PrngRestoreState(void); void sqlite3PrngResetState(void); void sqlite3RollbackAll(sqlite3*); void sqlite3CodeVerifySchema(Parse*, int); void sqlite3BeginTransaction(Parse*, int); void sqlite3CommitTransaction(Parse*); |
︙ | ︙ | |||
2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 | int sqlite3OpenTempDatabase(Parse *); void sqlite3StrAccumInit(StrAccum*, char*, int, int); void sqlite3StrAccumAppend(StrAccum*,const char*,int); char *sqlite3StrAccumFinish(StrAccum*); void sqlite3StrAccumReset(StrAccum*); void sqlite3SelectDestInit(SelectDest*,int,int); void sqlite3BackupRestart(sqlite3_backup *); void sqlite3BackupUpdate(sqlite3_backup *, Pgno, const u8 *); /* ** The interface to the LEMON-generated parser */ | > | 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 | int sqlite3OpenTempDatabase(Parse *); void sqlite3StrAccumInit(StrAccum*, char*, int, int); void sqlite3StrAccumAppend(StrAccum*,const char*,int); char *sqlite3StrAccumFinish(StrAccum*); void sqlite3StrAccumReset(StrAccum*); void sqlite3SelectDestInit(SelectDest*,int,int); Expr *sqlite3CreateColumnExpr(sqlite3 *, SrcList *, int, int); void sqlite3BackupRestart(sqlite3_backup *); void sqlite3BackupUpdate(sqlite3_backup *, Pgno, const u8 *); /* ** The interface to the LEMON-generated parser */ |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
575 576 577 578 579 580 581 | sqlite3 *db = pParse->db; /* Database connection */ const char *pVTab = (const char*)sqlite3GetVTable(db, pTab); SelectDest dest; /* Construct the SELECT statement that will find the new values for ** all updated rows. */ | | < | | 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 | sqlite3 *db = pParse->db; /* Database connection */ const char *pVTab = (const char*)sqlite3GetVTable(db, pTab); SelectDest dest; /* Construct the SELECT statement that will find the new values for ** all updated rows. */ pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_")); if( pRowid ){ pEList = sqlite3ExprListAppend(pParse, pEList, sqlite3ExprDup(db, pRowid, 0)); } assert( pTab->iPKey<0 ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]>=0 ){ pExpr = sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0); }else{ pExpr = sqlite3Expr(db, TK_ID, pTab->aCol[i].zName); } pEList = sqlite3ExprListAppend(pParse, pEList, pExpr); } pSelect = sqlite3SelectNew(pParse, pEList, pSrc, pWhere, 0, 0, 0, 0, 0, 0); /* Create the ephemeral table into which the update results will ** be stored. |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 | ** create an sqlite3_value structure containing this value, again with ** affinity aff applied to it, instead. ** ** If neither of the above apply, set *pp to NULL. ** ** If an error occurs, return an error code. Otherwise, SQLITE_OK. */ static int valueFromExpr( Parse *pParse, Expr *pExpr, u8 aff, sqlite3_value **pp ){ if( (pExpr->op==TK_VARIABLE) || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE) ){ int iVar = pExpr->iColumn; sqlite3VdbeSetVarmask(pParse->pVdbe, iVar, 0); *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff); return SQLITE_OK; } return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp); } /* ** This function is used to estimate the number of rows that will be visited ** by scanning an index for a range of values. The range may have an upper ** bound, a lower bound, or both. The WHERE clause terms that set the upper ** and lower bounds are represented by pLower and pUpper respectively. For ** example, assuming that index p is on t1(a): | > > | 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 | ** create an sqlite3_value structure containing this value, again with ** affinity aff applied to it, instead. ** ** If neither of the above apply, set *pp to NULL. ** ** If an error occurs, return an error code. Otherwise, SQLITE_OK. */ #ifdef SQLITE_ENABLE_STAT2 static int valueFromExpr( Parse *pParse, Expr *pExpr, u8 aff, sqlite3_value **pp ){ if( (pExpr->op==TK_VARIABLE) || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE) ){ int iVar = pExpr->iColumn; sqlite3VdbeSetVarmask(pParse->pVdbe, iVar, 0); *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff); return SQLITE_OK; } return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp); } #endif /* ** This function is used to estimate the number of rows that will be visited ** by scanning an index for a range of values. The range may have an upper ** bound, a lower bound, or both. The WHERE clause terms that set the upper ** and lower bounds are represented by pLower and pUpper respectively. For ** example, assuming that index p is on t1(a): |
︙ | ︙ |
Changes to test/join.test.
︙ | ︙ | |||
571 572 573 574 575 576 577 578 579 | do_test join-10.3 { execsql { SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24); } } {1 2 3 {} {} {}} } ;# ifcapable subquery finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 | do_test join-10.3 { execsql { SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24); } } {1 2 3 {} {} {}} } ;# ifcapable subquery #------------------------------------------------------------------------- # The following tests are to ensure that bug b73fb0bd64 is fixed. # do_test join-11.1 { drop_all_tables execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); INSERT INTO t1 VALUES(1,'abc'); INSERT INTO t1 VALUES(2,'def'); INSERT INTO t2 VALUES(1,'abc'); INSERT INTO t2 VALUES(2,'def'); SELECT * FROM t1 NATURAL JOIN t2; } } {1 abc 2 def} do_test join-11.2 { execsql { SELECT a FROM t1 JOIN t1 USING (a)} } {1 2} do_test join-11.3 { execsql { SELECT a FROM t1 JOIN t1 AS t2 USING (a)} } {1 2} do_test join-11.3 { execsql { SELECT * FROM t1 NATURAL JOIN t1 AS t2} } {1 abc 2 def} do_test join-11.4 { execsql { SELECT * FROM t1 NATURAL JOIN t1 } } {1 abc 2 def} do_test join-11.5 { drop_all_tables execsql { CREATE TABLE t1(a COLLATE nocase, b); CREATE TABLE t2(a, b); INSERT INTO t1 VALUES('ONE', 1); INSERT INTO t1 VALUES('two', 2); INSERT INTO t2 VALUES('one', 1); INSERT INTO t2 VALUES('two', 2); } } {} do_test join-11.6 { execsql { SELECT * FROM t1 NATURAL JOIN t2 } } {ONE 1 two 2} do_test join-11.7 { execsql { SELECT * FROM t2 NATURAL JOIN t1 } } {two 2} do_test join-11.8 { drop_all_tables execsql { CREATE TABLE t1(a, b TEXT); CREATE TABLE t2(b INTEGER, a); INSERT INTO t1 VALUES('one', '1.0'); INSERT INTO t1 VALUES('two', '2'); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(2, 'two'); } } {} do_test join-11.9 { execsql { SELECT * FROM t1 NATURAL JOIN t2 } } {one 1.0 two 2} do_test join-11.10 { execsql { SELECT * FROM t2 NATURAL JOIN t1 } } {1 one 2 two} finish_test |