Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug (ticket [fc7bd6358f59]) that caused incorrect query results in three way queries that involved comparing INTEGER and TEXT columns for equality. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
7097241c1220ada318f8eda938c3e343 |
User & Date: | drh 2013-03-06 01:55:27.319 |
Context
2013-03-06
| ||
11:44 | Fix a problem in incrvacuum_ioerr.test. Do not run ioerr6.test with an in-memory journal. (check-in: 66576b450a user: dan tags: trunk) | |
02:12 | Merge all the latest trunk changes into the sessions branch. (check-in: d69d21903b user: drh tags: sessions) | |
01:55 | Fix a bug (ticket [fc7bd6358f59]) that caused incorrect query results in three way queries that involved comparing INTEGER and TEXT columns for equality. (check-in: 7097241c12 user: drh tags: trunk) | |
01:48 | Add the SQLITE_READONLY_ROLLBACK extended error code. Do not try to set the permissions on journal or wal files except if the filesize is initially zero (indicating that it is a new file.) (check-in: ce4ac66a4b user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
639 640 641 642 643 644 645 | ** Hence a search for X will return <expr> if X=A1 and A1=A2 and A2=A3 ** and ... and A9=A10 and A10=<expr>. ** ** If there are multiple terms in the WHERE clause of the form "X <op> <expr>" ** then try for the one with no dependencies on <expr> - in other words where ** <expr> is a constant expression of some kind. Only return entries of ** the form "X <op> Y" where Y is a column in another table if no terms of | | | < | 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 | ** Hence a search for X will return <expr> if X=A1 and A1=A2 and A2=A3 ** and ... and A9=A10 and A10=<expr>. ** ** If there are multiple terms in the WHERE clause of the form "X <op> <expr>" ** then try for the one with no dependencies on <expr> - in other words where ** <expr> is a constant expression of some kind. Only return entries of ** the form "X <op> Y" where Y is a column in another table if no terms of ** the form "X <op> <const-expr>" exist. If no terms with a constant RHS ** exist, try to return a term that does not use WO_EQUIV. */ static WhereTerm *findTerm( WhereClause *pWC, /* The WHERE clause to be searched */ int iCur, /* Cursor number of LHS */ int iColumn, /* Column number of LHS */ Bitmask notReady, /* RHS must not overlap with this mask */ u32 op, /* Mask of WO_xx values describing operator */ |
︙ | ︙ | |||
700 701 702 703 704 705 706 | for(j=0; pIdx->aiColumn[j]!=iOrigCol; j++){ if( NEVER(j>=pIdx->nColumn) ) return 0; } if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ){ continue; } } | > | | > > > | 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 | for(j=0; pIdx->aiColumn[j]!=iOrigCol; j++){ if( NEVER(j>=pIdx->nColumn) ) return 0; } if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ){ continue; } } if( pTerm->prereqRight==0 ){ pResult = pTerm; goto findTerm_success; }else if( pResult==0 ){ pResult = pTerm; } } if( (pTerm->eOperator & WO_EQUIV)!=0 && nEquiv<ArraySize(aEquiv) ){ pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight); assert( pX->op==TK_COLUMN ); for(j=0; j<nEquiv; j+=2){ |
︙ | ︙ | |||
4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 4240 | assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg); sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg); VdbeComment((v, "pk")); pLevel->op = OP_Noop; }else if( pLevel->plan.wsFlags & WHERE_ROWID_RANGE ){ /* Case 2: We have an inequality comparison against the ROWID field. */ int testOp = OP_Noop; | > | 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 4240 4241 4242 4243 4244 | assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg); sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1); sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg); VdbeComment((v, "pk")); pLevel->op = OP_Noop; }else if( pLevel->plan.wsFlags & WHERE_ROWID_RANGE ){ /* Case 2: We have an inequality comparison against the ROWID field. */ int testOp = OP_Noop; |
︙ | ︙ |
Added test/tkt-fc7bd6358f.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | # 2013 March 05 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. Specifically, # it tests that ticket [fc7bd6358f]: # # The following SQL yields an incorrect result (zero rows) in all # versions of SQLite between 3.6.14 and 3.7.15.2: # # CREATE TABLE t(textid TEXT); # INSERT INTO t VALUES('12'); # INSERT INTO t VALUES('34'); # CREATE TABLE i(intid INTEGER PRIMARY KEY); # INSERT INTO i VALUES(12); # INSERT INTO i VALUES(34); # # SELECT t1.textid AS a, i.intid AS b, t2.textid AS c # FROM t t1, i, t t2 # WHERE t1.textid = i.intid # AND t1.textid = t2.textid; # # The correct result should be two rows, one with 12|12|12 and the other # with 34|34|34. With this bug, no rows are returned. Bisecting shows that # this bug was introduced with check-in [dd4d67a67454] on 2009-04-23. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt-fc7bd6358f.100 { db eval { CREATE TABLE t(textid TEXT); INSERT INTO t VALUES('12'); INSERT INTO t VALUES('34'); CREATE TABLE i(intid INTEGER PRIMARY KEY); INSERT INTO i VALUES(12); INSERT INTO i VALUES(34); } } {} unset -nocomplain from unset -nocomplain where unset -nocomplain a unset -nocomplain b foreach {a from} { 1 {FROM t t1, i, t t2} 2 {FROM i, t t1, t t2} 3 {FROM t t1, t t2, i} } { foreach {b where} { 1 {WHERE t1.textid=i.intid AND t1.textid=t2.textid} 2 {WHERE i.intid=t1.textid AND t1.textid=t2.textid} 3 {WHERE t1.textid=i.intid AND i.intid=t2.textid} 4 {WHERE t1.textid=i.intid AND t2.textid=i.intid} 5 {WHERE i.intid=t1.textid AND i.intid=t2.textid} 6 {WHERE i.intid=t1.textid AND t2.textid=i.intid} 7 {WHERE t1.textid=t2.textid AND i.intid=t2.textid} 8 {WHERE t1.textid=t2.textid AND t2.textid=i.intid} } { do_test tkt-fc7bd6358f.110.$a.$b.1 { db eval {PRAGMA automatic_index=ON} db eval "SELECT t1.textid, i.intid, t2.textid $from $where" } {12 12 12 34 34 34} do_test tkt-fc7bd6358f.110.$a.$b.2 { db eval {PRAGMA automatic_index=OFF} db eval "SELECT t1.textid, i.intid, t2.textid $from $where" } {12 12 12 34 34 34} } } finish_test |