SQLite

  rc = sqlite3Fts3InitTerm(db);
  if( rc!=SQLITE_OK ) return rc;
#endif

  rc = sqlite3Fts3InitAux(db);
  if( rc!=SQLITE_OK ) return rc;




  sqlite3Fts3SimpleTokenizerModule(&pSimple);
  sqlite3Fts3PorterTokenizerModule(&pPorter);

  /* Allocate and initialize the hash-table used to store tokenizers. */
  pHash = sqlite3_malloc(sizeof(Fts3Hash));
  if( !pHash ){
    rc = SQLITE_NOMEM;

        db, "fts3", &fts3Module, (void *)pHash, hashDestroy
    );
    if( rc==SQLITE_OK ){
      rc = sqlite3_create_module_v2(
          db, "fts4", &fts3Module, (void *)pHash, 0
      );
    }
    if( rc==SQLITE_OK ){
      rc = sqlite3Fts3InitTok(db, (void *)pHash);
    }
    return rc;
  }


  /* An error has occurred. Delete the hash table and return the error code. */
  assert( rc!=SQLITE_OK );
  if( pHash ){
    sqlite3Fts3HashClear(pHash);
    sqlite3_free(pHash);
  }

int sqlite3Fts3MsrIncrNext(
    Fts3Table *, Fts3MultiSegReader *, sqlite3_int64 *, char **, int *);
int sqlite3Fts3EvalPhrasePoslist(Fts3Cursor *, Fts3Expr *, int iCol, char **); 
int sqlite3Fts3MsrOvfl(Fts3Cursor *, Fts3MultiSegReader *, int *);
int sqlite3Fts3MsrIncrRestart(Fts3MultiSegReader *pCsr);

/* fts3_tokenize_vtab.c */
int sqlite3Fts3InitTok(sqlite3*, Fts3Hash *);

/* fts3_unicode2.c (functions generated by parsing unicode text files) */
#ifdef SQLITE_ENABLE_FTS4_UNICODE61
int sqlite3FtsUnicodeFold(int, int);
int sqlite3FtsUnicodeIsalnum(int);
int sqlite3FtsUnicodeIsdiacritic(int);
#endif

        p = 0;
        for(i=0; i<nMaxDepth; i++){
          if( apLeaf[i] ){
            if( p==0 ){
              p = apLeaf[i];
              p->pParent = 0;
            }else{
              assert( pFree!=0 );
              pFree->pRight = p;
              pFree->pLeft = apLeaf[i];
              pFree->pLeft->pParent = pFree;
              pFree->pRight->pParent = pFree;

              p = pFree;
              pFree = pFree->pParent;

  int iPos;                       /* Current 'pos' value */
};

/*
** Query FTS for the tokenizer implementation named zName.
*/
static int fts3tokQueryTokenizer(
  Fts3Hash *pHash,
  const char *zName,
  const sqlite3_tokenizer_module **pp,
  char **pzErr
){

  sqlite3_tokenizer_module *p;
  int nName = (int)strlen(zName);

  p = (sqlite3_tokenizer_module *)sqlite3Fts3HashFind(pHash, zName, nName+1);
  if( !p ){
    *pzErr = sqlite3_mprintf("unknown tokenizer: %s", zName);

    return SQLITE_ERROR;
  }







  *pp = p;
  return SQLITE_OK;
}

/*
** The second argument, argv[], is an array of pointers to nul-terminated
** strings. This function makes a copy of the array and strings into a 
** single block of memory. It then dequotes any of the strings that appear
** to be quoted.

**   argv[0]: module name
**   argv[1]: database name 
**   argv[2]: table name
**   argv[3]: first argument (tokenizer name)
*/
static int fts3tokConnectMethod(
  sqlite3 *db,                    /* Database connection */
  void *pHash,                    /* Hash table of tokenizers */
  int argc,                       /* Number of elements in argv array */
  const char * const *argv,       /* xCreate/xConnect argument array */
  sqlite3_vtab **ppVtab,          /* OUT: New sqlite3_vtab object */
  char **pzErr                    /* OUT: sqlite3_malloc'd error message */
){
  Fts3tokTable *pTab;
  const sqlite3_tokenizer_module *pMod = 0;
  sqlite3_tokenizer *pTok = 0;
  int rc;
  char **azDequote = 0;
  int nDequote;


  rc = sqlite3_declare_vtab(db, FTS3_TOK_SCHEMA);
  if( rc!=SQLITE_OK ) return rc;

  nDequote = argc-3;
  rc = fts3tokDequoteArray(nDequote, &argv[3], &azDequote);

  if( rc==SQLITE_OK ){
    const char *zModule;
    if( nDequote<1 ){
      zModule = "simple";
    }else{
      zModule = azDequote[0];
    }
    rc = fts3tokQueryTokenizer((Fts3Hash*)pHash, zModule, &pMod, pzErr);
  }

  assert( (rc==SQLITE_OK)==(pMod!=0) );


  if( rc==SQLITE_OK ){

    const char * const *azArg = (const char * const *)&azDequote[1];
    rc = pMod->xCreate((nDequote>1 ? nDequote-1 : 0), azArg, &pTok);
  }

  if( rc==SQLITE_OK ){
    pTab = (Fts3tokTable *)sqlite3_malloc(sizeof(Fts3tokTable));
    if( pTab==0 ){

  return SQLITE_OK;
}

/*
** Register the fts3tok module with database connection db. Return SQLITE_OK
** if successful or an error code if sqlite3_create_module() fails.
*/
int sqlite3Fts3InitTok(sqlite3 *db, Fts3Hash *pHash){
  static const sqlite3_module fts3tok_module = {
     0,                           /* iVersion      */
     fts3tokConnectMethod,        /* xCreate       */
     fts3tokConnectMethod,        /* xConnect      */
     fts3tokBestIndexMethod,      /* xBestIndex    */
     fts3tokDisconnectMethod,     /* xDisconnect   */
     fts3tokDisconnectMethod,     /* xDestroy      */

     0,                           /* xRename       */
     0,                           /* xSavepoint    */
     0,                           /* xRelease      */
     0                            /* xRollbackTo   */
  };
  int rc;                         /* Return code */

  rc = sqlite3_create_module(db, "fts3tokenize", &fts3tok_module, (void*)pHash);
  return rc;
}

#endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS3) */

    pIdxInfo->aConstraintUsage[gtIdx].omit = 1;
  }
  if( pIdxInfo->nOrderBy==1
   && pIdxInfo->aOrderBy[0].desc==0
  ){
    pIdxInfo->orderByConsumed = 1;
  }
  if( (idxNum & 12)==0 ){
    pIdxInfo->estimatedCost = (double)100000000;
  }else if( (idxNum & 3)==0 ){
    pIdxInfo->estimatedCost = (double)5;
  }else{
    pIdxInfo->estimatedCost = (double)1;
  }
  return SQLITE_OK;
}

/*
** A virtual table module that provides read-only access to a
** Tcl global variable namespace.
*/

# is preferred.  The rule using offset 60 for Monotone is for historical
# compatibility only.
#
0    string  =SQLite\ format\ 3
>68  belong  =0x0f055111  Fossil repository -
>68  belong  =0x0f055112  Fossil checkout - 
>68  belong  =0x0f055113  Fossil global configuration - 
>68  belong  =0x42654462  Bentley Systems BeSQLite Database -
>68  belong  =0x42654c6e  Bentley Systems Localization File -
>60  belong  =0x5f4d544e  Monotone source repository -
>0   string  =SQLite      SQLite3 database

      pParse->checkSchema = 1;
      return SQLITE_ERROR;
    }
    pFrom->pIndex = pIdx;
  }
  return SQLITE_OK;
}
/*
** Detect compound SELECT statements that use an ORDER BY clause with 
** an alternative collating sequence.
**
**    SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
**
** These are rewritten as a subquery:
**
**    SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
**     ORDER BY ... COLLATE ...
**
** This transformation is necessary because the multiSelectOrderBy() routine
** above that generates the code for a compound SELECT with an ORDER BY clause
** uses a merge algorithm that requires the same collating sequence on the
** result columns as on the ORDER BY clause.  See ticket
** http://www.sqlite.org/src/info/6709574d2a
**
** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
** The UNION ALL operator works fine with multiSelectOrderBy() even when
** there are COLLATE terms in the ORDER BY.
*/
static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){
  int i;
  Select *pNew;
  Select *pX;
  sqlite3 *db;
  struct ExprList_item *a;
  SrcList *pNewSrc;
  Parse *pParse;
  Token dummy;

  if( p->pPrior==0 ) return WRC_Continue;
  if( p->pOrderBy==0 ) return WRC_Continue;
  for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){}
  if( pX==0 ) return WRC_Continue;
  a = p->pOrderBy->a;
  for(i=p->pOrderBy->nExpr-1; i>=0; i--){
    if( a[i].pExpr->flags & EP_Collate ) break;
  }
  if( i<0 ) return WRC_Continue;

  /* If we reach this point, that means the transformation is required. */

  pParse = pWalker->pParse;
  db = pParse->db;
  pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
  if( pNew==0 ) return WRC_Abort;
  memset(&dummy, 0, sizeof(dummy));
  pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0,0);
  if( pNewSrc==0 ) return WRC_Abort;
  *pNew = *p;
  p->pSrc = pNewSrc;
  p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ALL, 0));
  p->op = TK_SELECT;
  p->pWhere = 0;
  pNew->pGroupBy = 0;
  pNew->pHaving = 0;
  pNew->pOrderBy = 0;
  p->pPrior = 0;
  pNew->pLimit = 0;
  pNew->pOffset = 0;
  return WRC_Continue;
}

/*
** This routine is a Walker callback for "expanding" a SELECT statement.
** "Expanding" means to do the following:
**
**    (1)  Make sure VDBE cursor numbers have been assigned to every
**         element of the FROM clause.

** If anything goes wrong, an error message is written into pParse.
** The calling function can detect the problem by looking at pParse->nErr
** and/or pParse->db->mallocFailed.
*/
static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){
  Walker w;
  memset(&w, 0, sizeof(w));
  w.xSelectCallback = convertCompoundSelectToSubquery;
  w.xExprCallback = exprWalkNoop;
  w.pParse = pParse;
  sqlite3WalkSelect(&w, pSelect);
  w.xSelectCallback = selectExpander;
  sqlite3WalkSelect(&w, pSelect);
}


#ifndef SQLITE_OMIT_SUBQUERY
/*
** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()

        rc = sqlite3_exec(p->db,
          "SELECT sql FROM "
          "  (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
          "     FROM sqlite_master UNION ALL"
          "   SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
          "WHERE lower(tbl_name) LIKE shellstatic()"
          "  AND type!='meta' AND sql NOTNULL "
          "ORDER BY rowid",

          callback, &data, &zErrMsg);
        zShellStatic = 0;
      }
    }else{
      rc = sqlite3_exec(p->db,
         "SELECT sql FROM "
         "  (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
         "     FROM sqlite_master UNION ALL"
         "   SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
         "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
         "ORDER BY rowid",

         callback, &data, &zErrMsg
      );
    }
    if( zErrMsg ){
      fprintf(stderr,"Error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      rc = 1;

      }
      nPrintCol = 80/(maxlen+2);
      if( nPrintCol<1 ) nPrintCol = 1;
      nPrintRow = (nRow + nPrintCol - 1)/nPrintCol;
      for(i=0; i<nPrintRow; i++){
        for(j=i; j<nRow; j+=nPrintRow){
          char *zSp = j<nPrintRow ? "" : "  ";
          fprintf(p->out, "%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : "");
        }
        fprintf(p->out, "\n");
      }
    }
    for(ii=0; ii<nRow; ii++) sqlite3_free(azResult[ii]);
    sqlite3_free(azResult);
  }else

  if( c=='t' && n>=8 && strncmp(azArg[0], "testctrl", n)==0 && nArg>=2 ){

        /* sqlite3_test_control(int, db, int) */
        case SQLITE_TESTCTRL_OPTIMIZATIONS:
        case SQLITE_TESTCTRL_RESERVE:             
          if( nArg==3 ){
            int opt = (int)strtol(azArg[2], 0, 0);        
            rc = sqlite3_test_control(testctrl, p->db, opt);
            fprintf(p->out, "%d (0x%08x)\n", rc, rc);
          } else {
            fprintf(stderr,"Error: testctrl %s takes a single int option\n",
                    azArg[1]);
          }
          break;

        /* sqlite3_test_control(int) */
        case SQLITE_TESTCTRL_PRNG_SAVE:           
        case SQLITE_TESTCTRL_PRNG_RESTORE:        
        case SQLITE_TESTCTRL_PRNG_RESET:
          if( nArg==2 ){
            rc = sqlite3_test_control(testctrl);
            fprintf(p->out, "%d (0x%08x)\n", rc, rc);
          } else {
            fprintf(stderr,"Error: testctrl %s takes no options\n", azArg[1]);
          }
          break;

        /* sqlite3_test_control(int, uint) */
        case SQLITE_TESTCTRL_PENDING_BYTE:        
          if( nArg==3 ){
            unsigned int opt = (unsigned int)integerValue(azArg[2]);        
            rc = sqlite3_test_control(testctrl, opt);
            fprintf(p->out, "%d (0x%08x)\n", rc, rc);
          } else {
            fprintf(stderr,"Error: testctrl %s takes a single unsigned"
                           " int option\n", azArg[1]);
          }
          break;
          
        /* sqlite3_test_control(int, int) */
        case SQLITE_TESTCTRL_ASSERT:              
        case SQLITE_TESTCTRL_ALWAYS:              
          if( nArg==3 ){
            int opt = atoi(azArg[2]);        
            rc = sqlite3_test_control(testctrl, opt);
            fprintf(p->out, "%d (0x%08x)\n", rc, rc);
          } else {
            fprintf(stderr,"Error: testctrl %s takes a single int option\n",
                            azArg[1]);
          }
          break;

        /* sqlite3_test_control(int, char *) */
#ifdef SQLITE_N_KEYWORD
        case SQLITE_TESTCTRL_ISKEYWORD:           
          if( nArg==3 ){
            const char *opt = azArg[2];        
            rc = sqlite3_test_control(testctrl, opt);
            fprintf(p->out, "%d (0x%08x)\n", rc, rc);
          } else {
            fprintf(stderr,"Error: testctrl %s takes a single char * option\n",
                            azArg[1]);
          }
          break;
#endif

    }else{
      sqlite3_trace(p->db, sql_trace_callback, p->traceOut);
    }
#endif
  }else

  if( c=='v' && strncmp(azArg[0], "version", n)==0 ){
    fprintf(p->out, "SQLite %s %s\n" /*extra-version-info*/,
        sqlite3_libversion(), sqlite3_sourceid());
  }else

  if( c=='v' && strncmp(azArg[0], "vfsname", n)==0 ){
    const char *zDbName = nArg==2 ? azArg[1] : "main";
    char *zVfsName = 0;
    if( p->db ){
      sqlite3_file_control(p->db, zDbName, SQLITE_FCNTL_VFSNAME, &zVfsName);
      if( zVfsName ){
        fprintf(p->out, "%s\n", zVfsName);
        sqlite3_free(zVfsName);
      }
    }
  }else

#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  if( c=='w' && strncmp(azArg[0], "wheretrace", n)==0 ){

        }
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      if( sqlite3IsRowid(pChanges->a[i].zName) ){
        j = -1;
        chngRowid = 1;
        pRowidExpr = pChanges->a[i].pExpr;
      }else{
        sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
        pParse->checkSchema = 1;
        goto update_cleanup;
      }
    }
#ifndef SQLITE_OMIT_AUTHORIZATION
    {
      int rc;
      rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
                            j<0 ? "ROWID" : pTab->aCol[j].zName,
                            db->aDb[iDb].zName);
      if( rc==SQLITE_DENY ){
        goto update_cleanup;
      }else if( rc==SQLITE_IGNORE ){
        aXRef[j] = -1;
      }
    }
#endif

          SQLITE_READ t5 x main t5_tr1   \
    ]
  do_test auth-5.3.2 {
    execsql { SELECT * FROM t5 }
  } {1}
}

# Ticket [0eb70d77cb05bb22720]:  Invalid pointer passsed to the authorizer
# callback when updating a ROWID.
#
do_test auth-6.1 {
  execsql {
    CREATE TABLE t6(a,b,c,d,e,f,g,h);
    INSERT INTO t6 VALUES(1,2,3,4,5,6,7,8);
  }
} {}
set ::authargs [list]
proc auth {args} {
  eval lappend ::authargs $args
  return SQLITE_OK
}
do_test auth-6.2 {
  execsql {UPDATE t6 SET rowID=rowID+100}
  set ::authargs
} [list SQLITE_READ   t6 ROWID main {} \
        SQLITE_UPDATE t6 ROWID main {} \
]
do_test auth-6.3 {
  execsql {SELECT rowid, * FROM t6}
} {101 1 2 3 4 5 6 7 8}

rename proc {}
rename proc_real proc


finish_test

#-------------------------------------------------------------------------
# Error cases.
#
do_catchsql_test 2.0 {
  CREATE VIRTUAL TABLE tX USING fts3tokenize(nosuchtokenizer);
} {1 {unknown tokenizer: nosuchtokenizer}}



do_catchsql_test 2.1 {






  CREATE VIRTUAL TABLE t4 USING fts3tokenize;
  SELECT * FROM t4;
} {1 {SQL logic error or missing database}}


finish_test

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-2.35 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY y COLLATE NOCASE,x,z
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.36 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY y COLLATE NOCASE DESC,x,z
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.37 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY c,b,a
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.40 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY z COLLATE BINARY DESC,x,y
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-2.41 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a,b,c
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-2.86 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE,x,z
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.87 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE DESC,x,z
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.91 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY z COLLATE BINARY DESC,x,y
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-2.92 {
  execsql {
    SELECT x,y,z FROM t2
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT c,b,a FROM t1

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-3.35 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY y COLLATE NOCASE,x,z
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.36 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY y COLLATE NOCASE DESC,x,z
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.37 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY c,b,a
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.40 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
    ORDER BY z COLLATE BINARY DESC,x,y
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-3.41 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a,b,c
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-3.86 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE,x,z
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.87 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE DESC,x,z
  }

    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.91 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY z COLLATE BINARY DESC,x,y
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-3.92 {
  execsql {
    SELECT x,y,z FROM t2
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT c,b,a FROM t1

# 2013-05-07
#
# 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 compound SELECT statements
# that have ORDER BY clauses with collating sequences that differ
# from the collating sequence used for comparison in the compound.
# 
# Ticket 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7:
# drh added on 2013-05-06 15:21:16:
#
# In the code shown below (which is intended to be run from the
# sqlite3.exe command-line tool) the three SELECT statements should all
# generate the same answer. But the third one does not. It is as if the
# COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT
# operator. Note that the ".print" commands are instructions to the
# sqlite3.exe shell program to output delimiter lines so that you can more
# easily tell where the output of one query ends and the next query
# begins. 
# 
#     CREATE TABLE t1(a);
#     INSERT INTO t1 VALUES('abc'),('def');
#     CREATE TABLE t2(a);
#     INSERT INTO t2 VALUES('DEF');
# 
#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
#     .print -----
#     SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
#      ORDER BY a COLLATE nocase;
#     .print -----
#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
# 
# Bisecting shows that this problem was introduced in SQLite version 3.6.0
# by check-in [8bbfa97837a74ef] on 2008-06-15. 
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test selectE-1.0 {
  db eval {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES('abc'),('def'),('ghi');
    CREATE TABLE t2(a);
    INSERT INTO t2 VALUES('DEF'),('abc');
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES('def'),('jkl');

    SELECT a FROM t1 EXCEPT SELECT a FROM t2
     ORDER BY a COLLATE nocase;
  }
} {def ghi}
do_test selectE-1.1 {
  db eval {
    SELECT a FROM t2 EXCEPT SELECT a FROM t3
     ORDER BY a COLLATE nocase;
  }
} {abc DEF}
do_test selectE-1.2 {
  db eval {
    SELECT a FROM t2 EXCEPT SELECT a FROM t3
     ORDER BY a COLLATE binary;
  }
} {DEF abc}
do_test selectE-1.3 {
  db eval {
    SELECT a FROM t2 EXCEPT SELECT a FROM t3
     ORDER BY a;
  }
} {DEF abc}

do_test selectE-2.1 {
  db eval {
    DELETE FROM t2;
    DELETE FROM t3;
    INSERT INTO t2 VALUES('ABC'),('def'),('GHI'),('jkl');
    INSERT INTO t3 SELECT lower(a) FROM t2;
    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
     ORDER BY 1
  }
} {}
do_test selectE-2.2 {
  db eval {
    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
     ORDER BY 1 COLLATE binary
  }
} {}

finish_test