SQLite

Check-in [7097241c12]
Login

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: 7097241c1220ada318f8eda938c3e3430b94a606
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
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
** 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.  Other than this priority, if there
** are two or more terms that match, then the choice of which term to return
** is arbitrary.
*/
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 */







|
|
<







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

707
708



709
710
711
712
713
714
715
              for(j=0; pIdx->aiColumn[j]!=iOrigCol; j++){
                if( NEVER(j>=pIdx->nColumn) ) return 0;
              }
              if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ){
                continue;
              }
            }

            pResult = pTerm;
            if( pTerm->prereqRight==0 ) goto findTerm_success;



          }
          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){







>
|
|
>
>
>







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