/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   305    305       if( pScan->iEquiv>=pScan->nEquiv ) break;
   306    306       pWC = pScan->pOrigWC;
   307    307       k = 0;
   308    308       pScan->iEquiv++;
   309    309     }
   310    310     return 0;
   311    311   }
          312  +
          313  +/*
          314  +** This is whereScanInit() for the case of an index on an expression.
          315  +** It is factored out into a separate tail-recursion subroutine so that
          316  +** the normal whereScanInit() routine, which is a high-runner, does not
          317  +** need to push registers onto the stack as part of its prologue.
          318  +*/
          319  +static SQLITE_NOINLINE WhereTerm *whereScanInitIndexExpr(WhereScan *pScan){
          320  +  pScan->idxaff = sqlite3ExprAffinity(pScan->pIdxExpr);
          321  +  return whereScanNext(pScan);
          322  +}
   312    323   
   313    324   /*
   314    325   ** Initialize a WHERE clause scanner object.  Return a pointer to the
   315    326   ** first match.  Return NULL if there are no matches.
   316    327   **
   317    328   ** The scanner will be searching the WHERE clause pWC.  It will look
   318    329   ** for terms of the form "X <op> <expr>" where X is column iColumn of table
................................................................................
   338    349     Index *pIdx             /* Must be compatible with this index */
   339    350   ){
   340    351     pScan->pOrigWC = pWC;
   341    352     pScan->pWC = pWC;
   342    353     pScan->pIdxExpr = 0;
   343    354     pScan->idxaff = 0;
   344    355     pScan->zCollName = 0;
          356  +  pScan->opMask = opMask;
          357  +  pScan->k = 0;
          358  +  pScan->aiCur[0] = iCur;
          359  +  pScan->nEquiv = 1;
          360  +  pScan->iEquiv = 1;
   345    361     if( pIdx ){
   346    362       int j = iColumn;
   347    363       iColumn = pIdx->aiColumn[j];
   348    364       if( iColumn==XN_EXPR ){
   349    365         pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
   350    366         pScan->zCollName = pIdx->azColl[j];
          367  +      pScan->aiColumn[0] = XN_EXPR;
          368  +      return whereScanInitIndexExpr(pScan);
   351    369       }else if( iColumn==pIdx->pTable->iPKey ){
   352    370         iColumn = XN_ROWID;
   353    371       }else if( iColumn>=0 ){
   354    372         pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
   355    373         pScan->zCollName = pIdx->azColl[j];
   356    374       }
   357    375     }else if( iColumn==XN_EXPR ){
   358    376       return 0;
   359    377     }
   360         -  pScan->opMask = opMask;
   361         -  pScan->k = 0;
   362         -  pScan->aiCur[0] = iCur;
   363    378     pScan->aiColumn[0] = iColumn;
   364         -  pScan->nEquiv = 1;
   365         -  pScan->iEquiv = 1;
   366    379     return whereScanNext(pScan);
   367    380   }
   368    381   
   369    382   /*
   370    383   ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
   371    384   ** where X is a reference to the iColumn of table iCur or of index pIdx
   372    385   ** if pIdx!=0 and <op> is one of the WO_xx operator codes specified by

Changes to test/indexexpr2.test.

   245    245     CREATE INDEX t5a ON t5( abs(a) );
   246    246     CREATE INDEX t5b ON t5( abs(b) );
   247    247   }
   248    248   do_execsql_test 5.4 {
   249    249     SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9;
   250    250   } {2 4 3 9}
   251    251   
          252  +#-------------------------------------------------------------------------
          253  +do_execsql_test 6.0 {
          254  +  CREATE TABLE x1(a INTEGER PRIMARY KEY, b);
          255  +  INSERT INTO x1 VALUES
          256  +      (1, 123), (2, '123'), (3, '123abc'), (4, 123.0), (5, 1234);
          257  +}
          258  +
          259  +do_execsql_test 6.1.1 {
          260  +  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
          261  +} {1 123   2 123   3 123abc  4 123.0}
          262  +do_execsql_test 6.1.2 {
          263  +  CREATE INDEX x1i ON x1( CAST(b AS INTEGER) );
          264  +  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
          265  +} {1 123   2 123   3 123abc  4 123.0}
          266  +do_eqp_test 6.1.3 {
          267  +  SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123;
          268  +} {SEARCH TABLE x1 USING INDEX x1i (<expr>=?)}
          269  +
          270  +do_execsql_test 6.2.1 {
          271  +  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
          272  +} {1 123   2 123}
          273  +do_execsql_test 6.2.2 {
          274  +  CREATE INDEX x1i2 ON x1( CAST(b AS TEXT) );
          275  +  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
          276  +} {1 123   2 123}
          277  +do_eqp_test 6.2.3 {
          278  +  SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123;
          279  +} {SEARCH TABLE x1 USING INDEX x1i2 (<expr>=?)}
   252    280   
   253    281   
   254    282   finish_test