SQLite

Check-in [9d4f43f030]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Refinements to the LIMIT patch (1823) for ticket #749. (CVS 1825)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9d4f43f030438b3e7358032de2e04132b80e04a8
User & Date: drh 2004-07-19 23:38:11.000
Context
2004-07-20
00:20
Correct handling of views on tables with quoted column names. Ticket #756. (CVS 1826) (check-in: 8ac5a8ad5e user: drh tags: trunk)
2004-07-19
23:38
Refinements to the LIMIT patch (1823) for ticket #749. (CVS 1825) (check-in: 9d4f43f030 user: drh tags: trunk)
23:16
LIMIT occurs after DISTINCT. Ticket #749. (CVS 1823) (check-in: e6bc8aa808 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
  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);
    }







|





<







320
321
322
323
324
325
326
327
328
329
330
331
332

333
334
335
336
337
338
339
  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);
    }
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
  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);







|







375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
  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);
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
    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.
    */







|







408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
    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.
    */
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
    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;







|







577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
    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;
Changes to test/limit.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 {
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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;







<







86
87
88
89
90
91
92

93
94
95
96
97
98
99
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;
291
292
293
294
295
296
297
298

299
300
301
302
303
304
305
306
307
308
309



310


311
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







|
>



|




|


>
>
>
|
>
>

290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
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