/ Check-in [85cca7cd]
Login

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

Overview
Comment:First cut at adding the COLLATE operator. Regression tests pass (or at least the quick set does) and a few new tests have been added. But many more tests are needed. Rules for combining collations need to be worked out. (CVS 3624)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 85cca7cd252d46ba71d302a89bc67c56146ec552
User & Date: drh 2007-02-01 23:02:45
Context
2007-02-02
12:33
Fix typos in the pragma documentation. Ticket #2210. (CVS 3625) check-in: f0c66ad8 user: drh tags: trunk
2007-02-01
23:02
First cut at adding the COLLATE operator. Regression tests pass (or at least the quick set does) and a few new tests have been added. But many more tests are needed. Rules for combining collations need to be worked out. (CVS 3624) check-in: 85cca7cd user: drh tags: trunk
01:53
Fix a bug in the copy method of the TCL interface. Ticket #2201. (CVS 3623) check-in: 93626396 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.272 2007/02/01 01:40:44 drh Exp $
           15  +** $Id: expr.c,v 1.273 2007/02/01 23:02:45 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
    44     44   #ifndef SQLITE_OMIT_CAST
    45     45     if( op==TK_CAST ){
    46     46       return sqlite3AffinityType(&pExpr->token);
    47     47     }
    48     48   #endif
    49     49     return pExpr->affinity;
    50     50   }
           51  +
           52  +/*
           53  +** Set the collating sequence for expression pExpr to be the collating
           54  +** sequence named by pToken.   Return a pointer to the revised expression.
           55  +*/
           56  +Expr *sqlite3ExprSetColl(Parse *pParse, Expr *pExpr, Token *pName){
           57  +  CollSeq *pColl;
           58  +  if( pExpr==0 ) return 0;
           59  +  pColl = sqlite3LocateCollSeq(pParse, (char*)pName->z, pName->n);
           60  +  if( pColl ){
           61  +    pExpr->pColl = pColl;
           62  +    pExpr->flags |= EP_ExpCollate;
           63  +  }
           64  +  return pExpr;
           65  +}
    51     66   
    52     67   /*
    53     68   ** Return the default collation sequence for the expression pExpr. If
    54     69   ** there is no default collation type, return 0.
    55     70   */
    56     71   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
    57     72     CollSeq *pColl = 0;
................................................................................
   886    901               cnt++;
   887    902               pExpr->iTable = pItem->iCursor;
   888    903               pMatch = pItem;
   889    904               pExpr->pSchema = pTab->pSchema;
   890    905               /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
   891    906               pExpr->iColumn = j==pTab->iPKey ? -1 : j;
   892    907               pExpr->affinity = pTab->aCol[j].affinity;
   893         -            pExpr->pColl = sqlite3FindCollSeq(db, ENC(db), zColl,-1, 0);
          908  +            if( (pExpr->flags & EP_ExpCollate)==0 ){
          909  +              pExpr->pColl = sqlite3FindCollSeq(db, ENC(db), zColl,-1, 0);
          910  +            }
   894    911               if( i<pSrcList->nSrc-1 ){
   895    912                 if( pItem[1].jointype & JT_NATURAL ){
   896    913                   /* If this match occurred in the left table of a natural join,
   897    914                   ** then skip the right table to avoid a duplicate match */
   898    915                   pItem++;
   899    916                   i++;
   900    917                 }else if( (pUsing = pItem[1].pUsing)!=0 ){
................................................................................
   942    959           cntTab++;
   943    960           for(iCol=0; iCol < pTab->nCol; iCol++, pCol++) {
   944    961             if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
   945    962               const char *zColl = pTab->aCol[iCol].zColl;
   946    963               cnt++;
   947    964               pExpr->iColumn = iCol==pTab->iPKey ? -1 : iCol;
   948    965               pExpr->affinity = pTab->aCol[iCol].affinity;
   949         -            pExpr->pColl = sqlite3FindCollSeq(db, ENC(db), zColl,-1, 0);
          966  +            if( (pExpr->flags & EP_ExpCollate)==0 ){
          967  +              pExpr->pColl = sqlite3FindCollSeq(db, ENC(db), zColl,-1, 0);
          968  +            }
   950    969               pExpr->pTab = pTab;
   951    970               break;
   952    971             }
   953    972           }
   954    973         }
   955    974       }
   956    975   #endif /* !defined(SQLITE_OMIT_TRIGGER) */

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.213 2007/01/27 02:38:30 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.214 2007/02/01 23:02:45 drh Exp $
    18     18   */
    19     19   
    20     20   // All token codes are small integers with #defines that begin with "TK_"
    21     21   %token_prefix TK_
    22     22   
    23     23   // The type of the data attached to each token is Token.  This is also the
    24     24   // default type for non-terminals.
................................................................................
   200    200   %right NOT.
   201    201   %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ.
   202    202   %left GT LE LT GE.
   203    203   %right ESCAPE.
   204    204   %left BITAND BITOR LSHIFT RSHIFT.
   205    205   %left PLUS MINUS.
   206    206   %left STAR SLASH REM.
   207         -%left CONCAT.
          207  +%left CONCAT COLLATE.
   208    208   %right UMINUS UPLUS BITNOT.
   209    209   
   210    210   // And "ids" is an identifer-or-string.
   211    211   //
   212    212   %type ids {Token}
   213    213   ids(A) ::= ID|STRING(X).   {A = X;}
   214    214   
................................................................................
   511    511   %type sortlist {ExprList*}
   512    512   %destructor sortlist {sqlite3ExprListDelete($$);}
   513    513   %type sortitem {Expr*}
   514    514   %destructor sortitem {sqlite3ExprDelete($$);}
   515    515   
   516    516   orderby_opt(A) ::= .                          {A = 0;}
   517    517   orderby_opt(A) ::= ORDER BY sortlist(X).      {A = X;}
   518         -sortlist(A) ::= sortlist(X) COMMA sortitem(Y) collate(C) sortorder(Z). {
   519         -  A = sqlite3ExprListAppend(X,Y,C.n>0?&C:0);
          518  +sortlist(A) ::= sortlist(X) COMMA sortitem(Y) sortorder(Z). {
          519  +  A = sqlite3ExprListAppend(X,Y,0);
   520    520     if( A ) A->a[A->nExpr-1].sortOrder = Z;
   521    521   }
   522         -sortlist(A) ::= sortitem(Y) collate(C) sortorder(Z). {
   523         -  A = sqlite3ExprListAppend(0,Y,C.n>0?&C:0);
          522  +sortlist(A) ::= sortitem(Y) sortorder(Z). {
          523  +  A = sqlite3ExprListAppend(0,Y,0);
   524    524     if( A && A->a ) A->a[0].sortOrder = Z;
   525    525   }
   526    526   sortitem(A) ::= expr(X).   {A = X;}
   527    527   
   528    528   %type sortorder {int}
   529    529   %type collate {Token}
   530    530   
................................................................................
   638    638   term(A) ::= STRING(X).       {A = sqlite3Expr(@X, 0, 0, &X);}
   639    639   expr(A) ::= REGISTER(X).     {A = sqlite3RegisterExpr(pParse, &X);}
   640    640   expr(A) ::= VARIABLE(X).     {
   641    641     Token *pToken = &X;
   642    642     Expr *pExpr = A = sqlite3Expr(TK_VARIABLE, 0, 0, pToken);
   643    643     sqlite3ExprAssignVarNumber(pParse, pExpr);
   644    644   }
          645  +expr(A) ::= expr(E) COLLATE id(C). {
          646  +  A = sqlite3ExprSetColl(pParse, E, &C);
          647  +}
   645    648   %ifndef SQLITE_OMIT_CAST
   646    649   expr(A) ::= CAST(X) LP expr(E) AS typetoken(T) RP(Y). {
   647    650     A = sqlite3Expr(TK_CAST, E, 0, &T);
   648    651     sqlite3ExprSpan(A,&X,&Y);
   649    652   }
   650    653   %endif  SQLITE_OMIT_CAST
   651    654   expr(A) ::= ID(X) LP distinct(D) exprlist(Y) RP(E). {

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.325 2007/01/26 19:23:33 drh Exp $
           15  +** $Id: select.c,v 1.326 2007/02/01 23:02:45 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Delete all the content of a Select structure but do not deallocate
    22     22   ** the select structure itself.
................................................................................
  1948   1948   
  1949   1949         aCopy = &pKeyInfo->aColl[nOrderByExpr];
  1950   1950         pSortOrder = pKeyInfo->aSortOrder = (u8*)&aCopy[nCol];
  1951   1951         memcpy(aCopy, pKeyInfo->aColl, nCol*sizeof(CollSeq*));
  1952   1952         apColl = pKeyInfo->aColl;
  1953   1953         for(i=0; i<nOrderByExpr; i++, pOTerm++, apColl++, pSortOrder++){
  1954   1954           Expr *pExpr = pOTerm->pExpr;
  1955         -        char *zName = pOTerm->zName;
  1956         -        assert( pExpr->op==TK_COLUMN && pExpr->iColumn<nCol );
  1957         -        if( zName ){
  1958         -          *apColl = sqlite3LocateCollSeq(pParse, zName, -1);
         1955  +        if( (pExpr->flags & EP_ExpCollate) ){
         1956  +          assert( pExpr->pColl!=0 );
         1957  +          *apColl = pExpr->pColl;
  1959   1958           }else{
  1960   1959             *apColl = aCopy[pExpr->iColumn];
  1961   1960           }
  1962   1961           *pSortOrder = pOTerm->sortOrder;
  1963   1962         }
  1964   1963         assert( p->pRightmost==p );
  1965   1964         assert( p->addrOpenEphm[2]>=0 );
................................................................................
  2474   2473   
  2475   2474     if( pOrderBy==0 ) return 0;
  2476   2475     for(i=0; i<pOrderBy->nExpr; i++){
  2477   2476       int iCol;
  2478   2477       Expr *pE = pOrderBy->a[i].pExpr;
  2479   2478       if( sqlite3ExprIsInteger(pE, &iCol) ){
  2480   2479         if( iCol>0 && iCol<=pEList->nExpr ){
         2480  +        CollSeq *pColl = pE->pColl;
         2481  +        int flags = pE->flags & EP_ExpCollate;
  2481   2482           sqlite3ExprDelete(pE);
  2482   2483           pE = pOrderBy->a[i].pExpr = sqlite3ExprDup(pEList->a[iCol-1].pExpr);
         2484  +        if( pColl && flags ){
         2485  +          pE->pColl = pColl;
         2486  +          pE->flags |= flags;
         2487  +        }
  2483   2488         }else{
  2484   2489           sqlite3ErrorMsg(pParse, 
  2485   2490              "%s BY column number %d out of range - should be "
  2486   2491              "between 1 and %d", zType, iCol, pEList->nExpr);
  2487   2492           return 1;
  2488   2493         }
  2489   2494       }
................................................................................
  2910   2915     if( pParent && pParentAgg &&
  2911   2916         flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
  2912   2917       if( isAgg ) *pParentAgg = 1;
  2913   2918       goto select_end;
  2914   2919     }
  2915   2920   #endif
  2916   2921   
  2917         -  /* If there is an ORDER BY clause, resolve any collation sequences
  2918         -  ** names that have been explicitly specified and create a sorting index.
  2919         -  **
  2920         -  ** This sorting index might end up being unused if the data can be 
         2922  +  /* If there is an ORDER BY clause, then this sorting
         2923  +  ** index might end up being unused if the data can be 
  2921   2924     ** extracted in pre-sorted order.  If that is the case, then the
  2922   2925     ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  2923   2926     ** we figure out that the sorting index is not needed.  The addrSortIndex
  2924   2927     ** variable is used to facilitate that change.
  2925   2928     */
  2926   2929     if( pOrderBy ){
  2927         -    struct ExprList_item *pTerm;
  2928   2930       KeyInfo *pKeyInfo;
  2929         -    for(i=0, pTerm=pOrderBy->a; i<pOrderBy->nExpr; i++, pTerm++){
  2930         -      if( pTerm->zName ){
  2931         -        pTerm->pExpr->pColl = sqlite3LocateCollSeq(pParse, pTerm->zName, -1);
  2932         -      }
  2933         -    }
  2934   2931       if( pParse->nErr ){
  2935   2932         goto select_end;
  2936   2933       }
  2937   2934       pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
  2938   2935       pOrderBy->iECursor = pParse->nTab++;
  2939   2936       p->addrOpenEphm[2] = addrSortIndex =
  2940   2937         sqlite3VdbeOp3(v, OP_OpenEphemeral, pOrderBy->iECursor, pOrderBy->nExpr+2,                     (char*)pKeyInfo, P3_KEYINFO_HANDOFF);

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.534 2007/02/01 01:40:44 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.535 2007/02/01 23:02:45 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Extra interface definitions for those who need them
    21     21   */
................................................................................
  1017   1017   #define EP_Agg          0x02  /* Contains one or more aggregate functions */
  1018   1018   #define EP_Resolved     0x04  /* IDs have been resolved to COLUMNs */
  1019   1019   #define EP_Error        0x08  /* Expression contains one or more errors */
  1020   1020   #define EP_Distinct     0x10  /* Aggregate function with DISTINCT keyword */
  1021   1021   #define EP_VarSelect    0x20  /* pSelect is correlated, not constant */
  1022   1022   #define EP_Dequoted     0x40  /* True if the string has been dequoted */
  1023   1023   #define EP_InfixFunc    0x80  /* True for an infix function: LIKE, GLOB, etc */
         1024  +#define EP_ExpCollate  0x100  /* Collating sequence specified explicitly */
  1024   1025   
  1025   1026   /*
  1026   1027   ** These macros can be used to test, set, or clear bits in the 
  1027   1028   ** Expr.flags field.
  1028   1029   */
  1029   1030   #define ExprHasProperty(E,P)     (((E)->flags&(P))==(P))
  1030   1031   #define ExprHasAnyProperty(E,P)  (((E)->flags&(P))!=0)
................................................................................
  1773   1774   int sqlite3TwoPartName(Parse *, Token *, Token *, Token **);
  1774   1775   const char *sqlite3ErrStr(int);
  1775   1776   int sqlite3ReadUniChar(const char *zStr, int *pOffset, u8 *pEnc, int fold);
  1776   1777   int sqlite3ReadSchema(Parse *pParse);
  1777   1778   CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char *,int,int);
  1778   1779   CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char *zName, int nName);
  1779   1780   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
         1781  +Expr *sqlite3ExprSetColl(Parse *pParse, Expr *, Token *);
  1780   1782   int sqlite3CheckCollSeq(Parse *, CollSeq *);
  1781   1783   int sqlite3CheckIndexCollSeq(Parse *, Index *);
  1782   1784   int sqlite3CheckObjectName(Parse *, const char *);
  1783   1785   void sqlite3VdbeSetChanges(sqlite3 *, int);
  1784   1786   void sqlite3utf16Substr(sqlite3_context *,int,sqlite3_value **);
  1785   1787   
  1786   1788   const void *sqlite3ValueText(sqlite3_value*, u8);

Changes to test/collate1.test.

     8      8   #    May you find forgiveness for yourself and forgive others.
     9      9   #    May you share freely, never taking more than you give.
    10     10   #
    11     11   #***********************************************************************
    12     12   # This file implements regression tests for SQLite library.  The
    13     13   # focus of this script is page cache subsystem.
    14     14   #
    15         -# $Id: collate1.test,v 1.4 2005/11/01 15:48:25 drh Exp $
           15  +# $Id: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   #
    21     21   # Tests are roughly organised as follows:
    22     22   #
................................................................................
    86     86   } {0x119 0x2D {}}
    87     87   do_test collate1-1.4 {
    88     88     execsql {
    89     89      SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;
    90     90     }
    91     91   } {{} 0x2D 0x119}
    92     92   do_test collate1-1.5 {
           93  +  execsql {
           94  +    SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1
           95  +  }
           96  +} {{} 0x2D 0x119}
           97  +do_test collate1-1.6 {
           98  +  execsql {
           99  +    SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC
          100  +  }
          101  +} {{} 0x2D 0x119}
          102  +do_test collate1-1.7 {
          103  +  execsql {
          104  +    SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC
          105  +  }
          106  +} {0x119 0x2D {}}
          107  +do_test collate1-1.99 {
    93    108     execsql {
    94    109       DROP TABLE collate1t1;
    95    110     }
    96    111   } {}
    97    112   
    98    113   do_test collate1-2.0 {
    99    114     execsql {
................................................................................
   129    144   } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
   130    145   do_test collate1-2.6 {
   131    146     execsql {
   132    147       SELECT c1, c2 FROM collate1t1 
   133    148           ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
   134    149     }
   135    150   } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
   136         -do_test collate1-2.7 {
          151  +do_test collate1-2.12.1 {
          152  +  execsql {
          153  +    SELECT c1 COLLATE numeric, c2 FROM collate1t1 
          154  +     ORDER BY 1, 2 COLLATE hex;
          155  +  }
          156  +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
          157  +do_test collate1-2.12.2 {
          158  +  execsql {
          159  +    SELECT c1 COLLATE hex, c2 FROM collate1t1 
          160  +     ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
          161  +  }
          162  +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
          163  +do_test collate1-2.12.3 {
          164  +  execsql {
          165  +    SELECT c1, c2 COLLATE hex FROM collate1t1 
          166  +     ORDER BY 1 COLLATE numeric, 2;
          167  +  }
          168  +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
          169  +do_test collate1-2.12.4 {
          170  +  execsql {
          171  +    SELECT c1 COLLATE numeric, c2 COLLATE hex
          172  +      FROM collate1t1 
          173  +     ORDER BY 1, 2;
          174  +  }
          175  +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
          176  +do_test collate1-2.13 {
          177  +  execsql {
          178  +    SELECT c1 COLLATE binary, c2 COLLATE hex
          179  +      FROM collate1t1
          180  +     ORDER BY 1, 2;
          181  +  }
          182  +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
          183  +do_test collate1-2.14 {
          184  +  execsql {
          185  +    SELECT c1, c2
          186  +      FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
          187  +  }
          188  +} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
          189  +do_test collate1-2.15 {
          190  +  execsql {
          191  +    SELECT c1 COLLATE binary, c2 COLLATE hex
          192  +      FROM collate1t1 
          193  +     ORDER BY 1 DESC, 2 DESC;
          194  +  }
          195  +} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
          196  +do_test collate1-2.16 {
          197  +  execsql {
          198  +    SELECT c1 COLLATE hex, c2 COLLATE binary
          199  +      FROM collate1t1 
          200  +     ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
          201  +  }
          202  +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
          203  +do_test collate1-2.99 {
   137    204     execsql {
   138    205       DROP TABLE collate1t1;
   139    206     }
   140    207   } {}
   141    208   
   142    209   #
   143    210   # These tests ensure that the default collation type for a column is used 
................................................................................
   175    242       SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;
   176    243     }
   177    244   } {{} {} 1 1 0x5 5 0x45 69}
   178    245   do_test collate1-3.5 {
   179    246     execsql {
   180    247       SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;
   181    248     }
          249  +} {{} {} 0x45 69 0x5 5 1 1}
          250  +do_test collate1-3.5.1 {
          251  +  execsql {
          252  +    SELECT a COLLATE binary as c1, b as c2
          253  +      FROM collate1t1 ORDER BY c1;
          254  +  }
   182    255   } {{} {} 0x45 69 0x5 5 1 1}
   183    256   do_test collate1-3.6 {
   184    257     execsql {
   185    258       DROP TABLE collate1t1;
   186    259     }
   187    260   } {}
   188    261   
................................................................................
   216    289     }
   217    290   } {{} 1 12 101}
   218    291   do_test collate1-4.4 {
   219    292     execsql {
   220    293       SELECT c1||'' FROM collate1t1 ORDER BY 1;
   221    294     }
   222    295   } {{} 1 101 12}
          296  +do_test collate1-4.4.1 {
          297  +  execsql {
          298  +    SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;
          299  +  }
          300  +} {{} 1 12 101}
   223    301   do_test collate1-4.5 {
   224    302     execsql {
   225    303       DROP TABLE collate1t1;
   226    304     }
   227    305   } {}
   228    306   
   229    307   finish_test

Changes to test/collate2.test.

     8      8   #    May you find forgiveness for yourself and forgive others.
     9      9   #    May you share freely, never taking more than you give.
    10     10   #
    11     11   #***********************************************************************
    12     12   # This file implements regression tests for SQLite library.  The
    13     13   # focus of this script is page cache subsystem.
    14     14   #
    15         -# $Id: collate2.test,v 1.4 2005/01/21 03:12:16 danielk1977 Exp $
           15  +# $Id: collate2.test,v 1.5 2007/02/01 23:02:46 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   #
    21     21   # Tests are organised as follows:
    22     22   #
................................................................................
    93     93       }
    94     94     }
    95     95   } {}
    96     96   do_test collate2-1.1 {
    97     97     execsql {
    98     98       SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;
    99     99     }
          100  +} {ab bA bB ba bb}
          101  +do_test collate2-1.1.1 {
          102  +  execsql {
          103  +    SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1;
          104  +  }
          105  +} {ab bA bB ba bb}
          106  +do_test collate2-1.1.2 {
          107  +  execsql {
          108  +    SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1;
          109  +  }
          110  +} {ab bA bB ba bb}
          111  +do_test collate2-1.1.3 {
          112  +  execsql {
          113  +    SELECT a FROM collate2t1 WHERE c COLLATE binary > 'aa' ORDER BY 1;
          114  +  }
   100    115   } {ab bA bB ba bb}
   101    116   do_test collate2-1.2 {
   102    117     execsql {
   103    118       SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;
          119  +  }
          120  +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
          121  +do_test collate2-1.2.1 {
          122  +  execsql {
          123  +    SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa'
          124  +     ORDER BY 1, oid;
          125  +  }
          126  +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
          127  +do_test collate2-1.2.2 {
          128  +  execsql {
          129  +    SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa'
          130  +     ORDER BY 1, oid;
          131  +  }
          132  +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
          133  +do_test collate2-1.2.3 {
          134  +  execsql {
          135  +    SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa'
          136  +     ORDER BY 1, oid;
   104    137     }
   105    138   } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
   106    139   do_test collate2-1.3 {
   107    140     execsql {
   108    141       SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;
          142  +  }
          143  +} {ba Ab Bb ab bb}
          144  +do_test collate2-1.3.1 {
          145  +  execsql {
          146  +    SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa'
          147  +    ORDER BY 1;
          148  +  }
          149  +} {ba Ab Bb ab bb}
          150  +do_test collate2-1.3.2 {
          151  +  execsql {
          152  +    SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa'
          153  +    ORDER BY 1;
          154  +  }
          155  +} {ba Ab Bb ab bb}
          156  +do_test collate2-1.3.3 {
          157  +  execsql {
          158  +    SELECT c FROM collate2t1 WHERE c COLLATE backwards > 'aa'
          159  +    ORDER BY 1;
   109    160     }
   110    161   } {ba Ab Bb ab bb}
   111    162   do_test collate2-1.4 {
   112    163     execsql {
   113    164       SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;
   114    165     }
   115    166   } {AA AB Aa Ab BA BB Ba Bb aA aB}

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the lang-*.html files.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.120 2007/01/29 17:58:28 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.121 2007/02/01 23:02:46 drh Exp $}
     5      5   source common.tcl
     6      6   
     7      7   if {[llength $argv]>0} {
     8      8     set outputdir [lindex $argv 0]
     9      9   } else {
    10     10     set outputdir ""
    11     11   }
................................................................................
  1003   1003   <expr> NOTNULL |
  1004   1004   <expr> [NOT] BETWEEN <expr> AND <expr> |
  1005   1005   <expr> [NOT] IN ( <value-list> ) |
  1006   1006   <expr> [NOT] IN ( <select-statement> ) |
  1007   1007   <expr> [NOT] IN [<database-name> .] <table-name> |
  1008   1008   [EXISTS] ( <select-statement> ) |
  1009   1009   CASE [<expr>] LP WHEN <expr> THEN <expr> RPPLUS [ELSE <expr>] END |
  1010         -CAST ( <expr> AS <type> )
         1010  +CAST ( <expr> AS <type> ) |
         1011  +<expr> COLLATE <collation-name>
  1011   1012   } {like-op} {
  1012   1013   LIKE | GLOB | REGEXP | MATCH
  1013   1014   }
  1014   1015   
  1015   1016   puts {
  1016   1017   <p>This section is different from the others.  Most other sections of
  1017   1018   this document talks about a particular SQL command.  This section does
................................................................................
  1028   1029   &lt;&lt;   &gt;&gt;   &amp;    |
  1029   1030   &lt;    &lt;=   &gt;    &gt;=
  1030   1031   =    ==   !=   &lt;&gt;   </big>IN
  1031   1032   AND   
  1032   1033   OR</font>
  1033   1034   </pre></blockquote>
  1034   1035   
  1035         -<p>Supported unary operators are these:</p>
         1036  +<p>Supported unary prefix operators are these:</p>
  1036   1037   
  1037   1038   <blockquote><pre>
  1038   1039   <font color="#2c2cf0"><big>-    +    !    ~    NOT</big></font>
  1039   1040   </pre></blockquote>
         1041  +
         1042  +<p>The COLLATE operator can be thought of as a unary postfix
         1043  +operator.  The COLLATE operator has the highest precedence.
         1044  +It always binds more tightly than any prefix unary operator or
         1045  +any binary operator.</p>
  1040   1046   
  1041   1047   <p>The unary operator [Operator +] is a no-op.  It can be applied
  1042   1048   to strings, numbers, or blobs and it always gives as its result the
  1043   1049   value of the operand.</p>
  1044   1050   
  1045   1051   <p>Note that there are two variations of the equals and not equals
  1046   1052   operators.  Equals can be either}