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.110 2002/08/25 19:20:40 drh Exp $
*/
#include "sqliteInt.h"

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

**
** If flattening is not attempted, this routine is a no-op and return 0.
** If flattening is attempted this routine returns 1.
**
** All of the expression analysis must occur on both the outer query and
** the subquery before this routine runs.
*/
static int flattenSubquery(
  Parse *pParse,       /* The parsing context */
  Select *p,           /* The parent or outer SELECT statement */
  int iFrom,           /* Index in p->pSrc->a[] of the inner subquery */
  int isAgg,           /* True if outer SELECT uses aggregate functions */
  int subqueryIsAgg    /* True if the subquery uses aggregate functions */
){
  Select *pSub;       /* The inner query or "subquery" */
  SrcList *pSrc;      /* The FROM clause of the outer query */
  SrcList *pSubSrc;   /* The FROM clause of the subquery */
  ExprList *pList;    /* The result set of the outer query */
  int i;
  int iParent, iSub;
  Expr *pWhere;

  }else{
    substExpr(p->pWhere, iParent, pSub->pEList, iSub);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
  }
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  if( pSub->nLimit>=0 ){
    if( p->nLimit<0 ){
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;

  /* If the subquery contains subqueries of its own, that were not
  ** flattened, then code will have already been generated to put
  ** the results of those sub-subqueries into VDBE cursors relative
  ** to the subquery.  We must translate the cursor number into values
  ** suitable for use by the outer query.
  */
  for(i=0; i<pSubSrc->nSrc; i++){
    Vdbe *v;
    if( pSubSrc->a[i].pSelect==0 ) continue;
    v = sqliteGetVdbe(pParse);
    sqliteVdbeAddOp(v, OP_RenameCursor, pSub->base+i, p->base+i);
  }

  if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
    sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
  }
  pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab;
  pSubSrc->a[0].pTab = 0;
  assert( pSrc->a[iFrom].pSelect==pSub );
  pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect;

    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(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){

** Delete a linked list of TriggerStep structures.
*/
static void sqliteDeleteTriggerStep(TriggerStep *pTriggerStep){
  while( pTriggerStep ){
    TriggerStep * pTmp = pTriggerStep;
    pTriggerStep = pTriggerStep->pNext;

    if( pTmp->target.dyn ) sqliteFree((char*)pTmp->target.z);
    sqliteExprDelete(pTmp->pWhere);
    sqliteExprListDelete(pTmp->pExprList);
    sqliteSelectDelete(pTmp->pSelect);
    sqliteIdListDelete(pTmp->pIdList);

    sqliteFree(pTmp);
  }

  return pTriggerStep;
}

/* 
** Recursively delete a Trigger structure
*/
void sqliteDeleteTrigger(Trigger *pTrigger){


  sqliteDeleteTriggerStep(pTrigger->step_list);
  sqliteFree(pTrigger->name);
  sqliteFree(pTrigger->table);
  sqliteExprDelete(pTrigger->pWhen);
  sqliteIdListDelete(pTrigger->pColumns);
  sqliteFree(pTrigger);
}

** 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.171 2002/08/25 19:20:40 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

** "opNames.awk" awk script which is part of the source tree to regenerate
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "Transaction",       "Checkpoint",        "Commit",            "Rollback",
  "ReadCookie",        "SetCookie",         "VerifyCookie",      "Open",
  "OpenTemp",          "OpenWrite",         "OpenAux",           "OpenWrAux",
  "RenameCursor",      "Close",             "MoveTo",            "NewRecno",
  "PutIntKey",         "PutStrKey",         "Distinct",          "Found",
  "NotFound",          "IsUnique",          "NotExists",         "Delete",
  "Column",            "KeyAsData",         "Recno",             "FullKey",
  "NullRow",           "Last",              "Rewind",            "Next",
  "Destroy",           "Clear",             "CreateIndex",       "CreateTable",
  "IntegrityCk",       "IdxPut",            "IdxDelete",         "IdxRecno",
  "IdxGT",             "IdxGE",             "MemLoad",           "MemStore",
  "MemIncr",           "ListWrite",         "ListRewind",        "ListRead",
  "ListReset",         "ListPush",          "ListPop",           "SortPut",
  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortCallback",      "SortReset",         "FileOpen",          "FileRead",
  "FileColumn",        "AggReset",          "AggFocus",          "AggNext",
  "AggSet",            "AggGet",            "AggFunc",           "AggInit",
  "AggPush",           "AggPop",            "SetInsert",         "SetFound",
  "SetNotFound",       "SetFirst",          "SetNext",           "MakeRecord",
  "MakeKey",           "MakeIdxKey",        "IncrKey",           "Goto",
  "If",                "IfNot",             "Halt",              "Gosub",
  "Return",            "ColumnCount",       "ColumnName",        "Callback",
  "NullCallback",      "Integer",           "String",            "Pop",
  "Dup",               "Pull",              "Push",              "MustBeInt",
  "Add",               "AddImm",            "Subtract",          "Multiply",
  "Divide",            "Remainder",         "BitAnd",            "BitOr",
  "BitNot",            "ShiftLeft",         "ShiftRight",        "AbsValue",
  "Eq",                "Ne",                "Lt",                "Le",
  "Gt",                "Ge",                "StrEq",             "StrNe",
  "StrLt",             "StrLe",             "StrGt",             "StrGe",
  "IsNull",            "NotNull",           "Negative",          "And",
  "Or",                "Not",               "Concat",            "Noop",
  "Function",        
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.

  }
  if( pOut==0 ) pOut = stdout;
  fprintf(pOut,"%4d %-12s %4d %4d %s\n",
      pc, zOpName[pOp->opcode], pOp->p1, pOp->p2, zP3 ? zP3 : "");
  fflush(pOut);
}
#endif

/*
** Make sure there is space in the Vdbe structure to hold at least
** mxCursor cursors.  If there is not currently enough space, then
** allocate more.
**
** If a memory allocation error occurs, return 1.  Return 0 if
** everything works.
*/
static int expandCursorArraySize(Vdbe *p, int mxCursor){
  if( mxCursor>=p->nCursor ){
    Cursor *aCsr = sqliteRealloc( p->aCsr, (mxCursor+1)*sizeof(Cursor) );
    if( aCsr==0 ) return 1;
    p->aCsr = aCsr;
    memset(&p->aCsr[p->nCursor], 0, sizeof(Cursor)*(mxCursor+1-p->nCursor));
    p->nCursor = mxCursor+1;
  }
  return 0;
}

/*
** Execute the program in the VDBE.
**
** If an error occurs, an error message is written to memory obtained
** from sqliteMalloc() and *pzErrMsg is made to point to that memory.
** The return parameter is the number of errors.

  sqlite *db = p->db;        /* The database */
  char **zStack;             /* Text stack */
  Stack *aStack;             /* Additional stack information */
  unsigned uniqueCnt = 0;     /* Used by OP_MakeRecord when P2!=0 */
  int errorAction = OE_Abort; /* Recovery action to do in case of an error */
  int undoTransOnError = 0;   /* If error, either ROLLBACK or COMMIT */
  char zBuf[100];             /* Space to sprintf() an integer */
  int returnStack[100];     /* Return address stack for OP_Gosub & OP_Return */
  int returnDepth = 0;      /* Next unused element in returnStack[] */


  /* No instruction ever pushes more than a single element onto the
  ** stack.  And the stack never grows on successive executions of the
  ** same loop.  So the total number of instructions is an upper bound
  ** on the maximum stack depth required.
  **

** the one at index P2 from the beginning of
** the program.
*/
case OP_Goto: {
  pc = pOp->p2 - 1;
  break;
}

/* Opcode:  Gosub * P2 *
**
** Push the current address plus 1 onto the return address stack
** and then jump to address P2.
**
** The return address stack is of limited depth.  If too many
** OP_Gosub operations occur without intervening OP_Returns, then
** the return address stack will fill up and processing will abort
** with a fatal error.
*/
case OP_Gosub: {
  if( returnDepth>=sizeof(returnStack)/sizeof(returnStack[0]) ){
    sqliteSetString(pzErrMsg, "return address stack overflow", 0);
    rc = SQLITE_INTERNAL;
    goto cleanup;
  }
  returnStack[returnDepth++] = pc+1;
  pc = pOp->p2 - 1;
  break;
}

/* Opcode:  Return * * *
**
** Jump immediately to the next instruction after the last unreturned
** OP_Gosub.  If an OP_Return has occurred for all OP_Gosubs, then
** processing aborts with a fatal error.
*/
case OP_Return: {
  if( returnDepth<=0 ){
    sqliteSetString(pzErrMsg, "return address stack underflow", 0);
    rc = SQLITE_INTERNAL;
    goto cleanup;
  }
  returnDepth--;
  pc = returnStack[returnDepth] - 1;
  break;
}

/* Opcode:  Halt P1 P2 *
**
** Exit immediately.  All open cursors, Lists, Sorts, etc are closed
** automatically.
**
** P1 is the result code returned by sqlite_exec().  For a normal

    if( p2<2 ){
      sqliteSetString(pzErrMsg, "root page number less than 2", 0);
      rc = SQLITE_INTERNAL;
      goto cleanup;
    }
  }
  VERIFY( if( i<0 ) goto bad_instruction; )



  if( expandCursorArraySize(p, i) ) goto no_mem;






  cleanupCursor(&p->aCsr[i]);
  memset(&p->aCsr[i], 0, sizeof(Cursor));
  p->aCsr[i].nullRow = 1;
  if( pX==0 ) break;
  do{
    rc = sqliteBtreeCursor(pX, p2, wrFlag, &p->aCsr[i].pCursor);
    switch( rc ){

** whereas "Temporary" in the context of CREATE TABLE means for the duration
** of the connection to the database.  Same word; different meanings.
*/
case OP_OpenTemp: {
  int i = pOp->p1;
  Cursor *pCx;
  VERIFY( if( i<0 ) goto bad_instruction; )



  if( expandCursorArraySize(p, i) ) goto no_mem;






  pCx = &p->aCsr[i];
  cleanupCursor(pCx);
  memset(pCx, 0, sizeof(*pCx));
  pCx->nullRow = 1;
  rc = sqliteBtreeOpen(0, 0, TEMP_PAGES, &pCx->pBt);
  if( rc==SQLITE_OK ){
    rc = sqliteBtreeBeginTrans(pCx->pBt);

      }
    }else{
      rc = sqliteBtreeCursor(pCx->pBt, 2, 1, &pCx->pCursor);
    }
  }
  break;
}

/*
** Opcode: RenameCursor P1 P2 *
**
** Rename cursor number P1 as cursor number P2.  If P2 was previously
** opened is is closed before the renaming occurs.
*/
case OP_RenameCursor: {
  int from = pOp->p1;
  int to = pOp->p2;
  VERIFY( if( from<0 || to<0 ) goto bad_instruction; )
  if( to<p->nCursor && p->aCsr[to].pCursor ){
    cleanupCursor(&p->aCsr[to]);
  }
  expandCursorArraySize(p, to);
  if( from<p->nCursor ){
    memcpy(&p->aCsr[to], &p->aCsr[from], sizeof(p->aCsr[0]));
    memset(&p->aCsr[from], 0, sizeof(p->aCsr[0]));
  }
  break;
}

/* Opcode: Close P1 * *
**
** Close a cursor previously opened as P1.  If P1 is not
** currently open, this instruction is a no-op.
*/
case OP_Close: {

*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.58 2002/08/25 19:20:42 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines

#define OP_VerifyCookie        7

#define OP_Open                8
#define OP_OpenTemp            9
#define OP_OpenWrite          10
#define OP_OpenAux            11
#define OP_OpenWrAux          12
#define OP_RenameCursor       13
#define OP_Close              14
#define OP_MoveTo             15
#define OP_NewRecno           16
#define OP_PutIntKey          17
#define OP_PutStrKey          18
#define OP_Distinct           19
#define OP_Found              20
#define OP_NotFound           21
#define OP_IsUnique           22
#define OP_NotExists          23
#define OP_Delete             24
#define OP_Column             25
#define OP_KeyAsData          26
#define OP_Recno              27
#define OP_FullKey            28
#define OP_NullRow            29
#define OP_Last               30
#define OP_Rewind             31
#define OP_Next               32

#define OP_Destroy            33
#define OP_Clear              34
#define OP_CreateIndex        35
#define OP_CreateTable        36
#define OP_IntegrityCk        37

#define OP_IdxPut             38
#define OP_IdxDelete          39
#define OP_IdxRecno           40
#define OP_IdxGT              41
#define OP_IdxGE              42

#define OP_MemLoad            43
#define OP_MemStore           44
#define OP_MemIncr            45

#define OP_ListWrite          46
#define OP_ListRewind         47
#define OP_ListRead           48
#define OP_ListReset          49
#define OP_ListPush           50
#define OP_ListPop            51

#define OP_SortPut            52
#define OP_SortMakeRec        53
#define OP_SortMakeKey        54
#define OP_Sort               55
#define OP_SortNext           56
#define OP_SortCallback       57
#define OP_SortReset          58

#define OP_FileOpen           59
#define OP_FileRead           60
#define OP_FileColumn         61

#define OP_AggReset           62
#define OP_AggFocus           63
#define OP_AggNext            64
#define OP_AggSet             65
#define OP_AggGet             66
#define OP_AggFunc            67
#define OP_AggInit            68
#define OP_AggPush            69
#define OP_AggPop             70

#define OP_SetInsert          71
#define OP_SetFound           72
#define OP_SetNotFound        73
#define OP_SetFirst           74
#define OP_SetNext            75

#define OP_MakeRecord         76
#define OP_MakeKey            77
#define OP_MakeIdxKey         78
#define OP_IncrKey            79

#define OP_Goto               80
#define OP_If                 81
#define OP_IfNot              82
#define OP_Halt               83
#define OP_Gosub              84
#define OP_Return             85

#define OP_ColumnCount        86
#define OP_ColumnName         87
#define OP_Callback           88
#define OP_NullCallback       89

#define OP_Integer            90
#define OP_String             91
#define OP_Pop                92
#define OP_Dup                93
#define OP_Pull               94
#define OP_Push               95
#define OP_MustBeInt          96

#define OP_Add                97
#define OP_AddImm             98
#define OP_Subtract           99
#define OP_Multiply          100
#define OP_Divide            101
#define OP_Remainder         102
#define OP_BitAnd            103
#define OP_BitOr             104
#define OP_BitNot            105
#define OP_ShiftLeft         106
#define OP_ShiftRight        107
#define OP_AbsValue          108

/***** IMPORTANT NOTE: The code generator assumes that OP_XX+6==OP_StrXX *****/
#define OP_Eq                109
#define OP_Ne                110
#define OP_Lt                111
#define OP_Le                112
#define OP_Gt                113
#define OP_Ge                114
#define OP_StrEq             115
#define OP_StrNe             116
#define OP_StrLt             117
#define OP_StrLe             118
#define OP_StrGt             119
#define OP_StrGe             120
/***** IMPORTANT NOTE: the code generator assumes that OP_XX+6==OP_StrXX *****/

#define OP_IsNull            121
#define OP_NotNull           122
#define OP_Negative          123
#define OP_And               124
#define OP_Or                125
#define OP_Not               126
#define OP_Concat            127
#define OP_Noop              128
#define OP_Function          129

#define OP_MAX               129

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);

# 2002 February 26
#
# 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 VIEW statements.
#
# $Id: view.test,v 1.11 2002/08/25 19:20:43 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);

} {7 2 13 5 19 8 27 12}
do_test view-8.3 {
  execsql {
    CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
    SELECT * FROM v7 ORDER BY a;
  }
} {9 18 27 39}

do_test view-8.4 {
  execsql {
    CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
      (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
    SELECT * FROM v8;
  }
} 3
do_test view-8.5 {
  execsql {
    SELECT mx+10, mx*2 FROM v8;
  }
} {13 6}

finish_test