SQLite

**    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.72 2002/03/03 02:49:51 drh Exp $
*/
#include "sqliteInt.h"

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

** same column in table number iTo.
*/
static void changeTables(Expr *pExpr, int iFrom, int iTo){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
    pExpr->iTable = iTo;
  }else{
    static void changeTablesInList(ExprList*, int, int);
    changeTables(pExpr->pLeft, iFrom, iTo);
    changeTables(pExpr->pRight, iFrom, iTo);
    changeTablesInList(pExpr->pList, iFrom, iTo);
  }
}
static void changeTablesInList(ExprList *pList, int iFrom, int iTo){
  if( pList ){
    int i;
    for(i=0; i<pList->nExpr; i++){
      changeTables(pList->a[i].pExpr, iFrom, iTo);

    }
  }
}

/*
** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the corresponding

    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    pExpr->token = pNew->token;
    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);

  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
  if( pSub->pWhere ){
    pWhere = sqliteExprDup(pSub->pWhere);
    if( iParent!=iSub ){
      changeTables(pWhere, iSub, iParent);
    }
  }else{
    pWhere = 0;
  }
  if( subqueryIsAgg ){
    assert( p->pHaving==0 );
    p->pHaving = p->pWhere;
    p->pWhere = pWhere;
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
    if( pSub->pHaving ){
      Expr *pHaving = sqliteExprDup(pSub->pHaving);
      if( iParent!=iSub ){
        changeTables(pHaving, iSub, iParent);
      }
      if( p->pHaving ){
        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
      }else{
        p->pHaving = pHaving;
      }
    }
    assert( p->pGroupBy==0 );
    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
    if( iParent!=iSub ){
      changeTablesInList(p->pGroupBy, iSub, iParent);
    }
  }else if( p->pWhere==0 ){
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList, iSub);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }

**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.
**
** The pParent, parentTab, and *pParentAgg fields are filled in if this
** SELECT is a subquery.  This routine may try to combine this SELECT
** with its parent to form a single flat query.  In so doing, it might
** change the parent query from a non-aggregate to an aggregate query.
** For that reason, the pParentAgg flag is passed as a pointer, so it
** can be changed.
*/
int sqliteSelect(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  int iParm,             /* Save result in this memory location, if >=0 */
  Select *pParent,       /* Another SELECT for which this is a sub-query */
  int parentTab,         /* Index in pParent->pSrc of this query */
  int *pParentAgg        /* True if pParent uses aggregate functions */
){
  int i;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of columns to extract. */
  IdList *pTabList;      /* List of tables to select from */

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteVdbeAddOp(v, OP_OpenTemp, base+i, 0);
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, base+i,
                 p, i, &isAgg);
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    pOrderBy = p->pOrderBy;
    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }

  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
  if( pParent && pParentAgg &&
      flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }







  /* Do an analysis of aggregate expressions.
  */
  sqliteAggregateInfoReset(pParse);
  if( isAgg ){
    assert( pParse->nAgg==0 );
    for(i=0; i<pEList->nExpr; i++){

/*
** 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.98 2002/03/03 02:49:51 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);
int sqliteSelect(Parse*, Select*, int, int, Select*, int, int*);
Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,
                        int,int,int);
void sqliteSelectDelete(Select*);
void sqliteSelectUnbind(Select*);
Table *sqliteTableNameToTable(Parse*, const char*);
IdList *sqliteTableTokenToIdList(Parse*, Token*);
void sqliteDeleteFrom(Parse*, Token*, Expr*);

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.130 2002/03/03 02:49:51 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test

  char **pzErrMsg            /* Error msg written here */
){
  int i, rc;
  char *azValue[6];
  char zAddr[20];
  char zP1[20];
  char zP2[20];
  char zP3[40];
  static char *azColumnNames[] = {
     "addr", "opcode", "p1", "p2", "p3", 0
  };

  if( xCallback==0 ) return 0;
  azValue[0] = zAddr;
  azValue[2] = zP1;
  azValue[3] = zP2;
  azValue[5] = 0;
  rc = SQLITE_OK;
  for(i=0; rc==SQLITE_OK && i<p->nOp; i++){
    if( p->db->flags & SQLITE_Interrupt ){
      p->db->flags &= ~SQLITE_Interrupt;
      sqliteSetString(pzErrMsg, "interrupted", 0);
      rc = SQLITE_INTERRUPT;
      break;
    }
    sprintf(zAddr,"%d",i);
    sprintf(zP1,"%d", p->aOp[i].p1);
    sprintf(zP2,"%d", p->aOp[i].p2);
    if( p->aOp[i].p3type==P3_POINTER ){
      sprintf(zP3, "ptr(%#x)", (int)p->aOp[i].p3);
      azValue[4] = zP3;
    }else{
      azValue[4] = p->aOp[i].p3;
    }
    azValue[1] = zOpName[p->aOp[i].opcode];
    if( xCallback(pArg, 5, azValue, azColumnNames) ){
      rc = SQLITE_ABORT;
    }
  }
  return rc;
}

#    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 INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.8 2002/03/03 02:49:52 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {

  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

do_test insert2-3.0 {
  set x [execsql {PRAGMA integrity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

# File table t4 with lots of data
#
do_test insert2-3.1 {
  execsql {
    SELECT * from t4;
  }
} {1 2}
do_test insert2-3.2 {
  execsql {
    BEGIN;
    INSERT INTO t4 VALUES(2,4);
    INSERT INTO t4 VALUES(3,6);
    INSERT INTO t4 VALUES(4,8);
    INSERT INTO t4 VALUES(5,10);
    INSERT INTO t4 VALUES(6,12);
    INSERT INTO t4 VALUES(7,14);
    INSERT INTO t4 VALUES(8,16);
    INSERT INTO t4 VALUES(9,18);
    INSERT INTO t4 VALUES(10,20);
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {10}
do_test insert2-3.3 {
  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.4 {
  execsql {
    BEGIN;
    UPDATE t4 SET y='lots of data for the row where x=' || x
                     || ' and y=' || y || ' - even more data to fill space';
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.5 {
  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
    SELECT count(*) from t4;
    ROLLBACK;
  }
} {320}
do_test insert2-3.6 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.7 {
  execsql {
    BEGIN;
    DELETE FROM t4 WHERE x!=123;
    SELECT count(*) FROM t4;
    ROLLBACK;
  }
} {1}

finish_test

#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.3 2002/03/03 02:49:52 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;

      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY [a.q]
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}

do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}
do_test select6-3.4 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4 3 7}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a>10
  }
} {11.5 4 15.5}
do_test select6-3.9 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
    WHERE a<10
  }
} {}
do_test select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1 1 2 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY y
  }
} {1 1 2 2 4 3 8 4 5 5}

do_test select6-4.1 {
  execsql {
    SELECT a,b,c FROM 
      (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
    WHERE a<10 ORDER BY a;
  }
} {8 4 12 9 4 13}
do_test select6-4.2 {
  execsql {
    SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
  }
} {1 2 3 4}
do_test select6-4.3 {
  execsql {
    SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
  }
} {1 2 3 4}


finish_test

# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.7 2002/03/03 02:49:52 drh Exp $

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


# The vacuum command no longer functions.  There is






# nothing to test.











































# finish_test

        collide[j] = k;
        table[j]->collide = 0;
        if( k<j ) j = k-1;
      }
    }

    /* Print the hash table */
    if( tablesize>0 ){
      fprintf(out,"/* State %d */\n",stp->index); lineno++;
    }
    for(j=0; j<tablesize; j++){
      assert( table[j]!=0 );
      fprintf(out,"  {%4d,%4d,%4d}, /* %2d: ",
          table[j]->sp->index,
          collide[j]+1,
          compute_action(lemp,table[j]),
          j+1);
      PrintAction(table[j],out,22);
      fprintf(out," */\n"); 
      lineno++;
    }

    /* Update the table count */
    tablecnt += tablesize;