**    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.38 2002/01/22 03:13:42 drh Exp $
*/
#include "sqliteInt.h"


/*
** Recursively delete an expression tree.
*/
................................................................................
  sqliteFree(p);
}

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
static int isConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;




    default: {
      if( p->pLeft && !isConstant(p->pLeft) ) return 0;
      if( p->pRight && !isConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;
        for(i=0; i<p->pList->nExpr; i++){
          if( !isConstant(p->pList->a[i].pExpr) ) return 0;
        }
      }
      break;
    }
  }
  return 1;
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
................................................................................
        **
        ** Create a set to put the exprlist values in.  The Set id is stored
        ** in iTable.
        */
        int i, iSet;
        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;
          if( !isConstant(pE2) ){
            sqliteSetString(&pParse->zErrMsg,
              "right-hand side of IN operator must be constant", 0);
            pParse->nErr++;
            return 1;
          }
          if( sqliteExprCheck(pParse, pE2, 0, 0) ){
            return 1;

**    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.39 2002/01/22 14:11:29 drh Exp $
*/
#include "sqliteInt.h"


/*
** Recursively delete an expression tree.
*/
................................................................................
  sqliteFree(p);
}

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
int sqliteExprIsConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_STRING:
      return 1;
    default: {
      if( p->pLeft && !sqliteExprIsConstant(p->pLeft) ) return 0;
      if( p->pRight && !sqliteExprIsConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;
        for(i=0; i<p->pList->nExpr; i++){
          if( !sqliteExprIsConstant(p->pList->a[i].pExpr) ) return 0;
        }
      }
      return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0);
    }
  }
  return 0;
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
................................................................................
        **
        ** Create a set to put the exprlist values in.  The Set id is stored
        ** in iTable.
        */
        int i, iSet;
        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;
          if( !sqliteExprIsConstant(pE2) ){
            sqliteSetString(&pParse->zErrMsg,
              "right-hand side of IN operator must be constant", 0);
            pParse->nErr++;
            return 1;
          }
          if( sqliteExprCheck(pParse, pE2, 0, 0) ){
            return 1;

**    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.54 2002/01/22 03:13:42 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      return 1;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;






      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }
  }
  if( pGroupBy ){
    for(i=0; i<pGroupBy->nExpr; i++){
      Expr *pE = pGroupBy->a[i].pExpr;






      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }

**    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.55 2002/01/22 14:11:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      return 1;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){
        sqliteSetString(&pParse->zErrMsg, 
             "ORDER BY expressions should not be constant", 0);
        pParse->nErr++;
        return 1;
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }
  }
  if( pGroupBy ){
    for(i=0; i<pGroupBy->nExpr; i++){
      Expr *pE = pGroupBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){
        sqliteSetString(&pParse->zErrMsg, 
             "GROUP BY expressions should not be constant", 0);
        pParse->nErr++;
        return 1;
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }

**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.78 2002/01/22 03:13:42 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);

**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.79 2002/01/22 14:11:29 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.35 2002/01/14 09:28:20 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
    }while( ca!=0 );
  }else{
    map = UpperToLower;
    do{
      if( (ca=map[*a++])!=(cb=map[*b++]) ) break;
      cclass = stateMachine[cclass*N_CHAR_CLASS + charClass[ca]];
    }while( ca!=0 );


  }
  switch( cclass ){
    case 0:
    case 1: {
      if( isdigit(ca) && isdigit(cb) ){
        cclass = 2;
      }

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.36 2002/01/22 14:11:30 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
    }while( ca!=0 );
  }else{
    map = UpperToLower;
    do{
      if( (ca=map[*a++])!=(cb=map[*b++]) ) break;
      cclass = stateMachine[cclass*N_CHAR_CLASS + charClass[ca]];
    }while( ca!=0 );
    if( ca>='[' && ca<='`' ) cb = b[-1];
    if( cb>='[' && cb<='`' ) ca = a[-1];
  }
  switch( cclass ){
    case 0:
    case 1: {
      if( isdigit(ca) && isdigit(cb) ){
        cclass = 2;
      }

#    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 SELECT statement.
#
# $Id: select1.test,v 1.17 2002/01/22 03:13:43 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {too few arguments to function min()}}






# ORDER BY ignored on an aggregate query
#
do_test select1-5.1 {
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}

#    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 SELECT statement.
#
# $Id: select1.test,v 1.18 2002/01/22 14:11:30 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {too few arguments to function min()}}
do_test select1-4.5 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 8.4;
  }
} {1 {ORDER BY expressions should not be constant}}

# ORDER BY ignored on an aggregate query
#
do_test select1-5.1 {
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}

#    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.4 2002/01/22 03:13:43 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {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}






# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}

#    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.5 2002/01/22 14:11:30 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {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 {
    SELECT log, count(*) FROM t1 GROUP BY 8 ORDER BY log;
  }
} {1 {GROUP BY expressions should not be constant}}

# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}

#    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.3 2001/09/16 00:13:28 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
  execsql {
    UPDATE t1 SET v='x ' || (-1.3+0.01*n);
    SELECT v FROM t1 ORDER BY v;
  }
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}



































finish_test

#    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.4 2002/01/22 14:11:30 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
  execsql {
    UPDATE t1 SET v='x ' || (-1.3+0.01*n);
    SELECT v FROM t1 ORDER BY v;
  }
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test sort-3.1 {
  execsql {
    CREATE TABLE t2(a,b);
    INSERT INTO t2 VALUES('AGLIENTU',1);
    INSERT INTO t2 VALUES('AGLIE`',2);
    INSERT INTO t2 VALUES('AGNA',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {AGLIENTU 1 AGLIE` 2 AGNA 3}
do_test sort-3.2 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {AGNA 3 AGLIE` 2 AGLIENTU 1}
do_test sort-3.3 {
  execsql {
    DELETE FROM t2;
    INSERT INTO t2 VALUES('aglientu',1);
    INSERT INTO t2 VALUES('aglie`',2);
    INSERT INTO t2 VALUES('agna',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {aglie` 2 aglientu 1 agna 3}
do_test sort-3.4 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {agna 3 aglientu 1 aglie` 2}


finish_test

proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan ?? (2.2.4)} {
<li>The label to the right of an AS in the column list of a SELECT can now
    be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or
    HAVING clauses.</li>
<li>Fix a bug in the <b>-separator</b> command-line option to the <b>sqlite</b>
    command.</li>



}

chng {2002 Jan 16 (2.2.3)} {
<li>Fix warning messages in VC++ 7.0.  (Patches from nicolas352001)</li>
<li>Make the library thread-safe.  (The code is there and appears to work
    but has not been stressed.)</li>
<li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li>

proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan 22 (2.2.4)} {
<li>The label to the right of an AS in the column list of a SELECT can now
    be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or
    HAVING clauses.</li>
<li>Fix a bug in the <b>-separator</b> command-line option to the <b>sqlite</b>
    command.</li>
<li>Fix a problem with the sort order when comparing upper-case strings against
    characters greater than 'Z' but less than 'a'.</li>
<li>Report an error if an ORDER BY or GROUP BY expression is constant.</li>
}

chng {2002 Jan 16 (2.2.3)} {
<li>Fix warning messages in VC++ 7.0.  (Patches from nicolas352001)</li>
<li>Make the library thread-safe.  (The code is there and appears to work
    but has not been stressed.)</li>
<li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li>