SQLite

Check-in [2c886f3d49]
Login

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

Overview
Comment:Fix the query planner so that it is able to use an index on a CAST expression.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2c886f3d49c9569e9f6ee28b89b57e2d4004c8972d11da0c23f7b2d2e0c9900a
User & Date: drh 2019-01-28 19:06:17.490
Context
2019-01-29
02:37
Set a low limit on the length of strings and blobs in the OSSFuzz module, in an effort to avoid timeouts when the fuzzer does things like "randomblob(1e12)". (check-in: 119d1609fa user: drh tags: trunk)
2019-01-28
19:06
Fix the query planner so that it is able to use an index on a CAST expression. (check-in: 2c886f3d49 user: drh tags: trunk)
18:58
Fix a performance regression caused by the previous commit. (Closed-Leaf check-in: c4db0ad12d user: drh tags: index-on-cast)
16:50
Fix a buffer overread in fts3 that could occur when accessing a corrupt database. (check-in: a9faf90339 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
305
306
307
308
309
310
311











312
313
314
315
316
317
318
    if( pScan->iEquiv>=pScan->nEquiv ) break;
    pWC = pScan->pOrigWC;
    k = 0;
    pScan->iEquiv++;
  }
  return 0;
}












/*
** Initialize a WHERE clause scanner object.  Return a pointer to the
** first match.  Return NULL if there are no matches.
**
** The scanner will be searching the WHERE clause pWC.  It will look
** for terms of the form "X <op> <expr>" where X is column iColumn of table







>
>
>
>
>
>
>
>
>
>
>







305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
    if( pScan->iEquiv>=pScan->nEquiv ) break;
    pWC = pScan->pOrigWC;
    k = 0;
    pScan->iEquiv++;
  }
  return 0;
}

/*
** This is whereScanInit() for the case of an index on an expression.
** It is factored out into a separate tail-recursion subroutine so that
** the normal whereScanInit() routine, which is a high-runner, does not
** need to push registers onto the stack as part of its prologue.
*/
static SQLITE_NOINLINE WhereTerm *whereScanInitIndexExpr(WhereScan *pScan){
  pScan->idxaff = sqlite3ExprAffinity(pScan->pIdxExpr);
  return whereScanNext(pScan);
}

/*
** Initialize a WHERE clause scanner object.  Return a pointer to the
** first match.  Return NULL if there are no matches.
**
** The scanner will be searching the WHERE clause pWC.  It will look
** for terms of the form "X <op> <expr>" where X is column iColumn of table
338
339
340
341
342
343
344





345
346
347
348
349
350


351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
  Index *pIdx             /* Must be compatible with this index */
){
  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  pScan->pIdxExpr = 0;
  pScan->idxaff = 0;
  pScan->zCollName = 0;





  if( pIdx ){
    int j = iColumn;
    iColumn = pIdx->aiColumn[j];
    if( iColumn==XN_EXPR ){
      pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
      pScan->zCollName = pIdx->azColl[j];


    }else if( iColumn==pIdx->pTable->iPKey ){
      iColumn = XN_ROWID;
    }else if( iColumn>=0 ){
      pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
      pScan->zCollName = pIdx->azColl[j];
    }
  }else if( iColumn==XN_EXPR ){
    return 0;
  }
  pScan->opMask = opMask;
  pScan->k = 0;
  pScan->aiCur[0] = iCur;
  pScan->aiColumn[0] = iColumn;
  pScan->nEquiv = 1;
  pScan->iEquiv = 1;
  return whereScanNext(pScan);
}

/*
** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
** where X is a reference to the iColumn of table iCur or of index pIdx
** if pIdx!=0 and <op> is one of the WO_xx operator codes specified by







>
>
>
>
>






>
>









<
<
<

<
<







349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377



378


379
380
381
382
383
384
385
  Index *pIdx             /* Must be compatible with this index */
){
  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  pScan->pIdxExpr = 0;
  pScan->idxaff = 0;
  pScan->zCollName = 0;
  pScan->opMask = opMask;
  pScan->k = 0;
  pScan->aiCur[0] = iCur;
  pScan->nEquiv = 1;
  pScan->iEquiv = 1;
  if( pIdx ){
    int j = iColumn;
    iColumn = pIdx->aiColumn[j];
    if( iColumn==XN_EXPR ){
      pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
      pScan->zCollName = pIdx->azColl[j];
      pScan->aiColumn[0] = XN_EXPR;
      return whereScanInitIndexExpr(pScan);
    }else if( iColumn==pIdx->pTable->iPKey ){
      iColumn = XN_ROWID;
    }else if( iColumn>=0 ){
      pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
      pScan->zCollName = pIdx->azColl[j];
    }
  }else if( iColumn==XN_EXPR ){
    return 0;
  }



  pScan->aiColumn[0] = iColumn;


  return whereScanNext(pScan);
}

/*
** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
** where X is a reference to the iColumn of table iCur or of index pIdx
** if pIdx!=0 and <op> is one of the WO_xx operator codes specified by
Changes to test/indexexpr2.test.
245
246
247
248
249
250
251




























252
253
254
  CREATE INDEX t5a ON t5( abs(a) );
  CREATE INDEX t5b ON t5( abs(b) );
}
do_execsql_test 5.4 {
  SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9;
} {2 4 3 9}































finish_test







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



245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  CREATE INDEX t5a ON t5( abs(a) );
  CREATE INDEX t5b ON t5( abs(b) );
}
do_execsql_test 5.4 {
  SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9;
} {2 4 3 9}

#-------------------------------------------------------------------------
do_execsql_test 6.0 {
  CREATE TABLE x1(a INTEGER PRIMARY KEY, b);
  INSERT INTO x1 VALUES
      (1, 123), (2, '123'), (3, '123abc'), (4, 123.0), (5, 1234);
}

do_execsql_test 6.1.1 {
  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
} {1 123   2 123   3 123abc  4 123.0}
do_execsql_test 6.1.2 {
  CREATE INDEX x1i ON x1( CAST(b AS INTEGER) );
  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
} {1 123   2 123   3 123abc  4 123.0}
do_eqp_test 6.1.3 {
  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
} {SEARCH TABLE x1 USING INDEX x1i (<expr>=?)}

do_execsql_test 6.2.1 {
  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
} {1 123   2 123}
do_execsql_test 6.2.2 {
  CREATE INDEX x1i2 ON x1( CAST(b AS TEXT) );
  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
} {1 123   2 123}
do_eqp_test 6.2.3 {
  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
} {SEARCH TABLE x1 USING INDEX x1i2 (<expr>=?)}


finish_test