Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved fix for ticket [da78413751863] that does not require disabling the query flattener as was done in [005d5b870625]. This also makes the code generator for vector IN operators a little easier to understand. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
723f1be3d4a905a6a16333f8ef3e1067 |
User & Date: | drh 2017-11-17 21:01:04.980 |
References
2025-04-15
| ||
21:59 | Correctly handle the case of a multi-column UNIQUE constraint that contains the ROWID as one of it columns, and then the columns of that UNIQUE are used in a row-value IN operator as a WHERE clause constraint. Reported by forum post b9647a113b. Problem introduced by [723f1be3d4a905a6], part of ticket [da78413751863]. (check-in: d22475b81c user: drh tags: trunk) | |
Context
2017-11-18
| ||
18:07 | Enhance the log messages produced in some cases if database corruption is encountered by an SQLITE_DEBUG build. (check-in: ee840a7669 user: dan tags: trunk) | |
17:30 | Enhance the log messages produced in some cases if database corruption is encountered by an SQLITE_DEBUG build. (Closed-Leaf check-in: 23a3128083 user: dan tags: sqlite-corrupt-page) | |
2017-11-17
| ||
21:01 | Improved fix for ticket [da78413751863] that does not require disabling the query flattener as was done in [005d5b870625]. This also makes the code generator for vector IN operators a little easier to understand. (check-in: 723f1be3d4 user: drh tags: trunk) | |
20:07 | Add some missing "finish_test" lines to the end of test scripts. (check-in: c21406ab32 user: dan tags: trunk) | |
Changes
Changes to src/wherecode.c.
︙ | ︙ | |||
373 374 375 376 377 378 379 | || sqlite3ExprNeedsNoAffinityChange(p, zAff[i]) ){ zAff[i] = SQLITE_AFF_BLOB; } } } | | > > > | | < > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > | > > > > > > > > > > > > > > > | > > > | | | > > > > > > > > > > > > > > > | > > > > > > | < | 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 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 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 | || sqlite3ExprNeedsNoAffinityChange(p, zAff[i]) ){ zAff[i] = SQLITE_AFF_BLOB; } } } /* ** pX is an expression of the form: (vector) IN (SELECT ...) ** In other words, it is a vector IN operator with a SELECT clause on the ** LHS. But not all terms in the vector are indexable and the terms might ** not be in the correct order for indexing. ** ** This routine makes a copy of the input pX expression and then adjusts ** the vector on the LHS with corresponding changes to the SELECT so that ** the vector contains only index terms and those terms are in the correct ** order. The modified IN expression is returned. The caller is responsible ** for deleting the returned expression. ** ** Example: ** ** CREATE TABLE t1(a,b,c,d,e,f); ** CREATE INDEX t1x1 ON t1(e,c); ** SELECT * FROM t1 WHERE (a,b,c,d,e) IN (SELECT v,w,x,y,z FROM t2) ** \_______________________________________/ ** The pX expression ** ** Since only columns e and c can be used with the index, in that order, ** the modified IN expression that is returned will be: ** ** (e,c) IN (SELECT z,x FROM t2) ** ** The reduced pX is different from the original (obviously) and thus is ** only used for indexing, to improve performance. The original unaltered ** IN expression must also be run on each output row for correctness. */ static Expr *removeUnindexableInClauseTerms( Parse *pParse, /* The parsing context */ int iEq, /* Look at loop terms starting here */ WhereLoop *pLoop, /* The current loop */ Expr *pX /* The IN expression to be reduced */ ){ sqlite3 *db = pParse->db; Expr *pNew = sqlite3ExprDup(db, pX, 0); if( db->mallocFailed==0 ){ ExprList *pOrigRhs = pNew->x.pSelect->pEList; /* Original unmodified RHS */ ExprList *pOrigLhs = pNew->pLeft->x.pList; /* Original unmodified LHS */ ExprList *pRhs = 0; /* New RHS after modifications */ ExprList *pLhs = 0; /* New LHS after mods */ int i; /* Loop counter */ Select *pSelect; /* Pointer to the SELECT on the RHS */ for(i=iEq; i<pLoop->nLTerm; i++){ if( pLoop->aLTerm[i]->pExpr==pX ){ int iField = pLoop->aLTerm[i]->iField - 1; assert( pOrigRhs->a[iField].pExpr!=0 ); pRhs = sqlite3ExprListAppend(pParse, pRhs, pOrigRhs->a[iField].pExpr); pOrigRhs->a[iField].pExpr = 0; assert( pOrigLhs->a[iField].pExpr!=0 ); pLhs = sqlite3ExprListAppend(pParse, pLhs, pOrigLhs->a[iField].pExpr); pOrigLhs->a[iField].pExpr = 0; } } sqlite3ExprListDelete(db, pOrigRhs); sqlite3ExprListDelete(db, pOrigLhs); pNew->pLeft->x.pList = pLhs; pNew->x.pSelect->pEList = pRhs; if( pLhs && pLhs->nExpr==1 ){ /* Take care here not to generate a TK_VECTOR containing only a ** single value. Since the parser never creates such a vector, some ** of the subroutines do not handle this case. */ Expr *p = pLhs->a[0].pExpr; pLhs->a[0].pExpr = 0; sqlite3ExprDelete(db, pNew->pLeft); pNew->pLeft = p; } pSelect = pNew->x.pSelect; if( pSelect->pOrderBy ){ /* If the SELECT statement has an ORDER BY clause, zero the ** iOrderByCol variables. These are set to non-zero when an ** ORDER BY term exactly matches one of the terms of the ** result-set. Since the result-set of the SELECT statement may ** have been modified or reordered, these variables are no longer ** set correctly. Since setting them is just an optimization, ** it's easiest just to zero them here. */ ExprList *pOrderBy = pSelect->pOrderBy; for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].u.x.iOrderByCol = 0; } } #if 0 printf("For indexing, change the IN expr:\n"); sqlite3TreeViewExpr(0, pX, 0); printf("Into:\n"); sqlite3TreeViewExpr(0, pNew, 0); #endif } return pNew; } /* ** Generate code for a single equality term of the WHERE clause. An equality ** term can be either X=expr or X IN (...). pTerm is the term to be ** coded. ** |
︙ | ︙ | |||
456 457 458 459 460 461 462 | for(i=0; i<iEq; i++){ if( pLoop->aLTerm[i] && pLoop->aLTerm[i]->pExpr==pX ){ disableTerm(pLevel, pTerm); return iTarget; } } for(i=iEq;i<pLoop->nLTerm; i++){ | > | < < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | < < < < < < < < | | | | 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 | for(i=0; i<iEq; i++){ if( pLoop->aLTerm[i] && pLoop->aLTerm[i]->pExpr==pX ){ disableTerm(pLevel, pTerm); return iTarget; } } for(i=iEq;i<pLoop->nLTerm; i++){ assert( pLoop->aLTerm[i]!=0 ); if( pLoop->aLTerm[i]->pExpr==pX ) nEq++; } if( (pX->flags & EP_xIsSelect)==0 || pX->x.pSelect->pEList->nExpr==1 ){ eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0); }else{ sqlite3 *db = pParse->db; pX = removeUnindexableInClauseTerms(pParse, iEq, pLoop, pX); if( !db->mallocFailed ){ aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*nEq); eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap); pTerm->pExpr->iTable = pX->iTable; } sqlite3ExprDelete(db, pX); pX = pTerm->pExpr; } if( eType==IN_INDEX_INDEX_DESC ){ testcase( bRev ); bRev = !bRev; } iTab = pX->iTable; |
︙ | ︙ |