**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.125 2004/05/20 22:16:29 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
** The sqlite3ExprResolveIds() and sqlite3ExprCheck() routines must have
** both been called on the expression before it is passed to this routine.
*/
int sqlite3ExprType(Expr *p){
  if( p==0 ) return SQLITE_AFF_NONE;
  while( p ) switch( p->op ){
    case TK_CONCAT:

      return SQLITE_AFF_TEXT;

    case TK_AS:
      p = p->pLeft;
      break;


    case TK_NULL:
      return SQLITE_AFF_NONE;

    case TK_SELECT:   /*** FIX ME ****/
    case TK_COLUMN:   /*** FIX ME ****/
    case TK_CASE:     /*** FIX ME ****/

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.126 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
** The sqlite3ExprResolveIds() and sqlite3ExprCheck() routines must have
** both been called on the expression before it is passed to this routine.
*/
int sqlite3ExprType(Expr *p){
  if( p==0 ) return SQLITE_AFF_NONE;
  while( p ) switch( p->op ){
    case TK_CONCAT:
    case TK_STRING:
      return SQLITE_AFF_TEXT;

    case TK_AS:
      p = p->pLeft;
      break;

    case TK_VARIABLE:
    case TK_NULL:
      return SQLITE_AFF_NONE;

    case TK_SELECT:   /*** FIX ME ****/
    case TK_COLUMN:   /*** FIX ME ****/
    case TK_CASE:     /*** FIX ME ****/

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.172 2004/05/21 01:29:06 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** instead of OP_SortMakeKey.  Delete the OP_SortMakeKey opcode.
** All columns should have affinity NONE.  Handle ASC versus
** DESC sort order by defining a list of comparison functions to
** be used by the OP_Sort opcode.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;
#if 0
  char *zSortOrder;
  zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
  if( zSortOrder==0 ) return;
#endif
  for(i=0; i<pOrderBy->nExpr; i++){
#if 0
    int order = pOrderBy->a[i].sortOrder;
    int c;
    if( order==SQLITE_SO_ASC ){
      c = 'A';
    }else{
      c = 'D';
    }
    zSortOrder[i] = c;
#endif
    sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr);
  }
#if 0
  zSortOrder[pOrderBy->nExpr] = 0;
  sqlite3VdbeOp3(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, P3_DYNAMIC);
#endif
  sqlite3VdbeAddOp(v, OP_MakeKey, pOrderBy->nExpr, 0);
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
................................................................................
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
    }else{
      zType = "ANY";
      /** TODO:  Perhaps something related to the affinity of the 
      ** exprsssion? */


    }
    sqlite3VdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
  }
}

/*
** Generate code that will tell the VDBE the names of columns
................................................................................
** KeyInfo structure.  The number of columns in the KeyInfo is determined
** by the result set of the SELECT statement in the second argument.
**
** Make the new table a KeyAsData table if keyAsData is true.
*/
static void openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
  KeyInfo *pKeyInfo;
  int nColumn = p->pEList->nExpr;
  sqlite *db = pParse->db;
  int i;
  Vdbe *v = pParse->pVdbe;





  pKeyInfo = sqliteMalloc( sizeof(*pKeyInfo)+nColumn*sizeof(CollSeq*) );
  if( pKeyInfo==0 ) return;
  pKeyInfo->nField = nColumn;
  for(i=0; i<nColumn; i++){
    pKeyInfo->aColl[i] = db->pDfltColl;
  }
  sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.173 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** instead of OP_SortMakeKey.  Delete the OP_SortMakeKey opcode.
** All columns should have affinity NONE.  Handle ASC versus
** DESC sort order by defining a list of comparison functions to
** be used by the OP_Sort opcode.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;





  for(i=0; i<pOrderBy->nExpr; i++){










    sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr);
  }




  sqlite3VdbeAddOp(v, OP_MakeKey, pOrderBy->nExpr, 0);
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
................................................................................
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
    }else{
      switch( sqlite3ExprType(p) ){
        case SQLITE_AFF_TEXT:     zType = "TEXT";    break;
        case SQLITE_AFF_NUMERIC:  zType = "NUMERIC"; break;
        default:                  zType = "ANY";     break;
      }
    }
    sqlite3VdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
  }
}

/*
** Generate code that will tell the VDBE the names of columns
................................................................................
** KeyInfo structure.  The number of columns in the KeyInfo is determined
** by the result set of the SELECT statement in the second argument.
**
** Make the new table a KeyAsData table if keyAsData is true.
*/
static void openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
  KeyInfo *pKeyInfo;
  int nColumn;
  sqlite *db = pParse->db;
  int i;
  Vdbe *v = pParse->pVdbe;

  if( fillInColumnList(pParse, p) ){
    return;
  }
  nColumn = p->pEList->nExpr;
  pKeyInfo = sqliteMalloc( sizeof(*pKeyInfo)+nColumn*sizeof(CollSeq*) );
  if( pKeyInfo==0 ) return;
  pKeyInfo->nField = nColumn;
  for(i=0; i<nColumn; i++){
    pKeyInfo->aColl[i] = db->pDfltColl;
  }
  sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);

**
** 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.310 2004/05/21 01:29:06 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  pTos++;
  pTos->n = nByte;
  pTos->z = (char*)azArg;
  pTos->flags = MEM_Str | MEM_Dyn;
  break;
}

/* Opcode: SortMakeKey * * P3
**
** Convert the top few entries of the stack into a sort key.  The
** number of stack entries consumed is the number of characters in 
** the string P3.  One character from P3 is prepended to each entry.
** The first character of P3 is prepended to the element lowest in
** the stack and the last character of P3 is prepended to the top of
** the stack.  All stack entries are separated by a \000 character
** in the result.  The whole key is terminated by two \000 characters
** in a row.
**
** "N" is substituted in place of the P3 character for NULL values.
**
** See also the MakeKey and MakeIdxKey opcodes.
*/
case OP_SortMakeKey: {
  char *zNewKey;
  int nByte;
  int nField;
  int i, j, k;
  Mem *pRec;

  nField = strlen(pOp->p3);
  pRec = &pTos[1-nField];
  nByte = 1;
  for(i=0; i<nField; i++, pRec++){
    if( pRec->flags & MEM_Null ){
      nByte += 2;
    }else{
      Stringify(pRec);
      nByte += pRec->n+2;
    }
  }
  zNewKey = sqliteMallocRaw( nByte );
  if( zNewKey==0 ) goto no_mem;
  j = 0;
  k = 0;
  for(pRec=&pTos[1-nField], i=0; i<nField; i++, pRec++){
    if( pRec->flags & MEM_Null ){
      zNewKey[j++] = 'N';
      zNewKey[j++] = 0;
      k++;
    }else{
      zNewKey[j++] = pOp->p3[k++];
      memcpy(&zNewKey[j], pRec->z, pRec->n-1);
      j += pRec->n-1;
      zNewKey[j++] = 0;
    }
  }
  zNewKey[j] = 0;
  assert( j<nByte );
  popStack(&pTos, nField);
  pTos++;
  pTos->n = nByte;
  pTos->flags = MEM_Str|MEM_Dyn;
  pTos->z = zNewKey;
  break;
}

/* Opcode: Sort * * P3
**
** Sort all elements on the sorter.  The algorithm is a
** mergesort.  The P3 argument is a pointer to a KeyInfo structure
** that describes the keys to be sorted.
*/
case OP_Sort: {

**
** 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.311 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  pTos++;
  pTos->n = nByte;
  pTos->z = (char*)azArg;
  pTos->flags = MEM_Str | MEM_Dyn;
  break;
}




























































/* Opcode: Sort * * P3
**
** Sort all elements on the sorter.  The algorithm is a
** mergesort.  The P3 argument is a pointer to a KeyInfo structure
** that describes the keys to be sorted.
*/
case OP_Sort: {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.10 2004/05/13 11:34:17 danielk1977 Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.4 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.5 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.6 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.7 {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.11 2004/05/21 02:14:25 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.4 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT ANY}
do_test pragma-2.5 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT ANY}
do_test pragma-2.6 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.7 {

#    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.8 2003/01/31 17:16:37 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {3 4 4 8 5 15}
do_test select3-4.3 {
  execsql {
    SELECT log, count(*) FROM t1 
    GROUP BY log 
    HAVING count(*)>=4 
    ORDER BY max(n)
  }
} {3 4 4 8 5 15}
do_test select3-4.4 {
  execsql {
    SELECT log AS x, count(*) AS y FROM t1 
    GROUP BY x
    HAVING y>=4 
    ORDER BY max(n)
  }
} {3 4 4 8 5 15}
do_test select3-4.5 {
  execsql {
    SELECT log AS x FROM t1 
    GROUP BY x
    HAVING count(*)>=4 
    ORDER BY max(n)
  }
} {3 4 5}

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2), avg(n)
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2), min(log,avg(n))
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {

#    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.9 2004/05/21 02:14:25 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {3 4 4 8 5 15}
do_test select3-4.3 {
  execsql {
    SELECT log, count(*) FROM t1 
    GROUP BY log 
    HAVING count(*)>=4 
    ORDER BY max(n)+0
  }
} {3 4 4 8 5 15}
do_test select3-4.4 {
  execsql {
    SELECT log AS x, count(*) AS y FROM t1 
    GROUP BY x
    HAVING y>=4 
    ORDER BY max(n)+0
  }
} {3 4 4 8 5 15}
do_test select3-4.5 {
  execsql {
    SELECT log AS x FROM t1 
    GROUP BY x
    HAVING count(*)>=4 
    ORDER BY max(n)+0
  }
} {3 4 5}

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, avg(n)+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, min(log,avg(n))+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {

#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.10 2004/05/14 11:00:53 danielk1977 Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}

do_test sort-7.9 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
  }

} {1 2 11 12}
do_test sort-7.10 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;
  }

} {1 2 11 12}
do_test sort-7.11 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
  }
} {1 11 12 2}
do_test sort-7.12 {
  execsql {

#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.11 2004/05/21 02:14:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2}  ;# text from t4.b and numeric from v4.a
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2} ;# numeric from t4.a and text from v4.b
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}
#### Version 3 works differently here:
#do_test sort-7.9 {
#  execsql {
#    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;

#  }
#} {1 2 11 12}
#do_test sort-7.10 {
#  execsql {
#    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;

#  }
#} {1 2 11 12}
do_test sort-7.11 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
  }
} {1 11 12 2}
do_test sort-7.12 {
  execsql {

#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.19 2004/05/19 14:56:57 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {1 0 2 1 3 1 nosort}
do_test where-6.16 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
  }
} {1 0 2 1 3 1 sort}

do_test where-6.17 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
  }

} {4 121 10201 sort}
do_test where-6.18 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
  }

} {4 9 16 sort}
do_test where-6.19 {
  cksort {
    SELECT y FROM t1 ORDER BY w LIMIT 3;
  }
} {4 9 16 nosort}

# Tests for reverse-order sorting.

#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.20 2004/05/21 02:14:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {1 0 2 1 3 1 nosort}
do_test where-6.16 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
  }
} {1 0 2 1 3 1 sort}
#### Version 3 does not work this way:
#do_test where-6.17 {
#  cksort {
#    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;

#  }
#} {4 121 10201 sort}
#do_test where-6.18 {
#  cksort {
#    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;

#  }
#} {4 9 16 sort}
do_test where-6.19 {
  cksort {
    SELECT y FROM t1 ORDER BY w LIMIT 3;
  }
} {4 9 16 nosort}

# Tests for reverse-order sorting.