Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | LIMIT occurs after DISTINCT. Ticket #749. (CVS 1823) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e6bc8aa80824a9156e78fc99b5ac7045 |
User & Date: | drh 2004-07-19 23:16:39.000 |
Context
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) | |
22:28 | more uses of sqlite3.pc (CVS 1822) (check-in: a0f107ca66 user: dougcurrie tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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. |
︙ | ︙ | |||
315 316 317 318 319 320 321 322 323 324 325 326 327 328 | int i; for(i=0; i<pOrderBy->nExpr; i++){ sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr); } sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr, 0); sqlite3VdbeAddOp(v, OP_SortPut, 0, 0); } /* ** 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 | > > > > > > > > > > > > > > > > > > > > > > > > > | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | int i; for(i=0; i<pOrderBy->nExpr; i++){ sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr); } sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr, 0); 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); } sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue); } if( p->iLimit>=0 ){ sqlite3VdbeAddOp(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 |
︙ | ︙ | |||
341 342 343 344 345 346 347 348 349 350 351 352 353 354 | 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 */ char *aff /* affinity string if eDest is SRT_Union */ ){ Vdbe *v = pParse->pVdbe; int i; 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. */ | > > | < < < | < < < < | > > > | 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 | 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 */ char *aff /* affinity string if eDest is SRT_Union */ ){ 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(pParse, p, iContinue, iBreak, 0); } /* Pull the requested columns. */ if( nColumn>0 ){ for(i=0; i<nColumn; i++){ sqlite3VdbeAddOp(v, OP_Column, srcTab, i); } }else{ nColumn = pEList->nExpr; for(i=0; i<pEList->nExpr; i++){ sqlite3ExprCode(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 sqlite3VdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqlite3VdbeCurrentAddr(v)+7); #endif /* Deliberately leave the affinity string off of the following ** OP_MakeRecord */ 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. */ case SRT_Union: { |
︙ | ︙ | |||
555 556 557 558 559 560 561 | 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); | < < < < < | < < | 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; |
︙ | ︙ |
Changes to test/limit.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # 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. # | | | 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 { |
︙ | ︙ | |||
290 291 292 293 294 295 296 297 298 | } {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} finish_test | > > > > > > > > > > > > > | 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | } {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 # 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 |