/ Check-in [c1abd2dd]
Login

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

Overview
Comment:Fix a problem with using LIMIT in window-function queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: c1abd2dda4154cb573bdf627cdef794d3697f874c3b70357aaf5e4ed95ad1d5c
User & Date: dan 2018-06-23 16:26:20
Context
2018-06-23
19:29
Fix problems with using window-functions in correlated sub-queries. check-in: 3e23cfc8 user: dan tags: exp-window-functions
16:26
Fix a problem with using LIMIT in window-function queries. check-in: c1abd2dd user: dan tags: exp-window-functions
07:59
Fix a problem with using a window-function SELECT as a FROM clause sub-query in some circumstances. check-in: 11d73339 user: dan tags: exp-window-functions
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/select.c.

5929
5930
5931
5932
5933
5934
5935

5936
5937
5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
5951
5952
5953
5954
    }

    assert( p->pEList==pEList );
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( pWin ){
      int addrGosub = sqlite3VdbeMakeLabel(v);
      int iCont = sqlite3VdbeMakeLabel(v);

      int regGosub = ++pParse->nMem;
      int addr = 0;

      sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);

      addr = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeResolveLabel(v, addrGosub);
      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, 0);
      sqlite3VdbeResolveLabel(v, iCont);
      sqlite3VdbeAddOp1(v, OP_Return, regGosub);
      sqlite3VdbeJumpHere(v, addr);

    }else
#endif /* SQLITE_OMIT_WINDOWFUNC */
    {
      /* Use the standard inner loop. */
      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
          sqlite3WhereContinueLabel(pWInfo),
          sqlite3WhereBreakLabel(pWInfo));







>

<



|

|


|
<







5929
5930
5931
5932
5933
5934
5935
5936
5937

5938
5939
5940
5941
5942
5943
5944
5945
5946

5947
5948
5949
5950
5951
5952
5953
    }

    assert( p->pEList==pEList );
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( pWin ){
      int addrGosub = sqlite3VdbeMakeLabel(v);
      int iCont = sqlite3VdbeMakeLabel(v);
      int iBreak = sqlite3VdbeMakeLabel(v);
      int regGosub = ++pParse->nMem;


      sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);

      sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
      sqlite3VdbeResolveLabel(v, addrGosub);
      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, iBreak);
      sqlite3VdbeResolveLabel(v, iCont);
      sqlite3VdbeAddOp1(v, OP_Return, regGosub);
      sqlite3VdbeResolveLabel(v, iBreak);

    }else
#endif /* SQLITE_OMIT_WINDOWFUNC */
    {
      /* Use the standard inner loop. */
      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
          sqlite3WhereContinueLabel(pWInfo),
          sqlite3WhereBreakLabel(pWInfo));

Changes to test/window1.test.

398
399
400
401
402
403
404
405






































406
407
} {
  Horace      East     1
  Charles     North   45
  Alice       North   34
  Elizabeth   South   99
  Grant       South   23
}







































finish_test









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


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
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
} {
  Horace      East     1
  Charles     North   45
  Alice       North   34
  Elizabeth   South   99
  Grant       South   23
}

do_execsql_test 10.2 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
} {
  Horace East       1  
  Brad North       22 
  Alice North      56 
  Charles North   101 
  Darrell South     8 
  Frank South      30 
  Grant South      53 
  Elizabeth South 152
}

do_execsql_test 10.3 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
  LIMIT 5
} {
  Horace East       1  
  Brad North       22 
  Alice North      56 
  Charles North   101 
  Darrell South     8 
}

do_execsql_test 10.4 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
  LIMIT 5 OFFSET 2
} {
  Alice North      56 
  Charles North   101 
  Darrell South     8 
  Frank South      30 
  Grant South      53 
}

finish_test