/ Check-in [b2e49ae3]
Login

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

Overview
Comment:Ensure that indexed expressions with collating sequences are handled correctly. Fix for ticket [eb703ba7b50c1a5] backported from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.17
Files: files | file ages | folders
SHA1: b2e49ae36bfd00a1d0b70a9de9d23d2e16d1c7ca
User & Date: drh 2017-02-11 15:02:23
Context
2017-02-13
11:35
Fix typos in using the MSVC_VERSION macro. check-in: 25ebadd0 user: drh tags: branch-3.17
2017-02-11
15:02
Ensure that indexed expressions with collating sequences are handled correctly. Fix for ticket [eb703ba7b50c1a5] backported from trunk. check-in: b2e49ae3 user: drh tags: branch-3.17
13:51
Ensure that indexed expressions with collating sequences are handled correctly. Proposed fix for ticket [eb703ba7b50c1a5]. check-in: 9689d04b user: drh tags: trunk
2017-02-10
17:38
Version 3.17.0 release candidate check-in: ad867e87 user: drh tags: branch-3.17
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

   227    227             pExpr->op==TK_NE || pExpr->op==TK_IS || pExpr->op==TK_ISNOT );
   228    228     assert( pExpr->pLeft );
   229    229     aff = sqlite3ExprAffinity(pExpr->pLeft);
   230    230     if( pExpr->pRight ){
   231    231       aff = sqlite3CompareAffinity(pExpr->pRight, aff);
   232    232     }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){
   233    233       aff = sqlite3CompareAffinity(pExpr->x.pSelect->pEList->a[0].pExpr, aff);
   234         -  }else if( NEVER(aff==0) ){
          234  +  }else if( aff==0 ){
   235    235       aff = SQLITE_AFF_BLOB;
   236    236     }
   237    237     return aff;
   238    238   }
   239    239   
   240    240   /*
   241    241   ** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.

Changes to src/where.c.

   304    304     pScan->idxaff = 0;
   305    305     pScan->zCollName = 0;
   306    306     if( pIdx ){
   307    307       int j = iColumn;
   308    308       iColumn = pIdx->aiColumn[j];
   309    309       if( iColumn==XN_EXPR ){
   310    310         pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
          311  +      pScan->zCollName = pIdx->azColl[j];
   311    312       }else if( iColumn==pIdx->pTable->iPKey ){
   312    313         iColumn = XN_ROWID;
   313    314       }else if( iColumn>=0 ){
   314    315         pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
   315    316         pScan->zCollName = pIdx->azColl[j];
   316    317       }
   317    318     }else if( iColumn==XN_EXPR ){

Changes to test/indexexpr1.test.

   365    365     CREATE INDEX t10_abcd ON t10(a+b,c+d);
   366    366   }
   367    367   do_execsql_test indexexpr1-1200.4 {
   368    368     SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
   369    369   } {
   370    370     0 0 0 2 0 4 2 0 2 2 4 0
   371    371   }
          372  +
          373  +# Ticket https://www.sqlite.org/src/tktview/eb703ba7b50c1a
          374  +# Incorrect result using an index on an expression with a collating function
          375  +#
          376  +do_execsql_test indexexpr1-1300.1 {
          377  +  CREATE TABLE t1300(a INTEGER PRIMARY KEY, b);
          378  +  INSERT INTO t1300 VALUES(1,'coffee'),(2,'COFFEE'),(3,'stress'),(4,'STRESS');
          379  +  CREATE INDEX t1300bexpr ON t1300( substr(b,4) );
          380  +  SELECT a FROM t1300 WHERE substr(b,4)='ess' COLLATE nocase ORDER BY +a;
          381  +} {3 4}
   372    382   
   373    383   finish_test