/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

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

/*
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.







|







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

/*
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.

Changes to src/where.c.

304
305
306
307
308
309
310

311
312
313
314
315
316
317
  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;

    }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 ){







>







304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
  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 ){

Changes to test/indexexpr1.test.

365
366
367
368
369
370
371
372










373
  CREATE INDEX t10_abcd ON t10(a+b,c+d);
}
do_execsql_test indexexpr1-1200.4 {
  SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
} {
  0 0 0 2 0 4 2 0 2 2 4 0
}











finish_test








>
>
>
>
>
>
>
>
>
>

365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
  CREATE INDEX t10_abcd ON t10(a+b,c+d);
}
do_execsql_test indexexpr1-1200.4 {
  SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
} {
  0 0 0 2 0 4 2 0 2 2 4 0
}

# Ticket https://www.sqlite.org/src/tktview/eb703ba7b50c1a
# Incorrect result using an index on an expression with a collating function
#
do_execsql_test indexexpr1-1300.1 {
  CREATE TABLE t1300(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1300 VALUES(1,'coffee'),(2,'COFFEE'),(3,'stress'),(4,'STRESS');
  CREATE INDEX t1300bexpr ON t1300( substr(b,4) );
  SELECT a FROM t1300 WHERE substr(b,4)='ess' COLLATE nocase ORDER BY +a;
} {3 4}

finish_test