SQLite

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** 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.465 2005/04/01 10:47:40 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

case OP_SortPut: {        /* no-push */
  Mem *pNos = &pTos[-1];
  Sorter *pSorter;
  assert( pNos>=p->aStack );
  if( Dynamicify(pTos, db->enc) ) goto no_mem;
  pSorter = sqliteMallocRaw( sizeof(Sorter) );
  if( pSorter==0 ) goto no_mem;
  pSorter->pNext = 0;
  if( p->pSortTail ){
    p->pSortTail->pNext = pSorter;
  }else{
    p->pSort = pSorter;
  }
  p->pSortTail = pSorter;
  assert( pTos->flags & MEM_Dyn );
  pSorter->nKey = pTos->n;
  pSorter->zKey = pTos->z;
  pSorter->data.flags = MEM_Null;
  rc = sqlite3VdbeMemMove(&pSorter->data, pNos);
  pTos -= 2;
  break;

  Mem *aStack;        /* The operand stack, except string values */
  Mem *pTos;          /* Top entry in the operand stack */
  Mem **apArg;        /* Arguments to currently executing user function */
  Mem *aColName;      /* Column names to return */
  int nCursor;        /* Number of slots in apCsr[] */
  Cursor **apCsr;     /* One element of this array for each open cursor */
  Sorter *pSort;      /* A linked list of objects to be sorted */
  Sorter *pSortTail;  /* Last element on the pSort list */
  int nVar;           /* Number of entries in aVar[] */
  Mem *aVar;          /* Values for the OP_Variable opcode. */
  char **azVar;       /* Name of variables */
  int okVar;          /* True if azVar[] has been initialized */
  int magic;              /* Magic number for sanity checking */
  int nMem;               /* Number of memory locations currently allocated */
  Mem *aMem;              /* The memory locations */

  while( p->pSort ){
    Sorter *pSorter = p->pSort;
    p->pSort = pSorter->pNext;
    sqliteFree(pSorter->zKey);
    sqlite3VdbeMemRelease(&pSorter->data);
    sqliteFree(pSorter);
  }
  p->pSortTail = 0;
}

/*
** Free all resources allociated with AggElem pElem, an element of
** aggregate pAgg.
*/
static void freeAggElem(AggElem *pElem, Agg *pAgg){

#    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: collate4.test,v 1.8 2005/04/01 10:47:40 drh Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]

  cksort {SELECT b FROM collate4t1 ORDER BY b}
} {{} A B a b nosort}
do_test collate4-1.1.5 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.6 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
} {{} a A b B sort}

do_test collate4-1.1.7 {
  execsql {
    CREATE TABLE collate4t2(
      a PRIMARY KEY COLLATE NOCASE, 
      b UNIQUE COLLATE TEXT
    );

    INSERT INTO collate4t4 VALUES( 'A', 'A' );
    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
  }
} {}
do_test collate4-1.1.22 {
  cksort {SELECT a FROM collate4t4 ORDER BY a}
} {{} a A b B sort}
do_test collate4-1.1.23 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
} {{} a A b B sort}
do_test collate4-1.1.24 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.25 {
  cksort {SELECT b FROM collate4t4 ORDER BY b}
} {{} A B a b sort}
do_test collate4-1.1.26 {

  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
} {{} A B a b sort}
do_test collate4-1.2.4 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b}
} {{} A a B b nosort}
do_test collate4-1.2.5 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
} {{} a A b B sort}
do_test collate4-1.2.6 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
} {{} A a B b nosort}

do_test collate4-1.2.7 {
  execsql {
    CREATE TABLE collate4t2(

    INSERT INTO collate4t3 VALUES( 'B', 'B' );
    INSERT INTO collate4t3 VALUES( 'A', 'A' );
    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
  }
} {}
do_test collate4-1.2.15 {
  cksort {SELECT a FROM collate4t3 ORDER BY a}
} {{} a A b B sort}
do_test collate4-1.2.16 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
} {{} a A b B sort}
do_test collate4-1.2.17 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
} {{} A B a b nosort}
do_test collate4-1.2.18 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
} {{} A B a b sort}
do_test collate4-1.2.19 {

#
#*************************************************************************
# 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.4 2005/04/01 10:47:40 drh Exp $

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


#
# Tests are organised as follows:

# 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 {

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.13 2005/04/01 10:47:40 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {

do_test select3-2.6 {
  execsql {
    SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
  execsql {
    SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.8 {
  execsql {
    SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
  }
} {11 15 9 8 7 4 5 2 3 1 1 1}
do_test select3-2.9 {
  catchsql {