/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5929   5929       }
  5930   5930   
  5931   5931       assert( p->pEList==pEList );
  5932   5932   #ifndef SQLITE_OMIT_WINDOWFUNC
  5933   5933       if( pWin ){
  5934   5934         int addrGosub = sqlite3VdbeMakeLabel(v);
  5935   5935         int iCont = sqlite3VdbeMakeLabel(v);
         5936  +      int iBreak = sqlite3VdbeMakeLabel(v);
  5936   5937         int regGosub = ++pParse->nMem;
  5937         -      int addr = 0;
  5938   5938   
  5939   5939         sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
  5940   5940   
  5941         -      addr = sqlite3VdbeAddOp0(v, OP_Goto);
         5941  +      sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
  5942   5942         sqlite3VdbeResolveLabel(v, addrGosub);
  5943         -      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, 0);
         5943  +      selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, iBreak);
  5944   5944         sqlite3VdbeResolveLabel(v, iCont);
  5945   5945         sqlite3VdbeAddOp1(v, OP_Return, regGosub);
  5946         -      sqlite3VdbeJumpHere(v, addr);
  5947         -
         5946  +      sqlite3VdbeResolveLabel(v, iBreak);
  5948   5947       }else
  5949   5948   #endif /* SQLITE_OMIT_WINDOWFUNC */
  5950   5949       {
  5951   5950         /* Use the standard inner loop. */
  5952   5951         selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
  5953   5952             sqlite3WhereContinueLabel(pWInfo),
  5954   5953             sqlite3WhereBreakLabel(pWInfo));

Changes to test/window1.test.

   398    398   } {
   399    399     Horace      East     1
   400    400     Charles     North   45
   401    401     Alice       North   34
   402    402     Elizabeth   South   99
   403    403     Grant       South   23
   404    404   }
          405  +
          406  +do_execsql_test 10.2 {
          407  +  SELECT emp, region, sum(total) OVER win FROM sales
          408  +  WINDOW win AS (PARTITION BY region ORDER BY total)
          409  +} {
          410  +  Horace East       1  
          411  +  Brad North       22 
          412  +  Alice North      56 
          413  +  Charles North   101 
          414  +  Darrell South     8 
          415  +  Frank South      30 
          416  +  Grant South      53 
          417  +  Elizabeth South 152
          418  +}
          419  +
          420  +do_execsql_test 10.3 {
          421  +  SELECT emp, region, sum(total) OVER win FROM sales
          422  +  WINDOW win AS (PARTITION BY region ORDER BY total)
          423  +  LIMIT 5
          424  +} {
          425  +  Horace East       1  
          426  +  Brad North       22 
          427  +  Alice North      56 
          428  +  Charles North   101 
          429  +  Darrell South     8 
          430  +}
          431  +
          432  +do_execsql_test 10.4 {
          433  +  SELECT emp, region, sum(total) OVER win FROM sales
          434  +  WINDOW win AS (PARTITION BY region ORDER BY total)
          435  +  LIMIT 5 OFFSET 2
          436  +} {
          437  +  Alice North      56 
          438  +  Charles North   101 
          439  +  Darrell South     8 
          440  +  Frank South      30 
          441  +  Grant South      53 
          442  +}
   405    443   
   406    444   finish_test
   407    445