/ Check-in [96ea9909]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:96ea9909429f0b3b4a67002e8340ae3f7dc0b73f
User & Date: drh 2016-03-16 18:26:54
References
2016-09-16
13:57 New ticket [0eab1ac7] O(N*N) behavior where it should be O(N). artifact: 62ca7f9b user: drh
2016-05-06
03:26 New ticket [16c9801c] Assertion fault on DELETE. artifact: 79eb5be6 user: drh
Context
2016-03-16
19:10
Avoid a few unnecessary fstat()s on journal files. check-in: dbf84705 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: 96ea9909 user: drh tags: trunk
01:03
Add the SQLITE_OMIT_CODEC_FROM_TCL compile-time option. check-in: 45f7f0c8 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

285
286
287
288
289
290
291

292
293
294
295
296
297
298
....
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
  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;
................................................................................
  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;







>







 







|







285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
....
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
  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;
................................................................................
  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