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


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

  if( zType==0 ) return;
  for(i=0; i<nColumn; i++){
    zType[i] = sqliteExprType(pEList->a[i].pExpr)==SQLITE_SO_NUM ? 'n' : 't';
  }
  zType[i] = 0;
  sqliteVdbeChangeP3(v, -1, zType, P3_DYNAMIC);
}

/*
** 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 = sqliteVdbeCurrentAddr(v) + 2;
    if( nPop>0 ) addr++;
    sqliteVdbeAddOp(v, OP_MemIncr, p->iOffset, addr);
    if( nPop>0 ){
      sqliteVdbeAddOp(v, OP_Pop, nPop, 0);
    }
    sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
  }
  if( p->iLimit>=0 ){
    sqliteVdbeAddOp(v, OP_MemIncr, p->iLimit, iBreak);
  }
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** If srcTab and nColumn are both zero, then the pEList expressions
** are evaluated in order to get the data for this row.  If nColumn>0

  int eDest,              /* How to dispose of the results */
  int iParm,              /* An argument to the disposal method */
  int iContinue,          /* Jump here to continue with next row */
  int iBreak              /* Jump here to break out of the inner loop */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  int hasDistinct;        /* True if the DISTINCT keyword is present */

  if( v==0 ) return 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++){
      sqliteVdbeAddOp(v, OP_Column, srcTab, i);
    }
  }else{
    nColumn = pEList->nExpr;
    for(i=0; i<pEList->nExpr; i++){
      sqliteExprCode(pParse, pEList->a[i].pExpr);
    }
  }

  /* If the DISTINCT keyword was present on the SELECT statement
  ** and this row has been seen before, then do not make this row
  ** part of the result.
  */
  if( hasDistinct ){
#if NULL_ALWAYS_DISTINCT
    sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);
#endif
    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
    if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pEList);
    sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
    sqliteVdbeAddOp(v, OP_String, 0, 0);
    sqliteVdbeAddOp(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.
    */
    case SRT_Union: {

){
  int end1 = sqliteVdbeMakeLabel(v);
  int end2 = sqliteVdbeMakeLabel(v);
  int addr;
  if( eDest==SRT_Sorter ) return;
  sqliteVdbeAddOp(v, OP_Sort, 0, 0);
  addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end1);





  codeLimiter(v, p, addr, end2, 1);


  switch( eDest ){
    case SRT_Callback: {
      sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
      break;
    }
    case SRT_Table:
    case SRT_TempTable: {

#    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.11.2.1 2004/07/19 23:33:04 drh Exp $

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

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

} {31}
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