/ Check-in [3ef711d9]
Login

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

Overview
Comment:Make indexes on CAST(...) expressions work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | index-on-cast
Files: files | file ages | folders
SHA3-256: 3ef711d98fb239cf24472f124e7b36b0dde33355de5a2c9a3a978bbdd042a735
User & Date: dan 2019-01-28 18:08:59
Context
2019-01-28
18:58
Fix a performance regression caused by the previous commit. Closed-Leaf check-in: c4db0ad1 user: drh tags: index-on-cast
18:08
Make indexes on CAST(...) expressions work. check-in: 3ef711d9 user: dan 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.

   344    344     pScan->zCollName = 0;
   345    345     if( pIdx ){
   346    346       int j = iColumn;
   347    347       iColumn = pIdx->aiColumn[j];
   348    348       if( iColumn==XN_EXPR ){
   349    349         pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
   350    350         pScan->zCollName = pIdx->azColl[j];
          351  +      pScan->idxaff = sqlite3ExprAffinity(pScan->pIdxExpr);
   351    352       }else if( iColumn==pIdx->pTable->iPKey ){
   352    353         iColumn = XN_ROWID;
   353    354       }else if( iColumn>=0 ){
   354    355         pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
   355    356         pScan->zCollName = pIdx->azColl[j];
   356    357       }
   357    358     }else if( iColumn==XN_EXPR ){

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