SQLite

Check-in [e038ce89]
Login

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

Overview
Comment:Do not assume that "col IS ?" matches at most a single row of a UNIQUE column unless the column is also NOT NULL.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e038ce8955e785afcc07bb22499955bbd22a7af4
User & Date: dan 2015-05-15 19:59:23
Context
2015-05-15
20:14
Minor coding style change, adjust new local variable casing for consistency. (check-in: 1a4628c6 user: mistachkin tags: trunk)
19:59
Do not assume that "col IS ?" matches at most a single row of a UNIQUE column unless the column is also NOT NULL. (check-in: e038ce89 user: dan tags: trunk)
04:13
Simplifications to error message processing. Fix a possible problem in error message formatting when vacuuming a database with a corrupt schema. (check-in: 56ef98a0 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

︙︙
6326
6327
6328
6329
6330
6331
6332

6333
6334
6335
6336
6337

6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
6348

    pLoop->aLTerm[0] = pTerm;
    pLoop->nLTerm = 1;
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){

      assert( pLoop->aLTermSpace==pLoop->aLTerm );
      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;

      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx);
        if( pTerm==0 ) break;
         testcase( pTerm->eOperator & WO_IS );
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;
      }








>





>

|

|








6326
6327
6328
6329
6330
6331
6332
6333
6334
6335
6336
6337
6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
6348
6349
6350

    pLoop->aLTerm[0] = pTerm;
    pLoop->nLTerm = 1;
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int opmask;
      assert( pLoop->aLTermSpace==pLoop->aLTerm );
      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;
      opmask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ;
      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, opmask, pIdx);
        if( pTerm==0 ) break;
        testcase( pTerm->eOperator & WO_IS );
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;
      }
︙︙

Changes to test/where4.test.

︙︙
15
16
17
18
19
20
21

22
23
24
25
26
27
28

# that IS NULL phrases are correctly optimized.  But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable !tclvar||!bloblit {
  finish_test
  return
}

# Build some test data








>








15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# that IS NULL phrases are correctly optimized.  But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix where4

ifcapable !tclvar||!bloblit {
  finish_test
  return
}

# Build some test data
︙︙
280
281
282
283
284
285
286
287

















288


    SELECT sum((
      SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
    )) FROM t7;
  }
} {{}}

}; #ifcapable subquery


















finish_test










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>

281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307

    SELECT sum((
      SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
    )) FROM t7;
  }
} {{}}

}; #ifcapable subquery

#-------------------------------------------------------------------------
# Verify that "IS ?" with a NULL bound to the variable also functions
# correctly.

unset -nocomplain null

do_execsql_test 8.1 {
  CREATE TABLE u9(a UNIQUE, b);
  INSERT INTO u9 VALUES(NULL, 1);
  INSERT INTO u9 VALUES(NULL, 2);
}
do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL  } {{} 1 {} 2}
do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2}




finish_test