SQLite

Check-in [96ea990942]
Login

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

Overview
Comment:Fix the query planner so that it is able to use the integer primary key from an index as part of a multi-column constraint.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 96ea9909429f0b3b4a67002e8340ae3f7dc0b73f
User & Date: drh 2016-03-16 18:26:54.117
References
2016-09-16
13:57 New ticket [0eab1ac759] O(N*N) behavior where it should be O(N). (artifact: 62ca7f9b91 user: drh)
2016-05-06
03:26 New ticket [16c9801ceb] Assertion fault on DELETE. (artifact: 79eb5be6ca user: drh)
Context
2016-03-16
19:10
Avoid a few unnecessary fstat()s on journal files. (check-in: dbf8470591 user: drh tags: trunk)
18:26
Fix the query planner so that it is able to use the integer primary key from an index as part of a multi-column constraint. (check-in: 96ea990942 user: drh tags: trunk)
01:03
Add the SQLITE_OMIT_CODEC_FROM_TCL compile-time option. (check-in: 45f7f0c80b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
285
286
287
288
289
290
291

292
293
294
295
296
297
298
  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  pScan->pIdxExpr = 0;
  if( pIdx ){
    j = iColumn;
    iColumn = pIdx->aiColumn[j];
    if( iColumn==XN_EXPR ) pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;

  }
  if( pIdx && iColumn>=0 ){
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    pScan->zCollName = pIdx->azColl[j];
  }else{
    pScan->idxaff = 0;
    pScan->zCollName = 0;







>







285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  pScan->pIdxExpr = 0;
  if( pIdx ){
    j = iColumn;
    iColumn = pIdx->aiColumn[j];
    if( iColumn==XN_EXPR ) pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
    if( iColumn==pIdx->pTable->iPKey ) iColumn = XN_ROWID;
  }
  if( pIdx && iColumn>=0 ){
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    pScan->zCollName = pIdx->azColl[j];
  }else{
    pScan->idxaff = 0;
    pScan->zCollName = 0;
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pLoop;
  int iCur;
  int j;
  Table *pTab;
  Index *pIdx;
  
  pWInfo = pBuilder->pWInfo;
  if( pWInfo->wctrlFlags & WHERE_FORCE_TABLE ) return 0;
  assert( pWInfo->pTabList->nSrc>=1 );
  pItem = pWInfo->pTabList->a;
  pTab = pItem->pTab;
  if( IsVirtual(pTab) ) return 0;
  if( pItem->fg.isIndexedBy ) return 0;







|







3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pLoop;
  int iCur;
  int j;
  Table *pTab;
  Index *pIdx;

  pWInfo = pBuilder->pWInfo;
  if( pWInfo->wctrlFlags & WHERE_FORCE_TABLE ) return 0;
  assert( pWInfo->pTabList->nSrc>=1 );
  pItem = pWInfo->pTabList->a;
  pTab = pItem->pTab;
  if( IsVirtual(pTab) ) return 0;
  if( pItem->fg.isIndexedBy ) return 0;
Changes to test/intpkey.test.
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
    SELECT * FROM t1 WHERE c=='world';
  }
} {5 hello world 11 hello world 5}
do_test intpkey-3.8 {
  count {
    SELECT * FROM t1 WHERE c=='world' AND a>7;
  }
} {11 hello world 4}
do_test intpkey-3.9 {
  count {
    SELECT * FROM t1 WHERE 7<a;
  }
} {11 hello world 1}

# Test inequality constraints on integer primary keys and rowids







|







292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
    SELECT * FROM t1 WHERE c=='world';
  }
} {5 hello world 11 hello world 5}
do_test intpkey-3.8 {
  count {
    SELECT * FROM t1 WHERE c=='world' AND a>7;
  }
} {11 hello world 3}
do_test intpkey-3.9 {
  count {
    SELECT * FROM t1 WHERE 7<a;
  }
} {11 hello world 1}

# Test inequality constraints on integer primary keys and rowids