/ Check-in [179ef816]
Login

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

Overview
Comment:Fix the OFFSET clause so that it works correctly on queries that lack a FROM clause. Ticket [07d6a0453d4ed8].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:179ef81648b0ad557df78b7712f216b876b6fb65
User & Date: drh 2014-03-21 18:16:23
Context
2014-03-21
19:27
Avoid leaking memory in an obscure case where the flattener adds an ORDER BY clause to the recursive part of a recursive query. check-in: 1f413aca user: dan tags: trunk
18:45
Merge the OFFSET-on-query-without-FROM fix from trunk. check-in: 71e9ae72 user: drh tags: orderby-planning
18:16
Fix the OFFSET clause so that it works correctly on queries that lack a FROM clause. Ticket [07d6a0453d4ed8]. check-in: 179ef816 user: drh tags: trunk
2014-03-20
19:04
Remove a testcase() that is now always true due to the "x IN (?)" optimization. Add an ALWAYS() around a conditional in the parser that cannot be false. check-in: d5a1530b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
...
575
576
577
578
579
580
581

582
583
584
585
586
587
588
** Add code to implement the OFFSET
*/
static void codeOffset(
  Vdbe *v,          /* Generate code into this VM */
  int iOffset,      /* Register holding the offset counter */
  int iContinue     /* Jump here to skip the current record */
){
  if( iOffset>0 && iContinue!=0 ){
    int addr;
    sqlite3VdbeAddOp2(v, OP_AddImm, iOffset, -1);
    addr = sqlite3VdbeAddOp1(v, OP_IfNeg, iOffset); VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue);
    VdbeComment((v, "skip OFFSET records"));
    sqlite3VdbeJumpHere(v, addr);
  }
................................................................................
  int iParm = pDest->iSDParm; /* First argument to disposal method */
  int nResultCol;             /* Number of result columns */

  assert( v );
  assert( pEList!=0 );
  hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP;
  if( pOrderBy==0 && !hasDistinct ){

    codeOffset(v, p->iOffset, iContinue);
  }

  /* Pull the requested columns.
  */
  nResultCol = pEList->nExpr;








|







 







>







469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
...
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
** Add code to implement the OFFSET
*/
static void codeOffset(
  Vdbe *v,          /* Generate code into this VM */
  int iOffset,      /* Register holding the offset counter */
  int iContinue     /* Jump here to skip the current record */
){
  if( iOffset>0 ){
    int addr;
    sqlite3VdbeAddOp2(v, OP_AddImm, iOffset, -1);
    addr = sqlite3VdbeAddOp1(v, OP_IfNeg, iOffset); VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue);
    VdbeComment((v, "skip OFFSET records"));
    sqlite3VdbeJumpHere(v, addr);
  }
................................................................................
  int iParm = pDest->iSDParm; /* First argument to disposal method */
  int nResultCol;             /* Number of result columns */

  assert( v );
  assert( pEList!=0 );
  hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP;
  if( pOrderBy==0 && !hasDistinct ){
    assert( iContinue!=0 );
    codeOffset(v, p->iOffset, iContinue);
  }

  /* Pull the requested columns.
  */
  nResultCol = pEList->nExpr;

Changes to src/where.c.

43
44
45
46
47
48
49

50
51
52
53
54
55
56
....
5449
5450
5451
5452
5453
5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
}

/*
** Return the VDBE address or label to jump to in order to continue
** immediately with the next row of a WHERE clause.
*/
int sqlite3WhereContinueLabel(WhereInfo *pWInfo){

  return pWInfo->iContinue;
}

/*
** Return the VDBE address or label to jump to in order to break
** out of a WHERE loop.
*/
................................................................................
  }
  pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
  pWInfo->nLevel = nTabList;
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->pOrderBy = pOrderBy;
  pWInfo->pResultSet = pResultSet;
  pWInfo->iBreak = sqlite3VdbeMakeLabel(v);
  pWInfo->wctrlFlags = wctrlFlags;
  pWInfo->savedNQueryLoop = pParse->nQueryLoop;
  pMaskSet = &pWInfo->sMaskSet;
  sWLB.pWInfo = pWInfo;
  sWLB.pWC = &pWInfo->sWC;
  sWLB.pNew = (WhereLoop*)(((char*)pWInfo)+nByteWInfo);
  assert( EIGHT_BYTE_ALIGNMENT(sWLB.pNew) );







>







 







|







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
....
5450
5451
5452
5453
5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
5464
}

/*
** Return the VDBE address or label to jump to in order to continue
** immediately with the next row of a WHERE clause.
*/
int sqlite3WhereContinueLabel(WhereInfo *pWInfo){
  assert( pWInfo->iContinue!=0 );
  return pWInfo->iContinue;
}

/*
** Return the VDBE address or label to jump to in order to break
** out of a WHERE loop.
*/
................................................................................
  }
  pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
  pWInfo->nLevel = nTabList;
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->pOrderBy = pOrderBy;
  pWInfo->pResultSet = pResultSet;
  pWInfo->iBreak = pWInfo->iContinue = sqlite3VdbeMakeLabel(v);
  pWInfo->wctrlFlags = wctrlFlags;
  pWInfo->savedNQueryLoop = pParse->nQueryLoop;
  pMaskSet = &pWInfo->sMaskSet;
  sWLB.pWInfo = pWInfo;
  sWLB.pWC = &pWInfo->sWC;
  sWLB.pNew = (WhereLoop*)(((char*)pWInfo)+nByteWInfo);
  assert( EIGHT_BYTE_ALIGNMENT(sWLB.pNew) );

Changes to test/limit.test.

611
612
613
614
615
616
617
618




















619
} {32}
do_test limit-13.72 {
  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
} {32}
do_test limit-13.81 {
  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
} {}





















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
} {32}
do_test limit-13.72 {
  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
} {32}
do_test limit-13.81 {
  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
} {}

do_execsql_test limit-14.1 {
  SELECT 123 LIMIT 1 OFFSET 0
} {123}
do_execsql_test limit-14.2 {
  SELECT 123 LIMIT 1 OFFSET 1
} {}
do_execsql_test limit-14.3 {
  SELECT 123 LIMIT 0 OFFSET 0
} {}
do_execsql_test limit-14.4 {
  SELECT 123 LIMIT 0 OFFSET 1
} {}
do_execsql_test limit-14.6 {
  SELECT 123 LIMIT -1 OFFSET 0
} {123}
do_execsql_test limit-14.7 {
  SELECT 123 LIMIT -1 OFFSET 1
} {}


finish_test