SQLite

Check-in [093d2b25f1]
Login

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

Overview
Comment:Modify new window functions function so that cursors are stepped immediately after each operation, instead of immediately before.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 093d2b25f1b656bba57d665aac68109436861b157a5769432b612176dae3f6eb
User & Date: dan 2019-03-07 19:26:17.600
Context
2019-03-07
20:47
Fix other "ROWS BETWEEN" cases on this branch. (check-in: a5f68f6647 user: dan tags: window-functions)
19:26
Modify new window functions function so that cursors are stepped immediately after each operation, instead of immediately before. (check-in: 093d2b25f1 user: dan tags: window-functions)
2019-03-06
21:04
Simplify the window function code generator some more. (check-in: 45cbd3b449 user: dan tags: window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/window.c.
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917

1918


1919

1920
1921
1922
1923
1924
1925
1926
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 

  if( regCountdown>0 ){
    addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  }

  if( jumpOnEof ){
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
    ret = sqlite3VdbeAddOp0(v, OP_Goto);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1);
  }

  switch( op ){
    case WINDOW_RETURN_ROW:
      windowAggFinal(p->pParse, p->pMWin, 0);
      windowReturnOneRow(p->pParse, p->pMWin, p->regGosub, p->addrGosub);
      break;

    case WINDOW_AGGINVERSE:
      windowAggStep(p->pParse, p->pMWin, csr, 1, p->regArg, p->pMWin->regSize);
      break;

    case WINDOW_AGGSTEP:
      windowAggStep(p->pParse, p->pMWin, csr, 0, p->regArg, p->pMWin->regSize);
      break;
  }

  if( ret ){

    sqlite3VdbeJumpHere(v, ret);


  }

  if( regCountdown>0 ){
    sqlite3VdbeJumpHere(v, addrIf);
  }
  return ret;
}









<
<
<
<
<
<
<















|
>
|
>
>

>







1888
1889
1890
1891
1892
1893
1894







1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 

  if( regCountdown>0 ){
    addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  }








  switch( op ){
    case WINDOW_RETURN_ROW:
      windowAggFinal(p->pParse, p->pMWin, 0);
      windowReturnOneRow(p->pParse, p->pMWin, p->regGosub, p->addrGosub);
      break;

    case WINDOW_AGGINVERSE:
      windowAggStep(p->pParse, p->pMWin, csr, 1, p->regArg, p->pMWin->regSize);
      break;

    case WINDOW_AGGSTEP:
      windowAggStep(p->pParse, p->pMWin, csr, 0, p->regArg, p->pMWin->regSize);
      break;
  }

  if( jumpOnEof ){
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
    ret = sqlite3VdbeAddOp0(v, OP_Goto);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1);
  }

  if( regCountdown>0 ){
    sqlite3VdbeJumpHere(v, addrIf);
  }
  return ret;
}


2020
2021
2022
2023
2024
2025
2026

2027
2028
2029
2030
2031
2032
2033
  int addrIf;
  int addrGosubFlush;
  int addrInteger;
  int addrCacheRewind;
  int addrCacheNext;

  int addrShortcut = 0;


  int bCache = windowCachePartition(pMWin);

  int reg = pParse->nMem+1;
  int regRecord = reg+nSub;
  int regRowid = regRecord+1;
  WindowCodeArg s;







>







2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
  int addrIf;
  int addrGosubFlush;
  int addrInteger;
  int addrCacheRewind;
  int addrCacheNext;

  int addrShortcut = 0;
  int addrEmpty = 0;

  int bCache = windowCachePartition(pMWin);

  int reg = pParse->nMem+1;
  int regRecord = reg+nSub;
  int regRowid = regRecord+1;
  WindowCodeArg s;
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154

2155
2156
2157
2158
2159
2160
2161
2162


2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193


2194
2195
2196
2197

2198



2199
2200
2201
2202







2203




2204
2205
2206
2207
2208
2209


2210
2211
2212
2213
2214
2215
2216
    addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);   sqlite3VdbeChangeP5(v, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1); sqlite3VdbeChangeP5(v, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1); sqlite3VdbeChangeP5(v, 1);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);

  /* Begin generating SECOND_ROW_CODE */
  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.SECOND_ROW_CODE"));
  if( bCache ){
    addrCacheNext = sqlite3VdbeCurrentAddr(v);
  }else{
    sqlite3VdbeJumpHere(v, addrIf);
  }
  if( pMWin->eStart==TK_FOLLOWING ){

    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  }else{


    int addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
    sqlite3VdbeJumpHere(v, addr);
  }
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));

  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.ALL_ROW_CODE"));
  sqlite3VdbeJumpHere(v, addrGoto);
  if( pMWin->eEnd!=TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
  }
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.ALL_ROW_CODE"));

  /* End of the main input loop */
  if( bCache ){
    sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheNext);
    sqlite3VdbeJumpHere(v, addrCacheRewind); 
  }else{
    if( addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
    sqlite3WhereEnd(pWInfo);
  }

  /* Fall through */

  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.FLUSH_CODE"));
  if( pMWin->pPartition && bCache==0 ){
    addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }



  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 1);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
  }else{

    int addrBreak;



    int addrStart = sqlite3VdbeCurrentAddr(v);
    if( pMWin->eStart==TK_FOLLOWING ){
      addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 1);
      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 1);







    }else{




      addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak);
  }



  if( bCache && addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regSize);
  if( bCache==0 ) sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.FLUSH_CODE"));
  if( pMWin->pPartition ){







|
|
|












>








>
>
|






|

<
<
<
<
<
<









<
<





>
>

|
|
|
>
|
>
>
>
|
<
|
|
>
>
>
>
>
>
>
|
>
>
>
>
|
|
<



>
>







2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172






2173
2174
2175
2176
2177
2178
2179
2180
2181


2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198

2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214

2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
    addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);

  /* Begin generating SECOND_ROW_CODE */
  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.SECOND_ROW_CODE"));
  if( bCache ){
    addrCacheNext = sqlite3VdbeCurrentAddr(v);
  }else{
    sqlite3VdbeJumpHere(v, addrIf);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  }else{
    int addr;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
    sqlite3VdbeJumpHere(v, addr);
  }
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));

  /* End of the main input loop */
  sqlite3VdbeJumpHere(v, addrGoto);






  if( bCache ){
    sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheNext);
    sqlite3VdbeJumpHere(v, addrCacheRewind); 
  }else{
    if( addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
    sqlite3WhereEnd(pWInfo);
  }

  /* Fall through */


  if( pMWin->pPartition && bCache==0 ){
    addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.FLUSH_CODE"));
  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    addrStart = sqlite3VdbeCurrentAddr(v);

    addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 1);
    addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 1);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak2);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak1);
    sqlite3VdbeJumpHere(v, addrBreak3);
  }else{
    int addrBreak;
    int addrStart;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);

    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak);
  }

  sqlite3VdbeJumpHere(v, addrEmpty);

  if( bCache && addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regSize);
  if( bCache==0 ) sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.FLUSH_CODE"));
  if( pMWin->pPartition ){
2677
2678
2679
2680
2681
2682
2683

2684
2685
2686
2687
2688
2689
2690
2691
2692
  ** windowCodeDefaultStep() is the only one of the three functions that
  ** does not cache each partition in a temp table before beginning to
  ** return rows.
  */
  if( pMWin->eType==TK_ROWS 
   && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
  ){

    if( (pMWin->eStart!=TK_PRECEDING && pMWin->eStart!=TK_FOLLOWING)
     || (pMWin->eEnd!=TK_FOLLOWING && pMWin->eEnd!=TK_PRECEDING) 
    ){
      VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()"));
      windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
      VdbeModuleComment((pParse->pVdbe, "End RowExprStep()"));
    }else{
      VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
      windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);







>
|
|







2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
  ** windowCodeDefaultStep() is the only one of the three functions that
  ** does not cache each partition in a temp table before beginning to
  ** return rows.
  */
  if( pMWin->eType==TK_ROWS 
   && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
  ){
    int bCache = windowCachePartition(pMWin);
    if( (pMWin->eEnd!=TK_FOLLOWING   && pMWin->eEnd!=TK_PRECEDING) 
     || (pMWin->eStart!=TK_FOLLOWING && pMWin->eStart!=TK_PRECEDING) 
    ){
      VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()"));
      windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
      VdbeModuleComment((pParse->pVdbe, "End RowExprStep()"));
    }else{
      VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
      windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);