Fix various collation sequence issues. (CVS 1568)

FossilOrigin-Name: 66835ee67051027456a536e33b2f88a741654525

15 files changed, 707 insertions, 340 deletions

diff --git a/manifest b/manifest
index 05f20c2b6..78fb1c9a1 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Comment\schanges\sin\spager.c.\s(CVS\s1567)
-D 2004-06-10T23:35:50
+C Fix\svarious\scollation\ssequence\sissues.\s(CVS\s1568)
+D 2004-06-11T10:51:27
 F Makefile.in ab7b0d5118e2da97bac66be8684a1034e3500f5a
 F Makefile.linux-gcc a9e5a0d309fa7c38e7c14d3ecf7690879d3a5457
 F README f1de682fbbd94899d50aca13d387d1b3fd3be2dd
@@ -32,13 +32,13 @@ F src/build.c b36b62f49aea7d258cb804999dcc8650e4d79464
 F src/date.c 8e6fa3173386fb29fdef012ee08a853c1e9908b2
 F src/delete.c 911221aadb35d610c84fadb32e71c52990827e58
 F src/encode.c a876af473d1d636faa3dca51c7571f2e007eea37
-F src/expr.c 34e63e960ab8ca9e4fc4a1f41b0a3b77df2ae167
-F src/func.c ffbdfa4cad2a16a41390c2ce923ef8b0f173d777
+F src/expr.c 97f3dd76c778a191834ea75d0cddeb82bba70f8b
+F src/func.c 91a21c9598b38b8122f745e6ac5cda4ce9f2cfa9
 F src/hash.c 440c2f8cb373ee1b4e13a0988489c7cd95d55b6f
 F src/hash.h 762d95f1e567664d1eafc1687de755626be962fb
 F src/insert.c 68c7f3ddd6a7f1e5596d6996da1a2861b3789a3a
 F src/legacy.c ad23746f15f67e34577621b1875f639c94839e1f
-F src/main.c 335b4cd48af0011017e33a411aea307553114e67
+F src/main.c 9f20ae4870fc0c0c6c7815316b9f77b4a5ad4e8f
 F src/md5.c 4302e84ae516c616bb079c4e6d038c0addb33481
 F src/os.h 23c69c5084e71b5fe199ff1c4e35a4aded0f1380
 F src/os_common.h 6393ac67a3a7b4aea19ff17529980ecf77eb2348
@@ -54,10 +54,10 @@ F src/parse.y 097438674976355a10cf177bd97326c548820b86
 F src/pragma.c 0bc3adea28df802074996bec067d506d55d28f84
 F src/printf.c 63b15f1ea9fe3daa066bb7430fd20d4a2d717dc8
 F src/random.c eff68e3f257e05e81eae6c4d50a51eb88beb4ff3
-F src/select.c 6cb407796dde0e8f27450ead68856eb9f8188789
+F src/select.c 3559dcd25b3e86150fb991b866b00b5152f15cac
 F src/shell.c ca519519dcbbc582f6d88f7d0e7583b857fd3469
 F src/sqlite.h.in 2b6afe1de6935d3dfbd6042f46a62f1b7c3b3992
-F src/sqliteInt.h e8e641bec4d7806023ce8192a64234d3599c5fc0
+F src/sqliteInt.h 625faf4c9ce2f99b9c85a2bca5c4e73736c30262
 F src/table.c af14284fa36c8d41f6829e3f2819dce07d3e2de2
 F src/tclsqlite.c e974c0b2479ed37334aeb268de331e0a1b21b5a8
 F src/test1.c 5f5c0773df1091cc02ddf6608a8f6e0c65940a56
@@ -71,9 +71,9 @@ F src/update.c 168b6d523087ca4545b74ec9f3102b1f3c6b1e38
 F src/utf.c c2c8e445bfea724f3502609d6389fe66651f02ab
 F src/util.c e8629f04d920ae968fced709dc7a3a2c62b65ac4
 F src/vacuum.c b921eb778842592e1fb48a9d4cef7e861103878f
-F src/vdbe.c 90e0e6bdbdf9b77c66f2500374b5784d30c323fa
+F src/vdbe.c 688ae431918ee4aefe53d395d7c43bb1aa32e458
 F src/vdbe.h 46f74444a213129bc4b5ce40124dd8ed613b0cde
-F src/vdbeInt.h d41605853332bdbd600d7ecd60e1f54bbaea174e
+F src/vdbeInt.h e27e29ffe5b8b3998032e394631944dacafe5c54
 F src/vdbeapi.c bcf5821ed09070d586898374b905861c4dd73d0b
 F src/vdbeaux.c 73764dadcdbf79aa2d948f863eae07b18589e663
 F src/vdbemem.c b1599f5d24131107a21a54e618e372e1252de958
@@ -95,10 +95,12 @@ F test/btree6.test a5ede6bfbbb2ec8b27e62813612c0f28e8f3e027
 F test/btree7.test 429b96cfef5b51a7d512cfb4b5b3e453384af293
 F test/capi2.test 8fb64e8ab7f78b8254cd4d04bb96822167f731b2
 F test/capi3.test b6fe8a66d2ffe28d4faaaec154a143131e8ff631
-F test/collate1.test 7f1ad4c24ea949b7a7aee387df7839389990a998
-F test/collate2.test 5b92d795048794266ac27f242a411da8ffeaae25
-F test/collate3.test 69ae73af2e32f4180397bdf2e98998d67a0d4a5d
-F test/collate4.test a8f2d58bd6943ed1746639c11b12896ccfe8f646
+F test/collate1.test 2ee4fa3a47a652ccf56c5ddf65dcc44d9bad82ef
+F test/collate2.test c1a3b41f761b28853c5696037f92de928f93233b
+F test/collate3.test 47cf6b393cadbad845f34ddfc04692bb3930986a
+F test/collate4.test 0e9fc08ffcf6eddf72e354a15de06688fa86db31
+F test/collate5.test 1dd5f0f508c46667f9d4606c7950c414b0bdc0d5
+F test/collate6.test 2a45768914f04c1447a69d1358bbede376552675
 F test/conflict.test 45ce1e44ea748944aed233df8c278a9e1c4c87cc
 F test/crashtest1.c 09c1c7d728ccf4feb9e481671e29dda5669bbcc2
 F test/date.test aed5030482ebc02bd8d386c6c86a29f694ab068d
@@ -220,7 +222,7 @@ F www/support.tcl 1801397edd271cc39a2aadd54e701184b5181248
 F www/tclsqlite.tcl 19191cf2a1010eaeff74c51d83fd5f5a4d899075
 F www/vdbe.tcl 59288db1ac5c0616296b26dce071c36cb611dfe9
 F www/whentouse.tcl a8335bce47cc2fddb07f19052cb0cb4d9129a8e4
-P 86744c9aca8f27c994a2bf37c4f9fd3c90b1266f
-R 65f18105c3c7cd6b45070d399a5db0dc
-U drh
-Z 8a5b524afd149095f5eb569662ebddff
+P 0e420f72cd5885e32914b4d958bad811fdd9fb77
+R 51b6a0eb20d4bdd91945614e74564613
+U danielk1977
+Z a2401ce0227bab41442da327e235dd95
diff --git a/manifest.uuid b/manifest.uuid
index 5af7d0098..392feed37 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-0e420f72cd5885e32914b4d958bad811fdd9fb77
\ No newline at end of file
+66835ee67051027456a536e33b2f88a741654525
\ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 762b696c4..7b4768f9a 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
 ** This file contains routines used for analyzing expressions and
 ** for generating VDBE code that evaluates expressions in SQLite.
 **
-** $Id: expr.c,v 1.138 2004/06/10 10:50:17 danielk1977 Exp $
+** $Id: expr.c,v 1.139 2004/06/11 10:51:27 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -386,6 +386,7 @@ Select *sqlite3SelectDup(Select *p){
   pNew->zSelect = 0;
   pNew->iLimit = -1;
   pNew->iOffset = -1;
+  pNew->ppOpenTemp = 0;
   return pNew;
 }
 
@@ -1280,6 +1281,7 @@ void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
       int p2 = 0;
       int i;
       int iPrefEnc = (pParse->db->enc==TEXT_Utf8)?0:1;
+      CollSeq *pColl = 0;
       getFunctionName(pExpr, &zId, &nId);
       pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, iPrefEnc, 0);
       assert( pDef!=0 );
@@ -1288,6 +1290,13 @@ void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
         if( sqlite3ExprIsConstant(pList->a[i].pExpr) ){
           p2 |= (1<<i);
         }
+        if( pDef->needCollSeq && !pColl ){
+          pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
+        }
+      }
+      if( pDef->needCollSeq ){
+        if( !pColl ) pColl = pParse->db->pDfltColl; 
+        sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, pColl, P3_COLLSEQ);
       }
       sqlite3VdbeOp3(v, OP_Function, nExpr, p2, (char*)pDef, P3_FUNCDEF);
       break;
diff --git a/src/func.c b/src/func.c
index cbeaf4e65..0563351c2 100644
--- a/src/func.c
+++ b/src/func.c
@@ -16,7 +16,7 @@
 ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
 ** All other code has file scope.
 **
-** $Id: func.c,v 1.65 2004/06/08 00:39:01 danielk1977 Exp $
+** $Id: func.c,v 1.66 2004/06/11 10:51:32 danielk1977 Exp $
 */
 #include <ctype.h>
 #include <math.h>
@@ -26,6 +26,10 @@
 #include "vdbeInt.h"
 #include "os.h"
 
+static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
+  return context->pColl;
+}
+
 /*
 ** Implementation of the non-aggregate min() and max() functions
 */
@@ -37,15 +41,18 @@ static void minmaxFunc(
   int i;
   int mask;    /* 0 for min() or 0xffffffff for max() */
   int iBest;
+  CollSeq *pColl;
 
   if( argc==0 ) return;
   mask = (int)sqlite3_user_data(context);
+  pColl = sqlite3GetFuncCollSeq(context);
+  assert( pColl );
   assert( mask==-1 || mask==0 );
   iBest = 0;
   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
   for(i=1; i<argc; i++){
     if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
-    if( (sqlite3MemCompare(argv[iBest], argv[i], 0)^mask)>=0 ){
+    if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
       iBest = i;
     }
   }
@@ -564,7 +571,8 @@ static void nullifFunc(
   int argc,
   sqlite3_value **argv
 ){
-  if( sqlite3MemCompare(argv[0], argv[1], 0)!=0 ){
+  CollSeq *pColl = sqlite3GetFuncCollSeq(context);
+  if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
     sqlite3_result_value(context, argv[0]);
   }
 }
@@ -857,6 +865,7 @@ static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv)
   if( SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
 
   if( pBest->flags ){
+    CollSeq *pColl = sqlite3GetFuncCollSeq(context);
     /* This step function is used for both the min() and max() aggregates,
     ** the only difference between the two being that the sense of the
     ** comparison is inverted. For the max() aggregate, the
@@ -866,7 +875,7 @@ static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv)
     ** aggregate, or 0 for min().
     */
     max = ((sqlite3_user_data(context)==(void *)-1)?1:0);
-    cmp = sqlite3MemCompare(pBest, pArg, 0);
+    cmp = sqlite3MemCompare(pBest, pArg, pColl);
     if( (max && cmp<0) || (!max && cmp>0) ){
       sqlite3VdbeMemCopy(pBest, pArg);
     }
@@ -893,54 +902,56 @@ void sqlite3RegisterBuiltinFunctions(sqlite *db){
      signed char nArg;
      u8 argType;               /* 0: none.  1: db  2: (-1) */
      u8 eTextRep;              /* 1: UTF-16.  0: UTF-8 */
+     u8 needCollSeq;
      void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
   } aFuncs[] = {
-    { "min",                        -1, 0, 0, minmaxFunc },
-    { "min",                         0, 0, 0, 0          },
-    { "max",                        -1, 2, 0, minmaxFunc },
-    { "max",                         0, 2, 0, 0          },
-    { "typeof",                      1, 0, 0, typeofFunc },
-    { "length",                      1, 0, 0, lengthFunc },
-    { "substr",                      3, 0, 0, substrFunc },
-    { "abs",                         1, 0, 0, absFunc    },
-    { "round",                       1, 0, 0, roundFunc  },
-    { "round",                       2, 0, 0, roundFunc  },
-    { "upper",                       1, 0, 0, upperFunc  },
-    { "lower",                       1, 0, 0, lowerFunc  },
-    { "coalesce",                   -1, 0, 0, ifnullFunc },
-    { "coalesce",                    0, 0, 0, 0          },
-    { "coalesce",                    1, 0, 0, 0          },
-    { "ifnull",                      2, 0, 0, ifnullFunc },
-    { "random",                     -1, 0, 0, randomFunc },
-    { "like",                        2, 0, 0, likeFunc   }, /* UTF-8 */
-    { "like",                        2, 2, 1, likeFunc   }, /* UTF-16 */
-    { "glob",                        2, 0, 0, globFunc   },
-    { "nullif",                      2, 0, 0, nullifFunc },
-    { "sqlite_version",              0, 0, 0, versionFunc},
-    { "quote",                       1, 0, 0, quoteFunc  },
-    { "last_insert_rowid",           0, 1, 0, last_insert_rowid },
-    { "change_count",                0, 1, 0, change_count      },
-    { "last_statement_change_count", 0, 1, 0, last_statement_change_count },
+    { "min",                        -1, 0, 0, 1, minmaxFunc },
+    { "min",                         0, 0, 0, 1, 0          },
+    { "max",                        -1, 2, 0, 1, minmaxFunc },
+    { "max",                         0, 2, 0, 1, 0          },
+    { "typeof",                      1, 0, 0, 0, typeofFunc },
+    { "length",                      1, 0, 0, 0, lengthFunc },
+    { "substr",                      3, 0, 0, 0, substrFunc },
+    { "abs",                         1, 0, 0, 0, absFunc    },
+    { "round",                       1, 0, 0, 0, roundFunc  },
+    { "round",                       2, 0, 0, 0, roundFunc  },
+    { "upper",                       1, 0, 0, 0, upperFunc  },
+    { "lower",                       1, 0, 0, 0, lowerFunc  },
+    { "coalesce",                   -1, 0, 0, 0, ifnullFunc },
+    { "coalesce",                    0, 0, 0, 0, 0          },
+    { "coalesce",                    1, 0, 0, 0, 0          },
+    { "ifnull",                      2, 0, 0, 1, ifnullFunc },
+    { "random",                     -1, 0, 0, 0, randomFunc },
+    { "like",                        2, 0, 0, 0, likeFunc   }, /* UTF-8 */
+    { "like",                        2, 2, 1, 0, likeFunc   }, /* UTF-16 */
+    { "glob",                        2, 0, 0, 0, globFunc   },
+    { "nullif",                      2, 0, 0, 0, nullifFunc },
+    { "sqlite_version",              0, 0, 0, 0, versionFunc},
+    { "quote",                       1, 0, 0, 0, quoteFunc  },
+    { "last_insert_rowid",           0, 1, 0, 0, last_insert_rowid },
+    { "change_count",                0, 1, 0, 0, change_count      },
+    { "last_statement_change_count", 0, 1, 0, 0, last_statement_change_count },
 #ifdef SQLITE_SOUNDEX
-    { "soundex",                     1, 0, 0, soundexFunc},
+    { "soundex",                     1, 0, 0, 0, soundexFunc},
 #endif
 #ifdef SQLITE_TEST
-    { "randstr",                     2, 0, 0, randStr    },
+    { "randstr",                     2, 0, 0, 0, randStr    },
 #endif
   };
   static struct {
     char *zName;
     signed char nArg;
     u8 argType;
+    u8 needCollSeq;
     void (*xStep)(sqlite3_context*,int,sqlite3_value**);
     void (*xFinalize)(sqlite3_context*);
   } aAggs[] = {
-    { "min",    1, 0, minmaxStep,   minMaxFinalize },
-    { "max",    1, 2, minmaxStep,   minMaxFinalize },
-    { "sum",    1, 0, sumStep,      sumFinalize    },
-    { "avg",    1, 0, sumStep,      avgFinalize    },
-    { "count",  0, 0, countStep,    countFinalize  },
-    { "count",  1, 0, countStep,    countFinalize  },
+    { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
+    { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
+    { "sum",    1, 0, 0, sumStep,      sumFinalize    },
+    { "avg",    1, 0, 0, sumStep,      avgFinalize    },
+    { "count",  0, 0, 0, countStep,    countFinalize  },
+    { "count",  1, 0, 0, countStep,    countFinalize  },
 #if 0
     { "stddev", 1, 0, stdDevStep,   stdDevFinalize },
 #endif
@@ -955,6 +966,13 @@ void sqlite3RegisterBuiltinFunctions(sqlite *db){
     }
     sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
         aFuncs[i].eTextRep, 0, pArg, aFuncs[i].xFunc, 0, 0);
+    if( aFuncs[i].needCollSeq ){
+      FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName, 
+          strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);
+      if( pFunc && aFuncs[i].needCollSeq ){
+        pFunc->needCollSeq = 1;
+      }
+    }
   }
   for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
     void *pArg = 0;
@@ -964,6 +982,14 @@ void sqlite3RegisterBuiltinFunctions(sqlite *db){
     }
     sqlite3_create_function(db, aAggs[i].zName, aAggs[i].nArg, 0, 0, pArg,
         0, aAggs[i].xStep, aAggs[i].xFinalize);
+    if( aAggs[i].needCollSeq ){
+      FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName,
+          strlen(aAggs[i].zName), aAggs[i].nArg, 0, 0);
+      if( pFunc && aAggs[i].needCollSeq ){
+        pFunc->needCollSeq = 1;
+      }
+    }
   }
   sqlite3RegisterDateTimeFunctions(db);
 }
+
diff --git a/src/main.c b/src/main.c
index ac8831a09..d3967b9a1 100644
--- a/src/main.c
+++ b/src/main.c
@@ -14,7 +14,7 @@
 ** other files are for internal use by SQLite and should not be
 ** accessed by users of the library.
 **
-** $Id: main.c,v 1.214 2004/06/10 10:50:22 danielk1977 Exp $
+** $Id: main.c,v 1.215 2004/06/11 10:51:32 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 #include "os.h"
@@ -420,8 +420,13 @@ static int binaryCollatingFunc(
 }
 
 /*
-** Another built-in collating sequence: NOCASE. At the moment there is
-** only a UTF-8 implementation.
+** Another built-in collating sequence: NOCASE. 
+**
+** This collating sequence is intended to be used for "case independant
+** comparison". SQLite's knowledge of upper and lower case equivalents
+** extends only to the 26 characters used in the English language.
+**
+** At the moment there is only a UTF-8 implementation.
 */
 static int nocaseCollatingFunc(
   void *NotUsed,
diff --git a/src/select.c b/src/select.c
index 011493e5e..f64853cf5 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.186 2004/06/10 10:50:25 danielk1977 Exp $
+** $Id: select.c,v 1.187 2004/06/11 10:51:35 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 
@@ -1229,29 +1229,67 @@ static void computeLimitRegisters(Parse *pParse, Select *p){
 ** KeyInfo structure.  The number of columns in the KeyInfo is determined
 ** by the result set of the SELECT statement in the second argument.
 **
+** Specifically, this routine is called to open an index table for
+** DISTINCT, UNION, INTERSECT and EXCEPT select statements (but not 
+** UNION ALL).
+**
 ** Make the new table a KeyAsData table if keyAsData is true.
+**
+** The value returned is the address of the OP_OpenTemp instruction.
 */
-static void openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
+static int openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
   KeyInfo *pKeyInfo;
   int nColumn;
   sqlite *db = pParse->db;
   int i;
   Vdbe *v = pParse->pVdbe;
+  int addr;
 
   if( fillInColumnList(pParse, p) ){
-    return;
+    return 0;
   }
   nColumn = p->pEList->nExpr;
   pKeyInfo = sqliteMalloc( sizeof(*pKeyInfo)+nColumn*sizeof(CollSeq*) );
-  if( pKeyInfo==0 ) return;
+  if( pKeyInfo==0 ) return 0;
+  pKeyInfo->enc = pParse->db->enc;
   pKeyInfo->nField = nColumn;
   for(i=0; i<nColumn; i++){
-    pKeyInfo->aColl[i] = db->pDfltColl;
+    pKeyInfo->aColl[i] = sqlite3ExprCollSeq(pParse, p->pEList->a[i].pExpr);
+    if( !pKeyInfo->aColl[i] ){
+      pKeyInfo->aColl[i] = db->pDfltColl;
+    }
   }
-  sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
+  addr = sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, 
+      (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
   if( keyAsData ){
     sqlite3VdbeAddOp(v, OP_KeyAsData, iTab, 1);
   }
+  return addr;
+}
+
+static int multiSelectOpenTempAddr(Select *p, int addr, IdList **ppOpenTemp){
+  if( !p->ppOpenTemp ){
+    *ppOpenTemp = sqlite3IdListAppend(0, 0);
+    p->ppOpenTemp = ppOpenTemp;
+  }else{
+    *p->ppOpenTemp = sqlite3IdListAppend(*p->ppOpenTemp, 0);
+  }
+  if( !(*p->ppOpenTemp) ){
+    return SQLITE_NOMEM;
+  }
+  (*p->ppOpenTemp)->a[(*p->ppOpenTemp)->nId-1].idx = addr;
+  return SQLITE_OK;
+}
+
+static CollSeq *multiSelectCollSeq(Parse *pParse, Select *p, int iCol){
+  CollSeq *pRet = 0;
+  if( p->pPrior ){
+    pRet = multiSelectCollSeq(pParse, p->pPrior, iCol);
+  }
+  if( !pRet ){
+    pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
+  }
+  return pRet;
 }
 
 /*
@@ -1294,25 +1332,7 @@ static int multiSelect(
   int rc = SQLITE_OK;  /* Success code from a subroutine */
   Select *pPrior;     /* Another SELECT immediately to our left */
   Vdbe *v;            /* Generate code to this VDBE */
-#if 0 /* NOT USED */
-  char *affStr = 0;
-
-  if( !aff ){
-    int len;
-    rc = fillInColumnList(pParse, p);
-    if( rc!=SQLITE_OK ){
-      goto multi_select_end;
-    }
-    len = p->pEList->nExpr+1;
-    affStr = (char *)sqliteMalloc(p->pEList->nExpr+1);
-    if( !affStr ){
-      rc = SQLITE_NOMEM;
-      goto multi_select_end;
-    }
-    memset(affStr, (int)SQLITE_AFF_NUMERIC, len-1);
-    aff = affStr;
-  }
-#endif
+  IdList *pOpenTemp = 0;
 
   /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
   ** the last SELECT in the series may have an ORDER BY or LIMIT.
@@ -1359,6 +1379,7 @@ static int multiSelect(
       if( p->pOrderBy==0 ){
         pPrior->nLimit = p->nLimit;
         pPrior->nOffset = p->nOffset;
+        pPrior->ppOpenTemp = p->ppOpenTemp;
         rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
         if( rc ){
           goto multi_select_end;
@@ -1384,6 +1405,7 @@ static int multiSelect(
       int priorOp;     /* The SRT_ operation to apply to prior selects */
       int nLimit, nOffset; /* Saved values of p->nLimit and p->nOffset */
       ExprList *pOrderBy;  /* The ORDER BY clause for the right SELECT */
+      int addr;
 
       priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
       if( eDest==priorOp && p->pOrderBy==0 && p->nLimit<0 && p->nOffset==0 ){
@@ -1401,16 +1423,20 @@ static int multiSelect(
           rc = 1;
           goto multi_select_end;
         }
+        addr = sqlite3VdbeAddOp(v, OP_OpenTemp, unionTab, 0);
         if( p->op!=TK_ALL ){
-          openTempIndex(pParse, p, unionTab, 1);
-        }else{
-          sqlite3VdbeAddOp(v, OP_OpenTemp, unionTab, 0);
+          rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
+          if( rc!=SQLITE_OK ){
+            goto multi_select_end;
+          }
+          sqlite3VdbeAddOp(v, OP_KeyAsData, unionTab, 1);
         }
         assert( p->pEList );
       }
 
       /* Code the SELECT statements to our left
       */
+      pPrior->ppOpenTemp = p->ppOpenTemp;
       rc = sqlite3Select(pParse, pPrior, priorOp, unionTab, 0, 0, 0, aff);
       if( rc ){
         goto multi_select_end;
@@ -1468,9 +1494,6 @@ static int multiSelect(
         sqlite3VdbeAddOp(v, OP_Next, unionTab, iStart);
         sqlite3VdbeResolveLabel(v, iBreak);
         sqlite3VdbeAddOp(v, OP_Close, unionTab, 0);
-        if( p->pOrderBy ){
-          generateSortTail(pParse, p, v, p->pEList->nExpr, eDest, iParm);
-        }
       }
       break;
     }
@@ -1478,6 +1501,7 @@ static int multiSelect(
       int tab1, tab2;
       int iCont, iBreak, iStart;
       int nLimit, nOffset;
+      int addr;
 
       /* INTERSECT is different from the others since it requires
       ** two temporary tables.  Hence it has its own case.  Begin
@@ -1489,11 +1513,18 @@ static int multiSelect(
         rc = 1;
         goto multi_select_end;
       }
-      openTempIndex(pParse, p, tab1, 1);
+
+      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab1, 0);
+      rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
+      if( rc!=SQLITE_OK ){
+        goto multi_select_end;
+      }
+      sqlite3VdbeAddOp(v, OP_KeyAsData, tab1, 1);
       assert( p->pEList );
 
       /* Code the SELECTs to our left into temporary table "tab1".
       */
+      pPrior->ppOpenTemp = p->ppOpenTemp;
       rc = sqlite3Select(pParse, pPrior, SRT_Union, tab1, 0, 0, 0, aff);
       if( rc ){
         goto multi_select_end;
@@ -1501,7 +1532,12 @@ static int multiSelect(
 
       /* Code the current SELECT into temporary table "tab2"
       */
-      openTempIndex(pParse, p, tab2, 1);
+      addr = sqlite3VdbeAddOp(v, OP_OpenTemp, tab2, 0);
+      rc = multiSelectOpenTempAddr(p, addr, &pOpenTemp);
+      if( rc!=SQLITE_OK ){
+        goto multi_select_end;
+      }
+      sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1);
       p->pPrior = 0;
       nLimit = p->nLimit;
       p->nLimit = -1;
@@ -1540,9 +1576,6 @@ static int multiSelect(
       sqlite3VdbeResolveLabel(v, iBreak);
       sqlite3VdbeAddOp(v, OP_Close, tab2, 0);
       sqlite3VdbeAddOp(v, OP_Close, tab1, 0);
-      if( p->pOrderBy ){
-        generateSortTail(pParse, p, v, p->pEList->nExpr, eDest, iParm);
-      }
       break;
     }
   }
@@ -1554,17 +1587,57 @@ static int multiSelect(
     goto multi_select_end;
   }
 
-multi_select_end:
-#if 0  /*** NOT USED ****/
-  if( affStr ){
-    if( rc!=SQLITE_OK ){
-      sqliteFree(affStr);
-    }else{
-      multiSelectAffinity(p, affStr);
-      sqlite3VdbeOp3(v, OP_Noop, 0, 0, affStr, P3_DYNAMIC);
+  if( p->pOrderBy || (pOpenTemp && pOpenTemp->nId>0) ){
+    int nCol = p->pEList->nExpr;
+    int i;
+    KeyInfo *pKeyInfo = sqliteMalloc(sizeof(*pKeyInfo)+nCol*sizeof(CollSeq*));
+    if( !pKeyInfo ){
+      rc = SQLITE_NOMEM;
+      goto multi_select_end;
+    }
+
+    pKeyInfo->enc = pParse->db->enc;
+    pKeyInfo->nField = nCol;
+
+    for(i=0; i<nCol; i++){
+      pKeyInfo->aColl[i] = multiSelectCollSeq(pParse, p, i);
+      if( !pKeyInfo->aColl[i] ){
+        pKeyInfo->aColl[i] = pParse->db->pDfltColl;
+      }
+    }
+
+    for(i=0; pOpenTemp && i<pOpenTemp->nId; i++){
+      int p3type = (i==0?P3_KEYINFO_HANDOFF:P3_KEYINFO);
+      int addr = pOpenTemp->a[i].idx;
+      sqlite3VdbeChangeP3(v, addr, (char *)pKeyInfo, p3type);
+    }
+
+    if( p->pOrderBy ){
+      for(i=0; i<p->pOrderBy->nExpr; i++){
+        Expr *pExpr = p->pOrderBy->a[i].pExpr;
+        char *zName = p->pOrderBy->a[i].zName;
+        assert( pExpr->op==TK_COLUMN && pExpr->iColumn<nCol );
+        assert( !pExpr->pColl );
+        if( zName ){
+          pExpr->pColl = sqlite3LocateCollSeq(pParse, zName, -1);
+        }else{
+          pExpr->pColl = pKeyInfo->aColl[pExpr->iColumn];
+        }
+      }
+      generateSortTail(pParse, p, v, p->pEList->nExpr, eDest, iParm);
+    }
+
+    if( !pOpenTemp ){
+      /* This happens for UNION ALL ... ORDER BY */
+      sqliteFree(pKeyInfo);
     }
   }
-#endif
+
+multi_select_end:
+  if( pOpenTemp ){
+    sqlite3IdListDelete(pOpenTemp);
+  }
+  p->ppOpenTemp = 0;
   return rc;
 }
 
@@ -1949,9 +2022,10 @@ static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){
   if( iCol<0 ){
     pIdx = 0;
   }else{
+    CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
       assert( pIdx->nColumn>=1 );
-      if( pIdx->aiColumn[0]==iCol ) break;
+      if( pIdx->aiColumn[0]==iCol && pIdx->keyInfo.aColl[0]==pColl ) break;
     }
     if( pIdx==0 ) return 0;
   }
@@ -2447,6 +2521,15 @@ int sqlite3Select(
       assert( pE->op==TK_AGG_FUNCTION );
       nExpr = sqlite3ExprCodeExprList(pParse, pE->pList);
       sqlite3VdbeAddOp(v, OP_Integer, i, 0);
+      if( pDef->needCollSeq ){
+        CollSeq *pColl = 0;
+        int j;
+        for(j=0; !pColl && j<nExpr; j++){
+          pColl = sqlite3ExprCollSeq(pParse, pE->pList->a[j].pExpr);
+        }
+        if( !pColl ) pColl = pParse->db->pDfltColl;
+        sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
+      }
       sqlite3VdbeOp3(v, OP_AggFunc, 0, nExpr, (char*)pDef, P3_POINTER);
     }
   }
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 27fd9bdf2..45a4fc283 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.281 2004/06/10 14:01:08 danielk1977 Exp $
+** @(#) $Id: sqliteInt.h,v 1.282 2004/06/11 10:51:35 danielk1977 Exp $
 */
 #include "config.h"
 #include "sqlite3.h"
@@ -475,6 +475,7 @@ struct FuncDef {
   void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
   void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
   void (*xFinalize)(sqlite3_context*);                /* Aggregate finializer */
+  u8 needCollSeq;      /* True if sqlite3GetFuncCollSeq() might be called */
 };
 
 /*
@@ -953,6 +954,7 @@ struct Select {
   int nLimit, nOffset;   /* LIMIT and OFFSET values.  -1 means not used */
   int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
   char *zSelect;         /* Complete text of the SELECT command */
+  IdList **ppOpenTemp;   /* OP_OpenTemp addresses used by multi-selects */
 };
 
 /*
diff --git a/src/vdbe.c b/src/vdbe.c
index 59add81c8..97e088a61 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -43,7 +43,7 @@
 ** in this file for details.  If in doubt, do not deviate from existing
 ** commenting and indentation practices when changing or adding code.
 **
-** $Id: vdbe.c,v 1.363 2004/06/09 21:01:11 drh Exp $
+** $Id: vdbe.c,v 1.364 2004/06/11 10:51:37 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 #include "os.h"
@@ -1218,6 +1218,22 @@ divide_by_zero:
   break;
 }
 
+/* Opcode: CollSeq * * P3
+**
+** P3 is a pointer to a CollSeq struct. If the next call to a user function
+** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will
+** be returned. This is used by the built-in min(), max() and nullif()
+** built-in functions.
+**
+** The interface used by the implementation of the aforementioned functions
+** to retrieve the collation sequence set by this opcode is not available
+** publicly, only to user functions defined in func.c.
+*/
+case OP_CollSeq: {
+  assert( pOp->p3type==P3_COLLSEQ );
+  break;
+}
+
 /* Opcode: Function P1 P2 P3
 **
 ** Invoke a user function (P3 is a pointer to a Function structure that
@@ -1263,6 +1279,12 @@ case OP_Function: {
   ctx.s.z = 0;
   ctx.isError = 0;
   ctx.isStep = 0;
+  if( ctx.pFunc->needCollSeq ){
+    assert( pOp>p->aOp );
+    assert( pOp[-1].p3type==P3_COLLSEQ );
+    assert( pOp[-1].opcode==OP_CollSeq );
+    ctx.pColl = (CollSeq *)pOp[-1].p3;
+  }
   if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse;
   (*ctx.pFunc->xFunc)(&ctx, n, apVal);
   if( sqlite3SafetyOn(db) ) goto abort_due_to_misuse;
@@ -4302,6 +4324,13 @@ case OP_AggFunc: {
   ctx.cnt = ++pMem->i;
   ctx.isError = 0;
   ctx.isStep = 1;
+  ctx.pColl = 0;
+  if( ctx.pFunc->needCollSeq ){
+    assert( pOp>p->aOp );
+    assert( pOp[-1].p3type==P3_COLLSEQ );
+    assert( pOp[-1].opcode==OP_CollSeq );
+    ctx.pColl = (CollSeq *)pOp[-1].p3;
+  }
   (ctx.pFunc->xStep)(&ctx, n, apVal);
   pMem->z = ctx.pAgg;
   pMem->flags = MEM_AggCtx;
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index 5cc05d47c..c466e8e2d 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -208,6 +208,7 @@ struct sqlite3_context {
   u8 isError;       /* Set to true for an error */
   u8 isStep;        /* Current in the step function */
   int cnt;          /* Number of times that the step function has been called */
+  CollSeq *pColl;
 };
 
 /*
diff --git a/test/collate1.test b/test/collate1.test
index d5782c4c0..be58de183 100644
--- a/test/collate1.test
+++ b/test/collate1.test
@@ -1,15 +1,18 @@
 #
-# The author or author's hereby grant to the public domain a non-exclusive,
-# fully paid-up, perpetual, license in the software and all related
-# intellectual property to make, have made, use, have used, reproduce,
-# prepare derivative works, distribute, perform and display the work.  
+# 2001 September 15
 #
-#*************************************************************************
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
 # This file implements regression tests for SQLite library.  The
-# focus of this file is testing the ORDER BY clause with 
-# user-defined collation sequences.
+# focus of this script is page cache subsystem.
 #
-# $Id: collate1.test,v 1.1 2004/06/09 09:55:20 danielk1977 Exp $
+# $Id: collate1.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
diff --git a/test/collate2.test b/test/collate2.test
index 4d74eb69a..b3e871de6 100644
--- a/test/collate2.test
+++ b/test/collate2.test
@@ -1,14 +1,18 @@
 #
-# The author or author's hereby grant to the public domain a non-exclusive,
-# fully paid-up, perpetual, license in the software and all related
-# intellectual property to make, have made, use, have used, reproduce,
-# prepare derivative works, distribute, perform and display the work.  
+# 2001 September 15
 #
-#*************************************************************************
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
 # This file implements regression tests for SQLite library.  The
-# focus of this file is testing comparison operators in expressions
-# that use user-defined collation sequences.
+# focus of this script is page cache subsystem.
 #
+# $Id: collate2.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
diff --git a/test/collate3.test b/test/collate3.test
index 673169c0c..70dc7f47f 100644
--- a/test/collate3.test
+++ b/test/collate3.test
@@ -1,15 +1,17 @@
+# 2001 September 15
 #
-# The author or author's hereby grant to the public domain a non-exclusive,
-# fully paid-up, perpetual, license in the software and all related
-# intellectual property to make, have made, use, have used, reproduce,
-# prepare derivative works, distribute, perform and display the work.  
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
 #
-#*************************************************************************
-# This file implements regression tests for SQLite library. The
-# focus of this file is testing that when the user tries to use an 
-# unknown or undefined collation type SQLite handles this correctly.
-# Also some other error cases are tested.
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
 #
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script is page cache subsystem.
+#
+# $Id: collate3.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -107,10 +109,6 @@ do_test collate3-2.6 {
     SELECT * FROM collate3t1;
   }
 } {0 {}}
-
-# FIX ME
-if 0 {
-
 do_test collate3-2.7 {
   catchsql {
     SELECT * FROM collate3t1 GROUP BY c1;
@@ -121,6 +119,7 @@ do_test collate3-2.8 {
     SELECT DISTINCT c1 FROM collate3t1;
   }
 } {1 {no such collation sequence: string_compare}} 
+
 do_test collate3-2.9 {
   catchsql {
     SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
@@ -167,9 +166,6 @@ do_test collate3-2.17 {
   }
 } {1 {no such collation sequence: string_compare}} 
 
-}
-
-
 #
 # Create an index that uses a collation sequence then close and
 # re-open the database without re-registering the collation
diff --git a/test/collate4.test b/test/collate4.test
index da5723b7c..794870f3d 100644
--- a/test/collate4.test
+++ b/test/collate4.test
@@ -1,14 +1,18 @@
 #
-# The author or author's hereby grant to the public domain a non-exclusive,
-# fully paid-up, perpetual, license in the software and all related
-# intellectual property to make, have made, use, have used, reproduce,
-# prepare derivative works, distribute, perform and display the work.  
+# 2001 September 15
 #
-#*************************************************************************
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
 # This file implements regression tests for SQLite library.  The
-# focus of this file is testing indices that use user-defined collation 
-# sequences.
+# focus of this script is page cache subsystem.
 #
+# $Id: collate4.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -544,6 +548,13 @@ do_test collate4-3.15 {
   }
 } {}
 
+# Mimic the SQLite 2 collation type NUMERIC.
+db collate numeric numeric_collate
+proc numeric_collate {lhs rhs} {
+  if {$lhs == $rhs} {return 0} 
+  return [expr ($lhs>$rhs)?1:-1]
+}
+
 #
 # These tests - collate4-4.* check that min() and max() only ever 
 # use indices constructed with built-in collation type numeric.
@@ -551,17 +562,13 @@ do_test collate4-3.15 {
 # CHANGED:  min() and max() now use the collation type. If there
 # is an indice that can be used, it is used.
 #
-
-# FIX ME: min() and max() are currently broken.
-if 0 {
-
 do_test collate4-4.0 {
   execsql {
     CREATE TABLE collate4t1(a COLLATE TEXT);
-    INSERT INTO collate4t1 VALUES(2);
-    INSERT INTO collate4t1 VALUES(10);
-    INSERT INTO collate4t1 VALUES(20);
-    INSERT INTO collate4t1 VALUES(104);
+    INSERT INTO collate4t1 VALUES('2');
+    INSERT INTO collate4t1 VALUES('10');
+    INSERT INTO collate4t1 VALUES('20');
+    INSERT INTO collate4t1 VALUES('104');
   }
 } {}
 do_test collate4-4.1 {
@@ -613,218 +620,48 @@ do_test collate4-4.7 {
 #
 do_test collate4-4.8 {
   execsql {
-    CREATE TABLE collate4t1(a NUMERIC, b TEXT, 
-                            c COLLATE TEXT, d COLLATE NUMERIC);
-    INSERT INTO collate4t1 VALUES(11, 101, 1001, 10001);
-    INSERT INTO collate4t1 VALUES(20002, 2002, 202, 22);
+    CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
+    INSERT INTO collate4t1 VALUES('11', '101');
+    INSERT INTO collate4t1 VALUES('101', '11')
   }
 } {}
 do_test collate4-4.9 {
   execsql {
-    SELECT max(a, b, c) FROM collate4t1;
+    SELECT max(a, b) FROM collate4t1;
   }
-} {11 202}
+} {11 11}
 do_test collate4-4.10 {
   execsql {
-    SELECT max(c, b, a) FROM collate4t1;
+    SELECT max(b, a) FROM collate4t1;
   }
-} {11 202}
+} {101 101}
 do_test collate4-4.11 {
   execsql {
-    SELECT max(a, b) FROM collate4t1;
+    SELECT max(a, '101') FROM collate4t1;
   }
-} {101 20002}
+} {11 101}
 do_test collate4-4.12 {
   execsql {
-    SELECT max(b, a) FROM collate4t1;
+    SELECT max('101', a) FROM collate4t1;
   }
-} {101 20002}
+} {11 101}
 do_test collate4-4.13 {
   execsql {
-    SELECT max(b, a) FROM collate4t1;
+    SELECT max(b, '101') FROM collate4t1;
   }
-} {101 20002}
+} {101 101}
 do_test collate4-4.14 {
   execsql {
-    SELECT max(b, '11') FROM collate4t1;
-  }
-} {11 2002}
-do_test collate4-4.15 {
-  execsql {
-    SELECT max('11', b) FROM collate4t1;
-  }
-} {11 2002}
-do_test collate4-4.16 {
-  execsql {
-    SELECT max(11, b) FROM collate4t1;
-  }
-} {101 2002}
-do_test collate4-4.17 {
-  execsql {
-    SELECT max(b, 11) FROM collate4t1;
-  }
-} {101 2002}
-do_test collate4-4.18 {
-  execsql {
-    SELECT max(c, d) FROM collate4t1;
-  }
-} {1001 22}
-do_test collate4-4.19 {
-  execsql {
-    SELECT max(d, c) FROM collate4t1;
-  }
-} {10001 202}
-do_test collate4-4.20 {
-  execsql {
-    DROP TABLE collate4t1;
-  }
-} {}
-
-}
-
-#
-# These tests - collate4-5.* - test the REINDEX command.
-#
-# FIX ME: Find out if version 3 needs REINDEX.
-if 0 {
-
-proc install_normal_collate {} {
-  db collate collate1 "string compare"
-}
-proc inverse_collate {l r} {
-  expr -1 * [string compare $l $r]
-}
-proc install_inverse_collate {} {
-  db collate collate1 inverse_collate 
-}
-install_normal_collate
-
-do_test collate4-5.0 {
-  execsql {
-    CREATE TABLE collate4t1(a COLLATE collate1);
-    INSERT INTO collate4t1 VALUES('A');
-    INSERT INTO collate4t1 VALUES(NULL);
-    INSERT INTO collate4t1 VALUES('B');
-    CREATE INDEX collate4i1 ON collate4t1(a);
-  }
-} {}
-do_test collate4-5.1 {
-  cksort {
-    SELECT * FROM collate4t1 ORDER BY 1;
-  }
-} {{} A B nosort}
-do_test collate4-5.2 {
-  install_inverse_collate
-  cksort {
-    SELECT * FROM collate4t1 ORDER BY 1;
-  }
-} {{} A B nosort}      ;# This is incorrect - because we need to REINDEX
-do_test collate4-5.3 {
-  install_inverse_collate
-  cksort {
-    REINDEX collate4t1;
-    SELECT * FROM collate4t1 ORDER BY 1;
-  }
-} {{} B A nosort}
-do_test collate4-5.4 {
-  install_normal_collate
-  cksort {
-    REINDEX;
-    SELECT * FROM collate4t1 ORDER BY 1;
-  }
-} {{} A B nosort}
-do_test collate4-5.5 {
-  install_inverse_collate
-  cksort {
-    REINDEX main.collate4t1;
-    SELECT * FROM collate4t1 ORDER BY 1;
-  }
-} {{} B A nosort}
-do_test collate4-5.6 {
-  catchsql {
-    REINDEX garbage;
-  }
-} {1 {no such table: garbage}}
-do_test collate4-5.7 {
-  execsql {
-    DROP TABLE collate4t1;
-    CREATE TEMP TABLE collate4t1(a COLLATE collate1, b COLLATE collate1);
-    CREATE INDEX collatei1 ON collate4t1(a);
-    CREATE INDEX collatei2 ON collate4t1(b);
-    INSERT INTO collate4t1 VALUES(1, 1);
-    INSERT INTO collate4t1 VALUES(NULL, NULL);
-    INSERT INTO collate4t1 VALUES(2, 2);
-  }
-} {}
-do_test collate4-5.8 {
-  cksort {
-    SELECT * FROM collate4t1 ORDER BY 1
-  }
-} {{} {} 2 2 1 1 nosort}
-do_test collate4-5.9 {
-  install_normal_collate
-  cksort {
-    REINDEX;
-    SELECT * FROM collate4t1 order by 2;
-  }
-} {{} {} 1 1 2 2 nosort}
-do_test collate4-5.10 {
-  install_inverse_collate
-  cksort {
-    REINDEX collate4t1;
-    SELECT * FROM collate4t1 order by 1;
-  }
-} {{} {} 2 2 1 1 nosort}
-do_test collate4-5.11 {
-  install_normal_collate
-  cksort {
-    REINDEX temp.collate4t1;
-    SELECT * FROM collate4t1 order by 2;
-  }
-} {{} {} 1 1 2 2 nosort}
-
-# This checks that if a REINDEX operation produces a conflict an error
-# is raised and the checkpoint rolled back.
-do_test collate4-5.12 {
-  execsql {
-    BEGIN;
-    CREATE UNIQUE INDEX collate4i3 ON collate4t1(a);
-    INSERT INTO collate4t1 VALUES(3, 3);
-  }
-  db collate collate1 "expr 0 ;"
-  catchsql {
-    REINDEX;
-  }
-} {1 {indexed columns are not unique}}
-do_test collate4-5.13 {
-  execsql {
-    COMMIT;
-    SELECT * FROM collate4t1;
+    SELECT max('101', b) FROM collate4t1;
   }
-} {1 1 {} {} 2 2 3 3}
+} {101 101}
 
-# Do an EXPLAIN REINDEX, just in case it leaks memory or something.
-do_test collate4-5.14 {
-  execsql {
-    EXPLAIN REINDEX;
-  }
-  expr 0
-} {0}
-do_test collate4-5.15 {
-  execsql {
-    EXPLAIN REINDEX collate4t1;
-  }
-  expr 0
-} {0}
-
-do_test collate4-5.16 {
+do_test collate4-4.15 {
   execsql {
     DROP TABLE collate4t1;
   }
 } {}
 
-}
-
 #
 # These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY 
 # indices do not confuse collation sequences. 
diff --git a/test/collate5.test b/test/collate5.test
new file mode 100644
index 000000000..8c8a65407
--- /dev/null
+++ b/test/collate5.test
@@ -0,0 +1,264 @@
+#
+# 2001 September 15
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT
+# SELECT statements that use user-defined collation sequences. Also
+# GROUP BY clauses that use user-defined collation sequences.
+#
+# $Id: collate5.test,v 1.1 2004/06/11 10:51:41 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+
+#
+# Tests are organised as follows:
+# collate5-1.* - DISTINCT
+# collate5-2.* - Compound SELECT
+# collate5-3.* - ORDER BY on compound SELECT
+# collate5-4.* - GROUP BY
+
+# Create the collation sequence 'TEXT', purely for asthetic reasons. The
+# test cases in this script could just as easily use BINARY.
+db collate TEXT [list string compare]
+
+# Mimic the SQLite 2 collation type NUMERIC.
+db collate numeric numeric_collate
+proc numeric_collate {lhs rhs} {
+  if {$lhs == $rhs} {return 0} 
+  return [expr ($lhs>$rhs)?1:-1]
+}
+
+#
+# These tests - collate5-1.* - focus on the DISTINCT keyword.
+#
+do_test collate5-1.0 {
+  execsql {
+    CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text);
+
+    INSERT INTO collate5t1 VALUES('a', 'apple');
+    INSERT INTO collate5t1 VALUES('A', 'Apple');
+    INSERT INTO collate5t1 VALUES('b', 'banana');
+    INSERT INTO collate5t1 VALUES('B', 'banana');
+    INSERT INTO collate5t1 VALUES('n', NULL);
+    INSERT INTO collate5t1 VALUES('N', NULL);
+  } 
+} {}
+do_test collate5-1.1 {
+  execsql {
+    SELECT DISTINCT a FROM collate5t1;
+  }
+} {a b n}
+do_test collate5-1.2 {
+  execsql {
+    SELECT DISTINCT b FROM collate5t1;
+  }
+} {apple Apple banana {}}
+do_test collate5-1.3 {
+  execsql {
+    SELECT DISTINCT a, b FROM collate5t1;
+  }
+} {a apple A Apple b banana n {}}
+
+
+#
+# Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT
+# queries that use user-defined collation sequences.
+#
+# collate5-2.1.* - UNION
+# collate5-2.2.* - INTERSECT
+# collate5-2.3.* - EXCEPT
+#
+do_test collate5-2.0 {
+  execsql {
+    CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase);
+
+    INSERT INTO collate5t2 VALUES('a', 'apple');
+    INSERT INTO collate5t2 VALUES('A', 'apple');
+    INSERT INTO collate5t2 VALUES('b', 'banana');
+    INSERT INTO collate5t2 VALUES('B', 'Banana');
+  } 
+} {}
+
+do_test collate5-2.1.1 {
+  execsql {
+    SELECT a FROM collate5t1 UNION select a FROM collate5t2;
+  }
+} {A B N}
+do_test collate5-2.1.2 {
+  execsql {
+    SELECT a FROM collate5t2 UNION select a FROM collate5t1;
+  }
+} {A B N a b n}
+do_test collate5-2.1.3 {
+  execsql {
+    SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2;
+  }
+} {A Apple A apple B Banana b banana N {}}
+do_test collate5-2.1.4 {
+  execsql {
+    SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1;
+  }
+} {A Apple B banana N {} a apple b banana n {}}
+
+do_test collate5-2.2.1 {
+  execsql {
+    SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;
+  }
+} {N}
+do_test collate5-2.2.2 {
+  execsql {
+    SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';
+  }
+} {A a}
+do_test collate5-2.2.3 {
+  execsql {
+    SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;
+  }
+} {A Apple N {}}
+do_test collate5-2.2.4 {
+  execsql {
+    SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1 
+      where a != 'a';
+  }
+} {A apple a apple}
+
+do_test collate5-2.3.1 {
+  execsql {
+    SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;
+  }
+} {A B}
+do_test collate5-2.3.2 {
+  execsql {
+    SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';
+  }
+} {B b}
+do_test collate5-2.3.3 {
+  execsql {
+    SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2;
+  }
+} {a apple B banana}
+do_test collate5-2.3.4 {
+  execsql {
+    SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1;
+  }
+} {A apple B Banana a apple b banana}
+
+#
+# This test ensures performs a UNION operation with a bunch of different
+# length records. The goal is to test that the logic that compares records
+# for the compound SELECT operators works with record lengths that lie
+# either side of the troublesome 256 and 65536 byte marks.
+#
+set ::lens [list \
+  0 1 2 3 4 5 6 7 8 9 \
+  240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \
+  257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \
+  65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \
+  65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \
+  65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ]
+do_test collate5-2.4.0 {
+  execsql {
+    BEGIN;
+    CREATE TABLE collate5t3(a, b);
+  }
+  foreach ii $::lens { 
+    execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');"
+  }
+  execsql {
+    COMMIT;
+    SELECT count(*) FROM 
+        (SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3);
+  }
+} [llength $::lens]
+do_test collate5-2.4.1 {
+  execsql {DROP TABLE collate5t3;}
+} {}
+unset ::lens
+
+#
+# These tests - collate5-3.* - focus on compound SELECT queries that 
+# feature ORDER BY clauses.
+#
+do_test collate5-3.0 {
+  execsql {
+    SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1;
+  }
+} {A a A a B b B b N n}
+do_test collate5-3.1 {
+  execsql {
+    SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1;
+  }
+} {A A B B N a a b b n}
+do_test collate5-3.2 {
+  execsql {
+    SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 
+      ORDER BY 1 COLLATE TEXT;
+  }
+} {A A B B N a a b b n}
+
+do_test collate5-3.3 {
+  execsql {
+    CREATE TABLE collate5t_cn(a COLLATE NUMERIC);
+    CREATE TABLE collate5t_ct(a COLLATE TEXT);
+    INSERT INTO collate5t_cn VALUES('1');
+    INSERT INTO collate5t_cn VALUES('11');
+    INSERT INTO collate5t_cn VALUES('101');
+    INSERT INTO collate5t_ct SELECT * FROM collate5t_cn;
+  }
+} {}
+do_test collate5-3.4 {
+  execsql {
+    SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1;
+  }
+} {1 11 101}
+do_test collate5-3.5 {
+  execsql {
+    SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1;
+  }
+} {1 101 11}
+
+do_test collate5-3.20 {
+  execsql {
+    DROP TABLE collate5t_cn;
+    DROP TABLE collate5t_ct;
+    DROP TABLE collate5t1;
+    DROP TABLE collate5t2;
+  }
+} {}
+
+do_test collate5-4.0 {
+  execsql {
+    CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC); 
+    INSERT INTO collate5t1 VALUES('a', '1');
+    INSERT INTO collate5t1 VALUES('A', '1.0');
+    INSERT INTO collate5t1 VALUES('b', '2');
+    INSERT INTO collate5t1 VALUES('B', '3');
+  }
+} {}
+do_test collate5-4.1 {
+  execsql {
+    SELECT a, count(*) FROM collate5t1 GROUP BY a;
+  }
+} {a 2 b 2}
+do_test collate5-4.2 {
+  execsql {
+    SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b;
+  }
+} {a 1 2 b 2 1 B 3 1}
+do_test collate5-4.3 {
+  execsql {
+    DROP TABLE collate5t1;
+  }
+} {}
+
+finish_test
diff --git a/test/collate6.test b/test/collate6.test
new file mode 100644
index 000000000..c371e088b
--- /dev/null
+++ b/test/collate6.test
@@ -0,0 +1,106 @@
+#
+# 2001 September 15
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script is collation sequences in concert with triggers.
+#
+# $Id: collate6.test,v 1.1 2004/06/11 10:51:41 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Create a case-insensitive collation type NOCASE for use in testing. 
+# Normally, capital letters are less than their lower-case counterparts.
+db collate NOCASE nocase_collate
+proc nocase_collate {a b} {
+  return [string compare -nocase $a $b]
+}
+
+#
+# Tests are organized as follows:
+# collate6-1.* - triggers.
+#
+
+do_test collate6-1.0 {
+  execsql {
+    CREATE TABLE collate6log(a, b);
+    CREATE TABLE collate6tab(a COLLATE NOCASE, b COLLATE BINARY);
+  }
+} {}
+
+# Test that the default collation sequence applies to new.* references 
+# in WHEN clauses.
+do_test collate6-1.1 {
+  execsql {
+    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab 
+      WHEN new.a = 'a' BEGIN
+        INSERT INTO collate6log VALUES(new.a, new.b);
+    END;
+  }
+} {}
+do_test collate6-1.2 {
+  execsql {
+    INSERT INTO collate6tab VALUES('a', 'b');
+    SELECT * FROM collate6log;
+  }
+} {a b}
+do_test collate6-1.3 {
+  execsql {
+    INSERT INTO collate6tab VALUES('A', 'B');
+    SELECT * FROM collate6log;
+  }
+} {a b A B}
+do_test collate6-1.4 {
+  execsql {
+    DROP TRIGGER collate6trig;
+    DELETE FROM collate6log;
+  } 
+} {}
+
+# Test that the default collation sequence applies to new.* references 
+# in the body of triggers.
+do_test collate6-1.5 {
+  execsql {
+    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab BEGIN
+      INSERT INTO collate6log VALUES(new.a='a', new.b='b');
+    END;
+  }
+} {}
+do_test collate6-1.6 {
+  execsql {
+    INSERT INTO collate6tab VALUES('a', 'b');
+    SELECT * FROM collate6log;
+  }
+} {1 1}
+do_test collate6-1.7 {
+  execsql {
+    INSERT INTO collate6tab VALUES('A', 'B');
+    SELECT * FROM collate6log;
+  }
+} {1 1 1 0}
+do_test collate6-1.8 {
+  execsql {
+    DROP TRIGGER collate6trig;
+    DELETE FROM collate6log;
+  } 
+} {}
+
+do_test collate6-1.9 {
+  execsql {
+    DROP TABLE collate6tab;
+  }
+} {}
+
+
+finish_test
+
+
+