Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Indexes on expressions with a COLLATE clause are able to satisfy an ORDER BY with the same COLLATE clause. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
0413001843dce7c63659d39b329ca14c |
| User & Date: | drh 2017-09-28 01:09:42 |
|
2017-09-28
| ||
| 01:58 | Add new routines to simplify dealing with collating sequences in expressions: sqlite3ExprNNCollSeq() and sqlite3ExprCollSeqMatch(). (check-in: 490e488e user: drh tags: trunk) | |
| 01:09 | Indexes on expressions with a COLLATE clause are able to satisfy an ORDER BY with the same COLLATE clause. (check-in: 04130018 user: drh tags: trunk) | |
| 00:01 | In two places, change the magic number -1 to its proper symbol XN_ROWID. (check-in: 80277d2f user: drh tags: trunk) | |
Changes to src/where.c.
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
....
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
|
if( pExpr->iColumn<0 ) return 1;
for(jj=0; jj<pIndex->nKeyCol; jj++){
if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
}
}else if( (aColExpr = pIndex->aColExpr)!=0 ){
for(jj=0; jj<pIndex->nKeyCol; jj++){
if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
if( sqlite3ExprCompare(0, pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
return 1;
}
}
}
}
return 0;
}
................................................................................
testcase( wctrlFlags & WHERE_DISTINCTBY );
if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
if( iColumn>=XN_ROWID ){
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
if( pOBExpr->iColumn!=iColumn ) continue;
}else{
if( sqlite3ExprCompare(0,
pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){
continue;
}
}
if( iColumn!=XN_ROWID ){
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
|
|
|
|
|
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
....
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
|
if( pExpr->iColumn<0 ) return 1;
for(jj=0; jj<pIndex->nKeyCol; jj++){
if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
}
}else if( (aColExpr = pIndex->aColExpr)!=0 ){
for(jj=0; jj<pIndex->nKeyCol; jj++){
if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
if( sqlite3ExprCompareSkip(pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
return 1;
}
}
}
}
return 0;
}
................................................................................
testcase( wctrlFlags & WHERE_DISTINCTBY );
if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
if( iColumn>=XN_ROWID ){
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
if( pOBExpr->iColumn!=iColumn ) continue;
}else{
Expr *pIdxExpr = pIndex->aColExpr->a[j].pExpr;
if( sqlite3ExprCompareSkip(pOBExpr, pIdxExpr, iCur) ){
continue;
}
}
if( iColumn!=XN_ROWID ){
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
|
Changes to test/indexexpr2.test.
131 132 133 134 135 136 137 138 139 |
CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b );
SELECT * FROM t4
WHERE substr(a, 2) = 'abc' COLLATE NOCASE
ORDER BY substr(a, 2), b;
} {
.ABC 1 .ABC 3 .abc 2 .abc 4
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > |
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b );
SELECT * FROM t4
WHERE substr(a, 2) = 'abc' COLLATE NOCASE
ORDER BY substr(a, 2), b;
} {
.ABC 1 .ABC 3 .abc 2 .abc 4
}
do_execsql_test 3.4.3 {
DROP INDEX i4;
UPDATE t4 SET a = printf('%s%d',a,b);
SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase;
} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4}
do_execsql_test 3.4.4 {
SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary;
} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4}
do_execsql_test 3.4.5 {
CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase );
SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase;
} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4}
do_execsql_test 3.4.5eqp {
EXPLAIN QUERY PLAN
SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase;
} {/SCAN TABLE t4 USING INDEX i4/}
do_execsql_test 3.4.6 {
SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary;
} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4}
finish_test
|