/ Check-in [490e488e]
Login

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

Overview
Comment:Add new routines to simplify dealing with collating sequences in expressions: sqlite3ExprNNCollSeq() and sqlite3ExprCollSeqMatch().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 490e488ea963fe725b16212822c8608f2b6abce688931b611446bc2cbfe6b87c
User & Date: drh 2017-09-28 01:58:23
Context
2017-09-28
13:47
Fix an issue introduced by check-in [4cd2a9672c59] (2017-03-03) that could allow a negative value in the 3rd parameter to memmove() when defragmentPage() is called on a btree page with a corrupted freeblock list. The corruption is now detected early and results in an SQLITE_CORRUPT return before the memmove() is reached. check-in: 5b9ae693 user: drh tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

   120    120     return pExpr;
   121    121   }
   122    122   
   123    123   /*
   124    124   ** Return the collation sequence for the expression pExpr. If
   125    125   ** there is no defined collating sequence, return NULL.
   126    126   **
          127  +** See also: sqlite3ExprNNCollSeq()
          128  +**
          129  +** The sqlite3ExprNNCollSeq() works the same exact that it returns the
          130  +** default collation if pExpr has no defined collation.
          131  +**
   127    132   ** The collating sequence might be determined by a COLLATE operator
   128    133   ** or by the presence of a column with a defined collating sequence.
   129    134   ** COLLATE operators take first precedence.  Left operands take
   130    135   ** precedence over right operands.
   131    136   */
   132    137   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
   133    138     sqlite3 *db = pParse->db;
................................................................................
   183    188       }
   184    189     }
   185    190     if( sqlite3CheckCollSeq(pParse, pColl) ){ 
   186    191       pColl = 0;
   187    192     }
   188    193     return pColl;
   189    194   }
          195  +
          196  +/*
          197  +** Return the collation sequence for the expression pExpr. If
          198  +** there is no defined collating sequence, return a pointer to the
          199  +** defautl collation sequence.
          200  +**
          201  +** See also: sqlite3ExprCollSeq()
          202  +**
          203  +** The sqlite3ExprCollSeq() routine works the same except that it
          204  +** returns NULL if there is no defined collation.
          205  +*/
          206  +CollSeq *sqlite3ExprNNCollSeq(Parse *pParse, Expr *pExpr){
          207  +  CollSeq *p = sqlite3ExprCollSeq(pParse, pExpr);
          208  +  if( p==0 ) p = pParse->db->pDfltColl;
          209  +  assert( p!=0 );
          210  +  return p;
          211  +}
          212  +
          213  +/*
          214  +** Return TRUE if the two expressions have equivalent collating sequences.
          215  +*/
          216  +int sqlite3ExprCollSeqMatch(Parse *pParse, Expr *pE1, Expr *pE2){
          217  +  CollSeq *pColl1 = sqlite3ExprNNCollSeq(pParse, pE1);
          218  +  CollSeq *pColl2 = sqlite3ExprNNCollSeq(pParse, pE2);
          219  +  return sqlite3StrICmp(pColl1->zName, pColl2->zName)==0;
          220  +}
   190    221   
   191    222   /*
   192    223   ** pExpr is an operand of a comparison operator.  aff2 is the
   193    224   ** type affinity of the other operand.  This routine returns the
   194    225   ** type affinity that should be used for the comparison operator.
   195    226   */
   196    227   char sqlite3CompareAffinity(Expr *pExpr, char aff2){
................................................................................
  1839   1870     int i;
  1840   1871   
  1841   1872     /* Check if pExpr is identical to any GROUP BY term. If so, consider
  1842   1873     ** it constant.  */
  1843   1874     for(i=0; i<pGroupBy->nExpr; i++){
  1844   1875       Expr *p = pGroupBy->a[i].pExpr;
  1845   1876       if( sqlite3ExprCompare(0, pExpr, p, -1)<2 ){
  1846         -      CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p);
  1847         -      if( pColl==0 || sqlite3_stricmp("BINARY", pColl->zName)==0 ){
         1877  +      CollSeq *pColl = sqlite3ExprNNCollSeq(pWalker->pParse, p);
         1878  +      if( sqlite3_stricmp("BINARY", pColl->zName)==0 ){
  1848   1879           return WRC_Prune;
  1849   1880         }
  1850   1881       }
  1851   1882     }
  1852   1883   
  1853   1884     /* Check if pExpr is a sub-select. If so, consider it variable. */
  1854   1885     if( ExprHasProperty(pExpr, EP_xIsSelect) ){

Changes to src/select.c.

  1105   1105     int i;
  1106   1106   
  1107   1107     nExpr = pList->nExpr;
  1108   1108     pInfo = sqlite3KeyInfoAlloc(db, nExpr-iStart, nExtra+1);
  1109   1109     if( pInfo ){
  1110   1110       assert( sqlite3KeyInfoIsWriteable(pInfo) );
  1111   1111       for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){
  1112         -      CollSeq *pColl;
  1113         -      pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
  1114         -      if( !pColl ) pColl = db->pDfltColl;
  1115         -      pInfo->aColl[i-iStart] = pColl;
         1112  +      pInfo->aColl[i-iStart] = sqlite3ExprNNCollSeq(pParse, pItem->pExpr);
  1116   1113         pInfo->aSortOrder[i-iStart] = pItem->sortOrder;
  1117   1114       }
  1118   1115     }
  1119   1116     return pInfo;
  1120   1117   }
  1121   1118   
  1122   1119   /*

Changes to src/sqliteInt.h.

  4000   4000   #endif
  4001   4001   
  4002   4002   const char *sqlite3ErrStr(int);
  4003   4003   int sqlite3ReadSchema(Parse *pParse);
  4004   4004   CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int);
  4005   4005   CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName);
  4006   4006   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
         4007  +CollSeq *sqlite3ExprNNCollSeq(Parse *pParse, Expr *pExpr);
         4008  +int sqlite3ExprCollSeqMatch(Parse*,Expr*,Expr*);
  4007   4009   Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, const Token*, int);
  4008   4010   Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*);
  4009   4011   Expr *sqlite3ExprSkipCollate(Expr*);
  4010   4012   int sqlite3CheckCollSeq(Parse *, CollSeq *);
  4011   4013   int sqlite3CheckObjectName(Parse *, const char *);
  4012   4014   void sqlite3VdbeSetChanges(sqlite3 *, int);
  4013   4015   int sqlite3AddInt64(i64*,i64);

Changes to src/where.c.

   399    399   
   400    400     for(i=0; i<pList->nExpr; i++){
   401    401       Expr *p = sqlite3ExprSkipCollate(pList->a[i].pExpr);
   402    402       if( p->op==TK_COLUMN
   403    403        && p->iColumn==pIdx->aiColumn[iCol]
   404    404        && p->iTable==iBase
   405    405       ){
   406         -      CollSeq *pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
   407         -      if( pColl && 0==sqlite3StrICmp(pColl->zName, zColl) ){
          406  +      CollSeq *pColl = sqlite3ExprNNCollSeq(pParse, pList->a[i].pExpr);
          407  +      if( 0==sqlite3StrICmp(pColl->zName, zColl) ){
   408    408           return i;
   409    409         }
   410    410       }
   411    411     }
   412    412   
   413    413     return -1;
   414    414   }
................................................................................
  3575   3575           ** optimization, and then only if they are actually used
  3576   3576           ** by the query plan */
  3577   3577           assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
  3578   3578           for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
  3579   3579           if( j>=pLoop->nLTerm ) continue;
  3580   3580         }
  3581   3581         if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
  3582         -        const char *z1, *z2;
  3583         -        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  3584         -        if( !pColl ) pColl = db->pDfltColl;
  3585         -        z1 = pColl->zName;
  3586         -        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
  3587         -        if( !pColl ) pColl = db->pDfltColl;
  3588         -        z2 = pColl->zName;
  3589         -        if( sqlite3StrICmp(z1, z2)!=0 ) continue;
         3582  +        if( sqlite3ExprCollSeqMatch(pWInfo->pParse, 
         3583  +                  pOrderBy->a[i].pExpr, pTerm->pExpr)==0 ){
         3584  +          continue;
         3585  +        }
  3590   3586           testcase( pTerm->pExpr->op==TK_IS );
  3591   3587         }
  3592   3588         obSat |= MASKBIT(i);
  3593   3589       }
  3594   3590   
  3595   3591       if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
  3596   3592         if( pLoop->wsFlags & WHERE_IPK ){
................................................................................
  3692   3688             }else{
  3693   3689               Expr *pIdxExpr = pIndex->aColExpr->a[j].pExpr;
  3694   3690               if( sqlite3ExprCompareSkip(pOBExpr, pIdxExpr, iCur) ){
  3695   3691                 continue;
  3696   3692               }
  3697   3693             }
  3698   3694             if( iColumn!=XN_ROWID ){
  3699         -            pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  3700         -            if( !pColl ) pColl = db->pDfltColl;
         3695  +            pColl = sqlite3ExprNNCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  3701   3696               if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
  3702   3697             }
  3703   3698             pLoop->u.btree.nIdxCol = j+1;
  3704   3699             isMatch = 1;
  3705   3700             break;
  3706   3701           }
  3707   3702           if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){

Changes to src/whereexpr.c.

   838    838   ** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
   839    839   ** This is an optimization.  No harm comes from returning 0.  But if 1 is
   840    840   ** returned when it should not be, then incorrect answers might result.
   841    841   */
   842    842   static int termIsEquivalence(Parse *pParse, Expr *pExpr){
   843    843     char aff1, aff2;
   844    844     CollSeq *pColl;
   845         -  const char *zColl1, *zColl2;
   846    845     if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
   847    846     if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
   848    847     if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
   849    848     aff1 = sqlite3ExprAffinity(pExpr->pLeft);
   850    849     aff2 = sqlite3ExprAffinity(pExpr->pRight);
   851    850     if( aff1!=aff2
   852    851      && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
   853    852     ){
   854    853       return 0;
   855    854     }
   856    855     pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
   857    856     if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
   858         -  pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
   859         -  zColl1 = pColl ? pColl->zName : 0;
   860         -  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
   861         -  zColl2 = pColl ? pColl->zName : 0;
   862         -  return sqlite3_stricmp(zColl1, zColl2)==0;
          857  +  return sqlite3ExprCollSeqMatch(pParse, pExpr->pLeft, pExpr->pRight);
   863    858   }
   864    859   
   865    860   /*
   866    861   ** Recursively walk the expressions of a SELECT statement and generate
   867    862   ** a bitmask indicating which tables are used in that expression
   868    863   ** tree.
   869    864   */