/ Check-in [9d4f43f0]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9d4f43f030438b3e7358032de2e04132b80e04a8
User & Date: drh 2004-07-19 23:38:11
Context
2004-07-20
00:20
Correct handling of views on tables with quoted column names. Ticket #756. (CVS 1826) check-in: 8ac5a8ad user: drh tags: trunk
2004-07-19
23:38
Refinements to the LIMIT patch (1823) for ticket #749. (CVS 1825) check-in: 9d4f43f0 user: drh tags: trunk
23:16
LIMIT occurs after DISTINCT. Ticket #749. (CVS 1823) check-in: e6bc8aa8 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
...
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
...
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
...
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
**    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;







|







 







|





<







 







|







 







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
320
321
322
323
324
325
326
327
328
329
330
331
332

333
334
335
336
337
338
339
...
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
...
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
**    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;

Changes to test/limit.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
...
291
292
293
294
295
296
297
298

299
300
301
302
303
304
305
306
307
308
309



310


311
#    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







|







 







<







 







|
>



|




|


>
>
>
|
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
86
87
88
89
90
91
92

93
94
95
96
97
98
99
...
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
#    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