**    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.198 2004/07/19 23:16:39 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(
  Parse *pParse,    /* Parsing context */
  Select *p,        /* The SELECT statement being coded */
  int iContinue,    /* Jump here to skip the current record */
  int iBreak,       /* Jump here to end the loop */
  int nPop          /* Number of times to pop stack when jumping */
){
  Vdbe *v = pParse->pVdbe;
  if( p->iOffset>=0 ){
    int addr = sqlite3VdbeCurrentAddr(v) + 2;
    if( nPop>0 ) addr++;
    sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, addr);
    if( nPop>0 ){
      sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
    }
................................................................................
  assert( pEList!=0 );

  /* If there was a LIMIT clause on the SELECT statement, then do the check
  ** to see if this row should be output.
  */
  hasDistinct = distinct>=0 && pEList && pEList->nExpr>0;
  if( pOrderBy==0 && !hasDistinct ){
    codeLimiter(pParse, p, iContinue, iBreak, 0);
  }

  /* Pull the requested columns.
  */
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeAddOp(v, OP_Column, srcTab, i);
................................................................................
    sqlite3VdbeAddOp(v, OP_MakeRecord, pEList->nExpr * -1, 0);
    sqlite3VdbeAddOp(v, OP_Distinct, distinct, sqlite3VdbeCurrentAddr(v)+3);
    sqlite3VdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
    sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutStrKey, distinct, 0);
    if( pOrderBy==0 ){
      codeLimiter(pParse, p, iContinue, iBreak, nColumn);
    }
  }

  switch( eDest ){
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
................................................................................
    if( !pInfo->aColl[i] ){
      pInfo->aColl[i] = db->pDfltColl;
    }
    pInfo->aSortOrder[i] = pOrderBy->a[i].sortOrder;
  }
  sqlite3VdbeOp3(v, OP_Sort, 0, 0, (char*)pInfo, P3_KEYINFO_HANDOFF);
  addr = sqlite3VdbeAddOp(v, OP_SortNext, 0, end1);
  codeLimiter(pParse, p, addr, end2, 1);
  switch( eDest ){
    case SRT_Table:
    case SRT_TempTable: {
      sqlite3VdbeAddOp(v, OP_NewRecno, iParm, 0);
      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
      sqlite3VdbeAddOp(v, OP_PutIntKey, iParm, 0);
      break;

**    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.199 2004/07/19 23:38:11 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(
  Vdbe *v,          /* Generate code into this VM */
  Select *p,        /* The SELECT statement being coded */
  int iContinue,    /* Jump here to skip the current record */
  int iBreak,       /* Jump here to end the loop */
  int nPop          /* Number of times to pop stack when jumping */
){

  if( p->iOffset>=0 ){
    int addr = sqlite3VdbeCurrentAddr(v) + 2;
    if( nPop>0 ) addr++;
    sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, addr);
    if( nPop>0 ){
      sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
    }
................................................................................
  assert( pEList!=0 );

  /* If there was a LIMIT clause on the SELECT statement, then do the check
  ** to see if this row should be output.
  */
  hasDistinct = distinct>=0 && pEList && pEList->nExpr>0;
  if( pOrderBy==0 && !hasDistinct ){
    codeLimiter(v, p, iContinue, iBreak, 0);
  }

  /* Pull the requested columns.
  */
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeAddOp(v, OP_Column, srcTab, i);
................................................................................
    sqlite3VdbeAddOp(v, OP_MakeRecord, pEList->nExpr * -1, 0);
    sqlite3VdbeAddOp(v, OP_Distinct, distinct, sqlite3VdbeCurrentAddr(v)+3);
    sqlite3VdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
    sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);
    sqlite3VdbeAddOp(v, OP_PutStrKey, distinct, 0);
    if( pOrderBy==0 ){
      codeLimiter(v, p, iContinue, iBreak, nColumn);
    }
  }

  switch( eDest ){
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
................................................................................
    if( !pInfo->aColl[i] ){
      pInfo->aColl[i] = db->pDfltColl;
    }
    pInfo->aSortOrder[i] = pOrderBy->a[i].sortOrder;
  }
  sqlite3VdbeOp3(v, OP_Sort, 0, 0, (char*)pInfo, P3_KEYINFO_HANDOFF);
  addr = sqlite3VdbeAddOp(v, OP_SortNext, 0, end1);
  codeLimiter(v, p, addr, end2, 1);
  switch( eDest ){
    case SRT_Table:
    case SRT_TempTable: {
      sqlite3VdbeAddOp(v, OP_NewRecno, iParm, 0);
      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
      sqlite3VdbeAddOp(v, OP_PutIntKey, iParm, 0);
      break;

#    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.14 2004/07/19 23:16:39 drh Exp $

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

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

btree_breakpoint
do_test limit-4.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(x);
    INSERT INTO t3 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;
................................................................................
do_test limit-7.12 {
  execsql {
    SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 
       ORDER BY 1 DESC LIMIT 1 OFFSET 1;
  }
} {30}

# Tests for limit in conjunction with distinct

#
do_test limit-8.1 {
  execsql {
    SELECT DISTINCT x/100 FROM t3 LIMIT 5;
  }
} {0 1 2 3 4}
do_test limit-8.2 {
  execsql {
    SELECT DISTINCT x/100 FROM t3 LIMIT 5 OFFSET 5;
  }
} {5 6 7 8 9}






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 the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.15 2004/07/19 23:38:11 drh Exp $

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

# Build some test data
#
execsql {
................................................................................
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(x);
    INSERT INTO t3 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;
................................................................................
do_test limit-7.12 {
  execsql {
    SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 
       ORDER BY 1 DESC LIMIT 1 OFFSET 1;
  }
} {30}

# Tests for limit in conjunction with distinct.  The distinct should
# occur before both the limit and the offset.  Ticket #749.
#
do_test limit-8.1 {
  execsql {
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5;
  }
} {0 1 2 3 4}
do_test limit-8.2 {
  execsql {
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 5;
  }
} {5 6 7 8 9}
do_test limit-8.3 {
  execsql {
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 25;
  }
} {25 26 27 28 29}

finish_test