/ Check-in [2c886f3d]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:2c886f3d49c9569e9f6ee28b89b57e2d4004c8972d11da0c23f7b2d2e0c9900a
User & Date: drh 2019-01-28 19:06:17
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: 119d1609 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: 2c886f3d user: drh tags: trunk
18:58
Fix a performance regression caused by the previous commit. Closed-Leaf check-in: c4db0ad1 user: drh tags: index-on-cast
16:50
Fix a buffer overread in fts3 that could occur when accessing a corrupt database. check-in: a9faf903 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

305
306
307
308
309
310
311











312
313
314
315
316
317
318
...
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
    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
................................................................................
  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







>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>






>
>









<
<
<

<
<







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
...
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
    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
................................................................................
  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