Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to src/where.c.

   639    639   ** Hence a search for X will return <expr> if X=A1 and A1=A2 and A2=A3
   640    640   ** and ... and A9=A10 and A10=<expr>.
   641    641   **
   642    642   ** If there are multiple terms in the WHERE clause of the form "X <op> <expr>"
   643    643   ** then try for the one with no dependencies on <expr> - in other words where
   644    644   ** <expr> is a constant expression of some kind.  Only return entries of
   645    645   ** the form "X <op> Y" where Y is a column in another table if no terms of
   646         -** the form "X <op> <const-expr>" exist.  Other than this priority, if there
   647         -** are two or more terms that match, then the choice of which term to return
   648         -** is arbitrary.
          646  +** the form "X <op> <const-expr>" exist.   If no terms with a constant RHS
          647  +** exist, try to return a term that does not use WO_EQUIV.
   649    648   */
   650    649   static WhereTerm *findTerm(
   651    650     WhereClause *pWC,     /* The WHERE clause to be searched */
   652    651     int iCur,             /* Cursor number of LHS */
   653    652     int iColumn,          /* Column number of LHS */
   654    653     Bitmask notReady,     /* RHS must not overlap with this mask */
   655    654     u32 op,               /* Mask of WO_xx values describing operator */
................................................................................
   700    699                 for(j=0; pIdx->aiColumn[j]!=iOrigCol; j++){
   701    700                   if( NEVER(j>=pIdx->nColumn) ) return 0;
   702    701                 }
   703    702                 if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ){
   704    703                   continue;
   705    704                 }
   706    705               }
   707         -            pResult = pTerm;
   708         -            if( pTerm->prereqRight==0 ) goto findTerm_success;
          706  +            if( pTerm->prereqRight==0 ){
          707  +              pResult = pTerm;
          708  +              goto findTerm_success;
          709  +            }else if( pResult==0 ){
          710  +              pResult = pTerm;
          711  +            }
   709    712             }
   710    713             if( (pTerm->eOperator & WO_EQUIV)!=0
   711    714              && nEquiv<ArraySize(aEquiv)
   712    715             ){
   713    716               pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
   714    717               assert( pX->op==TK_COLUMN );
   715    718               for(j=0; j<nEquiv; j+=2){
................................................................................
  4227   4230       assert( pTerm->pExpr!=0 );
  4228   4231       assert( omitTable==0 );
  4229   4232       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  4230   4233       iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg);
  4231   4234       addrNxt = pLevel->addrNxt;
  4232   4235       sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
  4233   4236       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
         4237  +    sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
  4234   4238       sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  4235   4239       VdbeComment((v, "pk"));
  4236   4240       pLevel->op = OP_Noop;
  4237   4241     }else if( pLevel->plan.wsFlags & WHERE_ROWID_RANGE ){
  4238   4242       /* Case 2:  We have an inequality comparison against the ROWID field.
  4239   4243       */
  4240   4244       int testOp = OP_Noop;

Added test/tkt-fc7bd6358f.test.

            1  +# 2013 March 05
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. Specifically,
           12  +# it tests that ticket [fc7bd6358f]:
           13  +#
           14  +# The following SQL yields an incorrect result (zero rows) in all
           15  +# versions of SQLite between 3.6.14 and 3.7.15.2:
           16  +#
           17  +#    CREATE TABLE t(textid TEXT);
           18  +#    INSERT INTO t VALUES('12');
           19  +#    INSERT INTO t VALUES('34');
           20  +#    CREATE TABLE i(intid INTEGER PRIMARY KEY);
           21  +#    INSERT INTO i VALUES(12);
           22  +#    INSERT INTO i VALUES(34);
           23  +#
           24  +#    SELECT t1.textid AS a, i.intid AS b, t2.textid AS c
           25  +#      FROM t t1, i, t t2
           26  +#     WHERE t1.textid = i.intid
           27  +#       AND t1.textid = t2.textid;
           28  +#
           29  +# The correct result should be two rows, one with 12|12|12 and the other
           30  +# with 34|34|34. With this bug, no rows are returned. Bisecting shows that
           31  +# this bug was introduced with check-in [dd4d67a67454] on 2009-04-23. 
           32  +#
           33  +
           34  +set testdir [file dirname $argv0]
           35  +source $testdir/tester.tcl
           36  +
           37  +do_test tkt-fc7bd6358f.100 {
           38  +  db eval {
           39  +    CREATE TABLE t(textid TEXT);
           40  +    INSERT INTO t VALUES('12');
           41  +    INSERT INTO t VALUES('34');
           42  +    CREATE TABLE i(intid INTEGER PRIMARY KEY);
           43  +    INSERT INTO i VALUES(12);
           44  +    INSERT INTO i VALUES(34);
           45  +  }
           46  +} {}
           47  +unset -nocomplain from
           48  +unset -nocomplain where
           49  +unset -nocomplain a
           50  +unset -nocomplain b
           51  +foreach {a from} {
           52  +  1 {FROM t t1, i, t t2}
           53  +  2 {FROM i, t t1, t t2}
           54  +  3 {FROM t t1, t t2, i}
           55  +} {
           56  +  foreach {b where} {
           57  +    1 {WHERE t1.textid=i.intid AND t1.textid=t2.textid}
           58  +    2 {WHERE i.intid=t1.textid AND t1.textid=t2.textid}
           59  +    3 {WHERE t1.textid=i.intid AND i.intid=t2.textid}
           60  +    4 {WHERE t1.textid=i.intid AND t2.textid=i.intid}
           61  +    5 {WHERE i.intid=t1.textid AND i.intid=t2.textid}
           62  +    6 {WHERE i.intid=t1.textid AND t2.textid=i.intid}
           63  +    7 {WHERE t1.textid=t2.textid AND i.intid=t2.textid}
           64  +    8 {WHERE t1.textid=t2.textid AND t2.textid=i.intid}
           65  +  } {
           66  +    do_test tkt-fc7bd6358f.110.$a.$b.1 {
           67  +       db eval {PRAGMA automatic_index=ON}
           68  +       db eval "SELECT t1.textid, i.intid, t2.textid $from $where"
           69  +    } {12 12 12 34 34 34}
           70  +    do_test tkt-fc7bd6358f.110.$a.$b.2 {
           71  +       db eval {PRAGMA automatic_index=OFF}
           72  +       db eval "SELECT t1.textid, i.intid, t2.textid $from $where"
           73  +    } {12 12 12 34 34 34}
           74  +  }
           75  +}
           76  +
           77  +    
           78  +finish_test