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.96 2002/06/21 23:01:50 drh Exp $
*/
#include "sqliteInt.h"

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

  if( v==0 ) return 0;

  /* If there was a LIMIT clause on the SELECT statement, then do the check
  ** to see if this row should be output.
  */
  if( pOrderBy==0 ){
    if( p->nOffset>0 ){
      int addr = sqliteVdbeCurrentAddr(v);
      sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+2);
      sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
    }
    if( p->nLimit>=0 ){
      sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, iBreak);
    }
  }

  /* Pull the requested columns.
  */
  if( pEList ){
    for(i=0; i<pEList->nExpr; i++){

  int iParm        /* Optional parameter associated with eDest */
){
  int end = sqliteVdbeMakeLabel(v);
  int addr;
  sqliteVdbeAddOp(v, OP_Sort, 0, 0);
  addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end);
  if( p->nOffset>0 ){
    sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+4);
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
  }
  if( p->nLimit>=0 ){
    sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, end);
  }
  switch( eDest ){
    case SRT_Callback: {
      sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
      break;
    }
    case SRT_Table:

  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nSrc!=1 ) return 0;
  if( (pSub->isDistinct || pSub->nLimit>=0) &&  (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** i-th entry of the FROM clause in the outer query.
  */
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);

  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, pTabList, pEList);
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;
    p->nOffset = 0;
  }else{
    int iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 0);
    p->nLimit = iMem;
    if( p->nOffset<=0 ){
      p->nOffset = 0;
    }else{
      iMem = pParse->nMem++;
      sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
      sqliteVdbeAddOp(v, OP_MemStore, iMem, 0);
      p->nOffset = iMem;
    }
  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nSrc; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+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.128 2002/06/21 23:01:50 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

** The zSelect field is used when the Select structure must be persistent.
** Normally, the expression tree points to tokens in the original input
** string that encodes the select.  But if the Select structure must live
** longer than its input string (for example when it is used to describe
** a VIEW) we have to make a copy of the input string so that the nodes
** of the expression tree will have something to point to.  zSelect is used
** to hold that copy.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the
** limit and nOffset to the value of the offset (or 0 if there is not
** offset).  But later on, nLimit and nOffset become the memory locations
** in the VDBE that record the limit and offset counters.
*/
struct Select {
  int isDistinct;        /* True if the DISTINCT keyword is present */
  ExprList *pEList;      /* The fields of the result */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */

** 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.158 2002/06/21 23:01:50 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

  int nLineAlloc;     /* Number of spaces allocated for zLine */
  int nMem;           /* Number of memory locations currently allocated */
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */
  int nCallback;      /* Number of callbacks invoked so far */


  int keylistStackDepth;  /* The size of the "keylist" stack */
  Keylist **keylistStack; /* The stack used by opcodes ListPush & ListPop */
};

/*
** Create a new virtual database engine.
*/

  "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",              "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.

  **
  ** Allocation all the stack space we will ever need.
  */
  NeedStack(p, p->nOp);
  zStack = p->zStack;
  aStack = p->aStack;
  p->tos = -1;



  /* Initialize the aggregrate hash table.
  */
  sqliteHashInit(&p->agg.hash, SQLITE_HASH_BINARY, 0);
  p->agg.pSearch = 0;

  rc = SQLITE_OK;

    zStack[tos] = z;
    aStack[tos].n = strlen(z) + 1;
    aStack[tos].flags = STK_Str | STK_Dyn;
  }
  sqliteFree(aRoot);
  break;
}

















































/* Opcode: ListWrite * * *
**
** Write the integer on the top of the stack
** into the temporary storage list.
*/
case OP_ListWrite: {

  if( aStack[tos].flags & STK_Str ){
    zStack[tos] = p->aMem[i].z;
    aStack[tos].flags |= STK_Static;
    aStack[tos].flags &= ~STK_Dyn;
  }
  break;
}

/* Opcode: MemIncr P1 P2 *
**
** Increment the integer valued memory cell P1 by 1.  If P2 is not zero
** and the result after the increment is greater than zero, then jump
** to P2.
**
** This instruction throws an error if the memory cell is not initially
** an integer.
*/
case OP_MemIncr: {
  int i = pOp->p1;
  Mem *pMem;
  VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; )
  pMem = &p->aMem[i];
  VERIFY( if( pMem->s.flags != STK_Int ) goto bad_instruction; )
  pMem->s.i++;
  if( pOp->p2>0 && pMem->s.i>0 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: AggReset * P2 *
**
** Reset the aggregator so that it no longer contains any data.
** Future aggregator elements will contain P2 values each.
*/
case OP_AggReset: {

*************************************************************************
** 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.57 2002/06/21 23:01:50 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_IdxDelete          38
#define OP_IdxRecno           39
#define OP_IdxGT              40
#define OP_IdxGE              41

#define OP_MemLoad            42
#define OP_MemStore           43
#define OP_MemIncr            44

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

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

#define OP_FileOpen           58
#define OP_FileRead           59
#define OP_FileColumn         60

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

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

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

#define OP_Goto               79
#define OP_If                 80
#define OP_IfNot              81
#define OP_Halt               82

#define OP_ColumnCount        83
#define OP_ColumnName         84
#define OP_Callback           85
#define OP_NullCallback       86

#define OP_Integer            87
#define OP_String             88
#define OP_Pop                89
#define OP_Dup                90
#define OP_Pull               91
#define OP_Push               92
#define OP_MustBeInt          93

#define OP_Add                94
#define OP_AddImm             95
#define OP_Subtract           96
#define OP_Multiply           97
#define OP_Divide             98
#define OP_Remainder          99
#define OP_BitAnd            100
#define OP_BitOr             101
#define OP_BitNot            102
#define OP_ShiftLeft         103
#define OP_ShiftRight        104
#define OP_AbsValue          105

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

#define OP_IsNull            118
#define OP_NotNull           119
#define OP_Negative          120
#define OP_And               121
#define OP_Or                122
#define OP_Not               123
#define OP_Concat            124
#define OP_Noop              125
#define OP_Function          126





#define OP_MAX               126

/*
** 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*);

#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.4 2002/06/21 23:01:51 drh Exp $

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

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

} 2
do_test limit-2.3 {
  execsql {
    SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
  }
} 2

do_test limit-3.1 {
  execsql {
    SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
    ORDER BY z LIMIT 5;
  }
} {50 51 52 53 54}

do_test limit-4.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3 AS SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    END;
    SELECT count(*) FROM t3;
  }
} {10240}
do_test limit-4.2 {
  execsql {
    SELECT x FROM t3 LIMIT 2 OFFSET 10000
  }
} {10001 10002}
do_test limit-4.3 {
  execsql {
    CREATE TABLE t4 AS SELECT x,
       'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
       'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
       'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
       'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
       'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
    FROM t3 LIMIT 1000;
    SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
  }
} {1}

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.9 2002/06/21 23:01:51 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#

    CREATE TABLE t1(a unique not null, b unique not null);
    INSERT INTO t1 VALUES('a',12345678901234567890);
    INSERT INTO t1 VALUES('b',12345678911234567890);
    INSERT INTO t1 VALUES('c',12345678921234567890);
    SELECT * FROM t1;
  }
} {0 {a 12345678901234567890 b 12345678911234567890 c 12345678921234567890}}


finish_test