SQLite

Check-in [c9724e761b]
Login

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

Overview
Comment:Performance optimization to the new affinity handling logic.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | pending
Files: files | file ages | folders
SHA3-256: c9724e761bce7a4ae63ce3c1408795915865e8d3024dcb90690456f724f0df53
User & Date: drh 2019-08-06 15:18:15.358
Context
2019-08-06
15:32
Ensure that columns of views and sub-queries that are expressions with no affinity are comparied without any type conversions, as required in the documentation. Tickets [61c853857f40da49] and [d52a29a9e6bc55c5]. (check-in: 9c8c1092a8 user: drh tags: trunk)
15:18
Performance optimization to the new affinity handling logic. (Closed-Leaf check-in: c9724e761b user: drh tags: pending)
14:37
Use 0x40 (ASCII '@') instead of 0x00 to mean "no affinity" so that columns with no affinity can appear in a zero-terminated string. Use the new SQLITE_AFF_NONE macro for this new magic number. (check-in: e8234f6939 user: drh tags: pending)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253

    ** affinity, use that. Otherwise use no affinity.
    */
    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_BLOB;
    }
  }else if( aff1<=SQLITE_AFF_NONE && aff2<=SQLITE_AFF_NONE ){
    /* Neither side of the comparison is a column.  Compare the
    ** results directly.
    */
    return SQLITE_AFF_BLOB;
  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    assert( aff1<=SQLITE_AFF_NONE || aff2<=SQLITE_AFF_NONE );
    return (aff1<=SQLITE_AFF_NONE ? aff2 : aff1) | SQLITE_AFF_NONE;
  }
}









<
<
<
<
<








235
236
237
238
239
240
241





242
243
244
245
246
247
248

    ** affinity, use that. Otherwise use no affinity.
    */
    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_BLOB;
    }





  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    assert( aff1<=SQLITE_AFF_NONE || aff2<=SQLITE_AFF_NONE );
    return (aff1<=SQLITE_AFF_NONE ? aff2 : aff1) | SQLITE_AFF_NONE;
  }
}
︙︙
276
277
278
279
280
281
282
283
284
285

286
287
288
289
290

291
292
293
294
295
296
297

** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);
  switch( aff ){
    case SQLITE_AFF_BLOB:
      return 1;

    case SQLITE_AFF_TEXT:
      return idx_affinity==SQLITE_AFF_TEXT;
    default:
      return sqlite3IsNumericAffinity(idx_affinity);
  }

}

/*
** Return the P5 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
*/
static u8 binaryCompareP5(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){








<
|
|
>
|
|
<
<

>








271
272
273
274
275
276
277

278
279
280
281
282


283
284
285
286
287
288
289
290
291

** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);

  if( aff<SQLITE_AFF_TEXT ){
    return 1;
  }
  if( aff==SQLITE_AFF_TEXT ){
    return idx_affinity==SQLITE_AFF_TEXT;


  }
  return sqlite3IsNumericAffinity(idx_affinity);
}

/*
** Return the P5 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
*/
static u8 binaryCompareP5(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){
︙︙