SQLite

Check-in [ebb0c8a3e9]
Login

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

Overview
Comment:Code generator tries to avoid pointless OP_IsNull and OP_Affinity opcodes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ebb0c8a3e977dc741704e733b5a5d931d9b27028
User & Date: drh 2009-11-17 18:31:48.000
References
2010-06-09
15:20 New ticket [f973c7ac31] Incorrect affinity for indexed range constraint. (artifact: def6ef2209 user: drh)
Context
2009-11-17
23:59
Enhancements to the VDBE opcode loop to reduce the number of machine-code instructions evaluated by about 10%. (check-in: 9744ffb3f5 user: drh tags: trunk)
18:31
Code generator tries to avoid pointless OP_IsNull and OP_Affinity opcodes. (check-in: ebb0c8a3e9 user: drh tags: trunk)
2009-11-16
23:36
Make sure the output of sqlite3OsFullPathname is initialized even if the fails due to I/O or OOM errors. (check-in: e4943adb83 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
1253
1254
1255
1256
1257
1258
1259

































































1260
1261
1262
1263
1264
1265
1266
               || (p->flags2 & EP2_MallocedToken)==0 );
    p->op = TK_INTEGER;
    p->flags |= EP_IntValue;
    p->u.iValue = *pValue;
  }
  return rc;
}


































































/*
** Return TRUE if the given string is a row-id column name.
*/
int sqlite3IsRowid(const char *z){
  if( sqlite3StrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqlite3StrICmp(z, "ROWID")==0 ) return 1;







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







1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
               || (p->flags2 & EP2_MallocedToken)==0 );
    p->op = TK_INTEGER;
    p->flags |= EP_IntValue;
    p->u.iValue = *pValue;
  }
  return rc;
}

/*
** Return FALSE if there is no chance that the expression can be NULL.
**
** If the expression might be NULL or if the expression is too complex
** to tell return TRUE.  
**
** This routine is used as an optimization, to skip OP_IsNull opcodes
** when we know that a value cannot be NULL.  Hence, a false positive
** (returning TRUE when in fact the expression can never be NULL) might
** be a small performance hit but is otherwise harmless.  On the other
** hand, a false negative (returning FALSE when the result could be NULL)
** will likely result in an incorrect answer.  So when in doubt, return
** TRUE.
*/
int sqlite3ExprCanBeNull(const Expr *p){
  u8 op;
  while( p->op==TK_UPLUS || p->op==TK_MINUS ){ p = p->pLeft; }
  op = p->op;
  if( op==TK_REGISTER ) op = p->op2;
  switch( op ){
    case TK_INTEGER:
    case TK_STRING:
    case TK_FLOAT:
    case TK_BLOB:
      return 0;
    default:
      return 1;
  }
}

/*
** Return TRUE if the given expression is a constant which would be
** unchanged by OP_Affinity with the affinity given in the second
** argument.
**
** This routine is used to determine if the OP_Affinity operation
** can be omitted.  When in doubt return FALSE.  A false negative
** is harmless.  A false positive, however, can result in the wrong
** answer.
*/
int sqlite3ExprNeedsNoAffinityChange(const Expr *p, char aff){
  u8 op;
  if( aff==SQLITE_AFF_NONE ) return 1;
  while( p->op==TK_UPLUS || p->op==TK_MINUS ){ p = p->pLeft; }
  op = p->op;
  if( op==TK_REGISTER ) op = p->op2;
  switch( op ){
    case TK_INTEGER: {
      return aff==SQLITE_AFF_INTEGER || aff==SQLITE_AFF_NUMERIC;
    }
    case TK_FLOAT: {
      return aff==SQLITE_AFF_REAL || aff==SQLITE_AFF_NUMERIC;
    }
    case TK_STRING: {
      return aff==SQLITE_AFF_TEXT;
    }
    case TK_BLOB: {
      return 1;
    }
    default: {
      return 0;
    }
  }
}

/*
** Return TRUE if the given string is a row-id column name.
*/
int sqlite3IsRowid(const char *z){
  if( sqlite3StrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqlite3StrICmp(z, "ROWID")==0 ) return 1;
Changes to src/sqliteInt.h.
2670
2671
2672
2673
2674
2675
2676


2677
2678
2679
2680
2681
2682
2683
void sqlite3RollbackTransaction(Parse*);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);
int sqlite3ExprIsConstantOrFunction(Expr*);
int sqlite3ExprIsInteger(Expr*, int*);


int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int);
void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int*);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);







>
>







2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
void sqlite3RollbackTransaction(Parse*);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);
int sqlite3ExprIsConstantOrFunction(Expr*);
int sqlite3ExprIsInteger(Expr*, int*);
int sqlite3ExprCanBeNull(const Expr*);
int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int);
void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int*);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
Changes to src/vdbe.c.
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300


2301
2302
2303
2304
2305

2306
2307
2308
2309
2310
2311
2312
** Apply affinities to a range of P2 registers starting with P1.
**
** P4 is a string that is P2 characters long. The nth character of the
** string indicates the column affinity that should be used for the nth
** memory cell in the range.
*/
case OP_Affinity: {
  char *zAffinity;   /* The affinity to be applied */
  Mem *pData0;       /* First register to which to apply affinity */
  Mem *pLast;        /* Last register to which to apply affinity */
  Mem *pRec;         /* Current register */

  zAffinity = pOp->p4.z;


  pData0 = &aMem[pOp->p1];
  pLast = &pData0[pOp->p2-1];
  for(pRec=pData0; pRec<=pLast; pRec++){
    ExpandBlob(pRec);
    applyAffinity(pRec, zAffinity[pRec-pData0], encoding);

  }
  break;
}

/* Opcode: MakeRecord P1 P2 P3 P4 *
**
** Convert P2 registers beginning with P1 into a single entry







|
<
<
|


>
>
|
|
|
|
|
>







2288
2289
2290
2291
2292
2293
2294
2295


2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
** Apply affinities to a range of P2 registers starting with P1.
**
** P4 is a string that is P2 characters long. The nth character of the
** string indicates the column affinity that should be used for the nth
** memory cell in the range.
*/
case OP_Affinity: {
  const char *zAffinity;   /* The affinity to be applied */


  char cAff;               /* A single character of affinity */

  zAffinity = pOp->p4.z;
  assert( zAffinity!=0 );
  assert( zAffinity[pOp->p2]==0 );
  pIn1 = &aMem[pOp->p1];
  while( (cAff = *(zAffinity++))!=0 ){
    assert( pIn1 <= &p->aMem[p->nMem] );
    ExpandBlob(pIn1);
    applyAffinity(pIn1, cAff, encoding);
    pIn1++;
  }
  break;
}

/* Opcode: MakeRecord P1 P2 P3 P4 *
**
** Convert P2 registers beginning with P1 into a single entry
3251
3252
3253
3254
3255
3256
3257

3258
3259
3260
3261
3262
3263
3264
      r.flags = UNPACKED_INCRKEY * (1 & (oc - OP_SeekLt));
      assert( oc!=OP_SeekGt || r.flags==UNPACKED_INCRKEY );
      assert( oc!=OP_SeekLe || r.flags==UNPACKED_INCRKEY );
      assert( oc!=OP_SeekGe || r.flags==0 );
      assert( oc!=OP_SeekLt || r.flags==0 );

      r.aMem = &aMem[pOp->p3];

      rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, &r, 0, 0, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->rowidIsValid = 0;
    }
    pC->deferredMoveto = 0;







>







3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
      r.flags = UNPACKED_INCRKEY * (1 & (oc - OP_SeekLt));
      assert( oc!=OP_SeekGt || r.flags==UNPACKED_INCRKEY );
      assert( oc!=OP_SeekLe || r.flags==UNPACKED_INCRKEY );
      assert( oc!=OP_SeekGe || r.flags==0 );
      assert( oc!=OP_SeekLt || r.flags==0 );

      r.aMem = &aMem[pOp->p3];
      ExpandBlob(r.aMem);
      rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, &r, 0, 0, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->rowidIsValid = 0;
    }
    pC->deferredMoveto = 0;
Changes to src/where.c.
2590
2591
2592
2593
2594
2595
2596




2597

2598
2599
2600




2601















2602
2603
2604

2605
2606
2607
2608
2609
2610
2611
  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**




** This routine assumes that zAff is dynamic and makes its own copy.

*/
static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  Vdbe *v = pParse->pVdbe;




  assert( v!=0 );















  sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
  sqlite3VdbeChangeP4(v, -1, zAff, 0);
  sqlite3ExprCacheAffinityChange(pParse, base, n);

}


/*
** Generate code for a single equality term of the WHERE clause.  An equality
** term can be either X=expr or X IN (...).   pTerm is the term to be 
** coded.







>
>
>
>
|
>



>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
>







2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**
** As an optimization, SQLITE_AFF_NONE entries (which are no-ops) at the
** beginning and end of zAff are ignored.  If all entries in zAff are
** SQLITE_AFF_NONE, then no code gets generated.
**
** This routine makes its own copy of zAff so that the caller is free
** to modify zAff after this routine returns.
*/
static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  Vdbe *v = pParse->pVdbe;
  if( zAff==0 ){
    assert( pParse->db->mallocFailed );
    return;
  }
  assert( v!=0 );

  /* Adjust base and n to skip over SQLITE_AFF_NONE entries at the beginning
  ** and end of the affinity string.
  */
  while( n>0 && zAff[0]==SQLITE_AFF_NONE ){
    n--;
    base++;
    zAff++;
  }
  while( n>1 && zAff[n-1]==SQLITE_AFF_NONE ){
    n--;
  }

  /* Code the OP_Affinity opcode if there is anything left to do. */
  if( n>0 ){
    sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
    sqlite3VdbeChangeP4(v, -1, zAff, n);
    sqlite3ExprCacheAffinityChange(pParse, base, n);
  }
}


/*
** Generate code for a single equality term of the WHERE clause.  An equality
** term can be either X=expr or X IN (...).   pTerm is the term to be 
** coded.
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687

2688
2689
2690
2691
2692
2693
2694
  }
  disableTerm(pLevel, pTerm);
  return iReg;
}

/*
** Generate code that will evaluate all == and IN constraints for an
** index.  The values for all constraints are left on the stack.
**
** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
** Suppose the WHERE clause is this:  a==5 AND b IN (1,2,3) AND c>5 AND c<10
** The index has as many as three equality constraints, but in this
** example, the third "c" value is an inequality.  So only two 
** constraints are coded.  This routine will generate code to evaluate
** a==5 and b IN (1,2,3).  The current values for a and b will be stored
** in consecutive registers and the index of the first register is returned.
**
** In the example above nEq==2.  But this subroutine works for any value
** of nEq including 0.  If nEq==0, this routine is nearly a no-op.
** The only thing it does is allocate the pLevel->iMem memory cell.

**
** This routine always allocates at least one memory cell and returns
** the index of that memory cell. The code that
** calls this routine will use that memory cell to store the termination
** key value of the loop.  If one or more IN operators appear, then
** this routine allocates an additional nEq memory cells for internal
** use.







|











|
>







2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
  }
  disableTerm(pLevel, pTerm);
  return iReg;
}

/*
** Generate code that will evaluate all == and IN constraints for an
** index.
**
** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
** Suppose the WHERE clause is this:  a==5 AND b IN (1,2,3) AND c>5 AND c<10
** The index has as many as three equality constraints, but in this
** example, the third "c" value is an inequality.  So only two 
** constraints are coded.  This routine will generate code to evaluate
** a==5 and b IN (1,2,3).  The current values for a and b will be stored
** in consecutive registers and the index of the first register is returned.
**
** In the example above nEq==2.  But this subroutine works for any value
** of nEq including 0.  If nEq==0, this routine is nearly a no-op.
** The only thing it does is allocate the pLevel->iMem memory cell and
** compute the affinity string.
**
** This routine always allocates at least one memory cell and returns
** the index of that memory cell. The code that
** calls this routine will use that memory cell to store the termination
** key value of the loop.  If one or more IN operators appear, then
** this routine allocates an additional nEq memory cells for internal
** use.
2758
2759
2760
2761
2762
2763
2764


2765

2766
2767
2768
2769




2770
2771
2772
2773
2774
2775
2776
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
      }
    }
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IN );
    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){


      sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);

      if( zAff 
       && sqlite3CompareAffinity(pTerm->pExpr->pRight, zAff[j])==SQLITE_AFF_NONE
      ){
        zAff[j] = SQLITE_AFF_NONE;




      }
    }
  }
  *pzAff = zAff;
  return regBase;
}








>
>
|
>
|
|
<
|
>
>
>
>







2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796

2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
      }
    }
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IN );
    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
      Expr *pRight = pTerm->pExpr->pRight;
      if( sqlite3ExprCanBeNull(pRight) ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
      }
      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){

          zAff[j] = SQLITE_AFF_NONE;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
          zAff[j] = SQLITE_AFF_NONE;
        }
      }
    }
  }
  *pzAff = zAff;
  return regBase;
}

3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103




3104
3105
3106
3107
3108
3109
3110

    /* Seek the index cursor to the start of the range. */
    nConstraint = nEq;
    if( pRangeStart ){
      Expr *pRight = pRangeStart->pExpr->pRight;
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
      if( zAff 
       && sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE
      ){
        /* Since the comparison is to be performed with no conversions applied
        ** to the operands, set the affinity to apply to pRight to 
        ** SQLITE_AFF_NONE.  */
        zAff[nConstraint] = SQLITE_AFF_NONE;
      }




      nConstraint++;
    }else if( isMinQuery ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
      nConstraint++;
      startEq = 0;
      start_constraints = 1;
    }







|
|
<
|
|
|
|
|
>
>
>
>







3121
3122
3123
3124
3125
3126
3127
3128
3129

3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145

    /* Seek the index cursor to the start of the range. */
    nConstraint = nEq;
    if( pRangeStart ){
      Expr *pRight = pRangeStart->pExpr->pRight;
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE){

          /* Since the comparison is to be performed with no conversions
          ** applied to the operands, set the affinity to apply to pRight to 
          ** SQLITE_AFF_NONE.  */
          zAff[nConstraint] = SQLITE_AFF_NONE;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[nConstraint]) ){
          zAff[nConstraint] = SQLITE_AFF_NONE;
        }
      }  
      nConstraint++;
    }else if( isMinQuery ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
      nConstraint++;
      startEq = 0;
      start_constraints = 1;
    }
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138




3139
3140
3141
3142
3143
3144
3145
    */
    nConstraint = nEq;
    if( pRangeEnd ){
      Expr *pRight = pRangeEnd->pExpr->pRight;
      sqlite3ExprCacheRemove(pParse, regBase+nEq);
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
      if( zAff 
       && sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE
      ){
        /* Since the comparison is to be performed with no conversions applied
        ** to the operands, set the affinity to apply to pRight to 
        ** SQLITE_AFF_NONE.  */
        zAff[nConstraint] = SQLITE_AFF_NONE;
      }




      codeApplyAffinity(pParse, regBase, nEq+1, zAff);
      nConstraint++;
    }
    sqlite3DbFree(pParse->db, zAff);

    /* Top of the loop body */
    pLevel->p2 = sqlite3VdbeCurrentAddr(v);







|
|
<
|
|
|
|
|
>
>
>
>







3159
3160
3161
3162
3163
3164
3165
3166
3167

3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
    */
    nConstraint = nEq;
    if( pRangeEnd ){
      Expr *pRight = pRangeEnd->pExpr->pRight;
      sqlite3ExprCacheRemove(pParse, regBase+nEq);
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE){

          /* Since the comparison is to be performed with no conversions
          ** applied to the operands, set the affinity to apply to pRight to 
          ** SQLITE_AFF_NONE.  */
          zAff[nConstraint] = SQLITE_AFF_NONE;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[nConstraint]) ){
          zAff[nConstraint] = SQLITE_AFF_NONE;
        }
      }  
      codeApplyAffinity(pParse, regBase, nEq+1, zAff);
      nConstraint++;
    }
    sqlite3DbFree(pParse->db, zAff);

    /* Top of the loop body */
    pLevel->p2 = sqlite3VdbeCurrentAddr(v);