SQLite

Check-in [946e06cd2a]
Login

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

Overview
Comment:Avoid assertion faults in queries using indices with redundant columns. Ticket [3dbdcdb14e7f41].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 946e06cd2a2cc209b0786b7e9d28c0746320d646
User & Date: drh 2010-04-30 21:03:24.000
Context
2010-05-03
14:08
Merge the write-ahead-logging changes into the trunk. (check-in: de9ae443cc user: drh tags: trunk)
2010-04-30
22:28
Merge in changes from the trunk. (check-in: 76bf0eee1f user: drh tags: wal)
21:03
Avoid assertion faults in queries using indices with redundant columns. Ticket [3dbdcdb14e7f41]. (check-in: 946e06cd2a user: drh tags: trunk)
05:57
Zero the "immediate FK constraint counter" associated with a statement object when sqlite3_reset() is called. Fix for [c39ff61c43]. (check-in: f660be615a user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907

** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.
*/
static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
  if( pTerm
      && ALWAYS((pTerm->wtFlags & TERM_CODED)==0)
      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
  ){
    pTerm->wtFlags |= TERM_CODED;
    if( pTerm->iParent>=0 ){
      WhereTerm *pOther = &pTerm->pWC->a[pTerm->iParent];
      if( (--pOther->nChild)==0 ){
        disableTerm(pLevel, pOther);








|








2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907

** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.
*/
static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
  if( pTerm
      && (pTerm->wtFlags & TERM_CODED)==0
      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
  ){
    pTerm->wtFlags |= TERM_CODED;
    if( pTerm->iParent>=0 ){
      WhereTerm *pOther = &pTerm->pWC->a[pTerm->iParent];
      if( (--pOther->nChild)==0 ){
        disableTerm(pLevel, pOther);
︙︙
3091
3092
3093
3094
3095
3096
3097


3098
3099
3100
3101
3102
3103
3104
3105

  */
  assert( pIdx->nColumn>=nEq );
  for(j=0; j<nEq; j++){
    int r1;
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx);
    if( NEVER(pTerm==0) ) break;


    assert( (pTerm->wtFlags & TERM_CODED)==0 );
    r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
    if( r1!=regBase+j ){
      if( nReg==1 ){
        sqlite3ReleaseTempReg(pParse, regBase);
        regBase = r1;
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);








>
>
|








3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107

  */
  assert( pIdx->nColumn>=nEq );
  for(j=0; j<nEq; j++){
    int r1;
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx);
    if( NEVER(pTerm==0) ) break;
    /* The following true for indices with redundant columns. 
    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
    r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
    if( r1!=regBase+j ){
      if( nReg==1 ){
        sqlite3ReleaseTempReg(pParse, regBase);
        regBase = r1;
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
︙︙
Changes to test/where2.test.
︙︙
615
616
617
618
619
620
621































622
623

    do_test where2-9.2 {
      count {
        SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
      }
    } {1 2 2 1 3 3 7}
  }
}
































finish_test








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



615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654

    do_test where2-9.2 {
      count {
        SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
      }
    } {1 2 2 1 3 3 7}
  }
}

# Indices with redundant columns
#
do_test where2-11.1 {
  execsql {
    CREATE TABLE t11(a,b,c,d);
    CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice.
    INSERT INTO t11 VALUES(1,2,3,4);
    INSERT INTO t11 VALUES(5,6,7,8);
    INSERT INTO t11 VALUES(1,2,9,10);
    INSERT INTO t11 VALUES(5,11,12,13);
    SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c;
  }
} {3 9}
do_test where2-11.2 {
  execsql {
    CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column
    SELECT d FROM t11 WHERE c=9;
  }
} {10}
do_test where2-11.3 {
  execsql {
    SELECT d FROM t11 WHERE c IN (1,2,3,4,5);
  }
} {4}
do_test where2-11.4 {
  execsql {
    SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d;
  }
} {4 8 10}


finish_test