SQLite

Check-in [0413001843]
Login

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

Overview
Comment:Indexes on expressions with a COLLATE clause are able to satisfy an ORDER BY with the same COLLATE clause.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0413001843dce7c63659d39b329ca14cdcd54f4407922f51b2fb7659572a733e
User & Date: drh 2017-09-28 01:09:42.223
Context
2017-09-28
01:58
Add new routines to simplify dealing with collating sequences in expressions: sqlite3ExprNNCollSeq() and sqlite3ExprCollSeqMatch(). (check-in: 490e488ea9 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: 0413001843 user: drh tags: trunk)
00:01
In two places, change the magic number -1 to its proper symbol XN_ROWID. (check-in: 80277d2fc9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679

      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;
}








|








2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679

      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;
}
︙︙
3686
3687
3688
3689
3690
3691
3692

3693
3694
3695
3696
3697
3698
3699
3700
3701

          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;








>
|
<








3686
3687
3688
3689
3690
3691
3692
3693
3694

3695
3696
3697
3698
3699
3700
3701

          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