SQLite

**    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.67 2002/06/02 16:09:02 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function

        }
      }
      return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0);
    }
  }
  return 0;
}

/*
** If the given expression codes a constant integer, return 1 and put
** the value of the integer in *pValue.  If the expression is not an
** integer, return 0 and leave *pValue unchanged.
*/
int sqliteExprIsInteger(Expr *p, int *pValue){
  switch( p->op ){
    case TK_INTEGER: {
      *pValue = atoi(p->token.z);
      return 1;
    }
    case TK_STRING: {
      char *z = p->token.z;
      int n = p->token.n;
      if( n>0 && z=='-' ){ z++; n--; }
      while( n>0 && *z && isdigit(*z) ){ z++; n--; }
      if( n==0 ){
        *pValue = atoi(p->token.z);
        return 1;
      }
      break;
    }
    case TK_UMINUS: {
      int v;
      if( sqliteExprIsInteger(p->pLeft, &v) ){
        *pValue = -v;
        return 1;
      }
      break;
    }
    default: break;
  }
  return 0;
}

/*
** Return TRUE if the given string is a row-id column name.
*/
static int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==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.90 2002/06/02 16:09:02 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/

** Any entry that does not match is flagged as an error.  The number
** of errors is returned.
*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this value in iTable */
  int mustComplete        /* If TRUE all ORDER BYs must match */
){
  int nErr = 0;
  int i, j;
  ExprList *pEList;

  if( pSelect==0 || pOrderBy==0 ) return 1;

    if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
      return 1;
    }
  }
  pEList = pSelect->pEList;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    if( pOrderBy->a[i].done ) continue;
    if( sqliteExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>pEList->nExpr ){
        char zBuf[200];
        sprintf(zBuf,"ORDER BY position %d should be between 1 and %d",
           iCol, pEList->nExpr);
        sqliteSetString(&pParse->zErrMsg, zBuf, 0);
        pParse->nErr++;
        nErr++;
        break;
      }
      iCol--;
    }
    for(j=0; iCol<0 && j<pEList->nExpr; j++){
      if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
        char *zName, *zLabel;
        zName = pEList->a[j].zName;
        assert( pE->token.z );
        zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
        sqliteDequote(zLabel);
        if( sqliteStrICmp(zName, zLabel)==0 ){ 
          iCol = j;
        }
        sqliteFree(zLabel);
      }
      if( iCol<0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
        iCol = j;
      }
    }
    if( iCol>=0 ){
      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;
      pOrderBy->a[i].done = 1;

    }

    if( iCol<0 && mustComplete ){
      char zBuf[30];
      sprintf(zBuf,"%d",i+1);
      sqliteSetString(&pParse->zErrMsg, "ORDER BY term number ", zBuf, 
        " does not match any result column", 0);
      pParse->nErr++;
      nErr++;
      break;

      goto select_end;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){
        int iCol;
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
          sqliteSetString(&pParse->zErrMsg, 
               "ORDER BY terms must not be non-integer constants", 0);
          pParse->nErr++;
          goto select_end;
        }else if( iCol<=0 || iCol>pEList->nExpr ){
          char zBuf[2000];
          sprintf(zBuf,"ORDER BY column number %d out of range - should be "
             "between 1 and %d", iCol, pEList->nExpr);
          sqliteSetString(&pParse->zErrMsg, zBuf, 0);
          pParse->nErr++;
          goto select_end;
        }
        sqliteExprDelete(pE);
        pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }

/*
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.119 2002/06/02 16:09:02 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*, int);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
int sqliteExprIsInteger(Expr*, int*);
void sqliteGenerateRowDelete(Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);

# 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 SELECT statement.
#
# $Id: select1.test,v 1.30 2002/06/02 16:09:03 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)}} msg]
  lappend v $msg
} {1 {misuse of aggregate function min()}}
do_test select1-4.5 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.6 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY '8.4';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 'xyz';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.8 {
  execsql {
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,10);
    INSERT INTO t5 VALUES(2,9);
    SELECT * FROM t5 ORDER BY 1;
  }
} {1 10 2 9}
do_test select1-4.9 {
  execsql {
    SELECT * FROM t5 ORDER BY 2;
  }
} {2 9 1 10}
do_test select1-4.10 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}}
do_test select1-4.11 {
  execsql {
    INSERT INTO t5 VALUES(3,10);
    SELECT * FROM t5 ORDER BY 2, 1 DESC;
  }
} {2 9 3 10 1 10}
do_test select1-4.12 {
  execsql {
    SELECT * FROM t5 ORDER BY 1 DESC, b;
  }
} {3 10 2 9 1 10}
do_test select1-4.13 {
  execsql {
    SELECT * FROM t5 ORDER BY b DESC, 1;
  }
} {1 10 3 10 2 9}


# 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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.10 2002/06/02 16:09:03 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]

    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2f {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2g {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 1;
  }
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2h {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 2;
  }
} {1 {ORDER BY position 2 should be between 1 and 1}}
do_test select4-5.2i {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 2, 1;
  }
} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
do_test select4-5.2j {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 1, 2 DESC;
  }
} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
do_test select4-5.2k {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY n, 1;
  }
} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
do_test select4-5.3 {
  set v [catch {execsql {
    SELECT DISTINCT log, n FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }} msg]