Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
d22475b81c4e26ccc50f3b5626d43b32 |
User & Date: | drh 2025-04-15 21:59:38.221 |
Context
2025-04-16
| ||
12:48 | Latest upstream teaish for fixes and improvements. (check-in: 8f9148b33e user: stephan tags: trunk) | |
10:53 | 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. (check-in: ba7d5bad32 user: drh tags: branch-3.49) | |
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) | |
19:53 | Fix a minor typo in a code comment. (check-in: 158e8c4fd7 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 | pIndex->uniqNotNull = 0; pIndex->bHasExpr = 1; }else{ j = pCExpr->iColumn; assert( j<=0x7fff ); if( j<0 ){ j = pTab->iPKey; }else{ if( pTab->aCol[j].notNull==0 ){ pIndex->uniqNotNull = 0; } if( pTab->aCol[j].colFlags & COLFLAG_VIRTUAL ){ pIndex->bHasVCol = 1; pIndex->bHasExpr = 1; | > | 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 | pIndex->uniqNotNull = 0; pIndex->bHasExpr = 1; }else{ j = pCExpr->iColumn; assert( j<=0x7fff ); if( j<0 ){ j = pTab->iPKey; pIndex->bIdxRowid = 1; }else{ if( pTab->aCol[j].notNull==0 ){ pIndex->uniqNotNull = 0; } if( pTab->aCol[j].colFlags & COLFLAG_VIRTUAL ){ pIndex->bHasVCol = 1; pIndex->bHasExpr = 1; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 | unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ unsigned hasStat1:1; /* aiRowLogEst values come from sqlite_stat1 */ unsigned bLowQual:1; /* sqlite_stat1 says this is a low-quality index */ unsigned bNoQuery:1; /* Do not use this index to optimize queries */ unsigned bAscKeyBug:1; /* True if the bba7b69f9849b5bf bug applies */ unsigned bHasVCol:1; /* Index references one or more VIRTUAL columns */ unsigned bHasExpr:1; /* Index contains an expression, either a literal ** expression, or a reference to a VIRTUAL column */ #ifdef SQLITE_ENABLE_STAT4 int nSample; /* Number of elements in aSample[] */ int mxSample; /* Number of slots allocated to aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ | > | 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 | unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ unsigned hasStat1:1; /* aiRowLogEst values come from sqlite_stat1 */ unsigned bLowQual:1; /* sqlite_stat1 says this is a low-quality index */ unsigned bNoQuery:1; /* Do not use this index to optimize queries */ unsigned bAscKeyBug:1; /* True if the bba7b69f9849b5bf bug applies */ unsigned bIdxRowid:1; /* One or more of the index keys is the ROWID */ unsigned bHasVCol:1; /* Index references one or more VIRTUAL columns */ unsigned bHasExpr:1; /* Index contains an expression, either a literal ** expression, or a reference to a VIRTUAL column */ #ifdef SQLITE_ENABLE_STAT4 int nSample; /* Number of elements in aSample[] */ int mxSample; /* Number of slots allocated to aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3472 3473 3474 3475 3476 3477 3478 | }else{ pNew->nOut = nOutUnadjusted; } if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<pProbe->nColumn && (pNew->u.btree.nEq<pProbe->nKeyCol || | | | 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 | }else{ pNew->nOut = nOutUnadjusted; } if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<pProbe->nColumn && (pNew->u.btree.nEq<pProbe->nKeyCol || (pProbe->idxType!=SQLITE_IDXTYPE_PRIMARYKEY && !pProbe->bIdxRowid)) ){ if( pNew->u.btree.nEq>3 ){ sqlite3ProgressCheck(pParse); } whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } pNew->nOut = saved_nOut; |
︙ | ︙ |
Changes to test/rowvalue.test.
︙ | ︙ | |||
777 778 779 780 781 782 783 784 785 | } {0 1} do_execsql_test 33.3 { SELECT * FROM t1 WHERE (a,b) BETWEEN (14,99) AND (16,0); } {15 -7} do_execsql_test 33.3 { SELECT * FROM t1 WHERE (a,b) BETWEEN (2,99) AND (4,0); } {3 100} finish_test | > > > > > > > > > > > > > > > > > > > > > > > | 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 | } {0 1} do_execsql_test 33.3 { SELECT * FROM t1 WHERE (a,b) BETWEEN (14,99) AND (16,0); } {15 -7} do_execsql_test 33.3 { SELECT * FROM t1 WHERE (a,b) BETWEEN (2,99) AND (4,0); } {3 100} # 2025-04-15 https://sqlite.org/forum/forumpost/b9647a113b465950 # Incorrect result when the schema includes a table with a UNIQUE # constraint and one of the columns in the UNIQUE constraint is the # INTEGER PRIMARY KEY, and the columns that UNIQUE constraint are # used in a rowvalue-IN operator constraint. # reset_db do_execsql_test 34.1 { CREATE TABLE items ( Id INTEGER /* rowid alias */, Item INTEGER /* any type */, Test TEXT /* TEXT or BLOB */, Filler, /* any type */ PRIMARY KEY(Id), UNIQUE(Item, Id) ); INSERT INTO items (Id, Item) VALUES (1, 2), (2, 2), (3, 3), (4, 5); UPDATE items SET test='ok' WHERE (Id, Item) IN (SELECT Id, Item FROM items); SELECT Id, Item, test FROM items ORDER BY id; } {1 2 ok 2 2 ok 3 3 ok 4 5 ok} finish_test |