/ Check-in [af0ea136]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Extend windowCodeStep() to handle any ROWS PRECEDING/FOLLOWING frame specification.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: af0ea1363548461b2aad8fd54ee3f2f616111dcae2d6480f5294da44c87a0a5d
User & Date: dan 2019-03-05 19:29:36
Wiki:window-functions
Context
2019-03-06
17:12
Improvements to the way built-in window functions are handled. check-in: e8eee566 user: dan tags: window-functions
2019-03-05
19:29
Extend windowCodeStep() to handle any ROWS PRECEDING/FOLLOWING frame specification. check-in: af0ea136 user: dan tags: window-functions
2019-03-04
21:08
Merge trunk changes into this branch. check-in: 9b4d561f user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c. 

1867
1868
1869
1870
1871
1872
1873

1874
1875
1876
1877
1878
1879
1880
1881
....
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936

1937
1938
1939
1940
1941
1942
1943




















1944
1945
1946
1947















1948












1949
1950
1951
1952
1953
1954
1955
1956
1957
1958

1959


1960
1961

1962

1963
1964
1965

1966
1967
1968
1969
1970



1971
1972
1973
1974


1975
1976
1977
1978
1979


1980
1981



















1982


1983
1984
1985
1986
1987
1988
1989
....
2461
2462
2463
2464
2465
2466
2467

2468


2469
2470
2471
2472
2473
2474
2475


  int addrGoto;
  int addrIf;
  int addrIfEnd;
  int addrIfStart;
  int addrGosubFlush;
  int addrInteger;

  int addrGoto2;

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

  pParse->nMem += 1 + nSub + 1;

................................................................................
  /* This block is run for the first row of each partition */
  addrIf = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
  if( pMWin->pPartition ){
    sqlite3VdbeAddOp3(v, OP_Copy, 
        reg+pMWin->nBufferCol, pMWin->regPart, pMWin->pPartition->nExpr-1
    );
  }
  sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);   sqlite3VdbeChangeP5(v, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1); sqlite3VdbeChangeP5(v, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);

  regArg = windowInitAccum(pParse, pMWin);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  windowCheckIntValue(pParse, regStart, 0);
  sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  windowCheckIntValue(pParse, regEnd, 1);




















  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);

  /* This block is run for the second and subsequent rows of each partition */
  sqlite3VdbeJumpHere(v, addrIf);















  sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1);












  addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
  windowAggFinal(pParse, pMWin, 0);
  sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1);
  windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
  sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
  windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
  sqlite3VdbeJumpHere(v, addrIfStart);
  sqlite3VdbeJumpHere(v, addrIfEnd);


  sqlite3VdbeJumpHere(v, addrGoto);


  windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0);


  /* End of the main input loop */

  sqlite3WhereEnd(pWInfo);

  /* Fall through */

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




  sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+2);
  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  windowAggFinal(pParse, pMWin, 0);
  windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);


  addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
  sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2);
  addrGoto2 = sqlite3VdbeAddOp0(v, OP_Goto);
  windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
  sqlite3VdbeJumpHere(v, addrIfStart);


  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrGoto-1);
  sqlite3VdbeJumpHere(v, addrGoto);



















  sqlite3VdbeJumpHere(v, addrGoto2);



  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  if( pMWin->pPartition ){
    sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  }
................................................................................
        || (pFunc->zName==leadName)
        || (pFunc->zName==lagName)
      ){
        bCache = 1;
        break;
      }
    }

    if( bCache || pMWin->eStart!=TK_PRECEDING || pMWin->eEnd!=TK_FOLLOWING ){


      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);
      VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));








>
|







 







<
<
<
>


<




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




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
|
|
>

>
>
|
|
>

>



>





>
>
>
|
|
|
|
>
>
|
|
|
|
|
>
>
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>







 







>
|
>
>








1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
....
1928
1929
1930
1931
1932
1933
1934



1935
1936
1937

1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
....
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557


  int addrGoto;
  int addrIf;
  int addrIfEnd;
  int addrIfStart;
  int addrGosubFlush;
  int addrInteger;

  int addrShortcut = 0;

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

  pParse->nMem += 1 + nSub + 1;

................................................................................
  /* This block is run for the first row of each partition */
  addrIf = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
  if( pMWin->pPartition ){
    sqlite3VdbeAddOp3(v, OP_Copy, 
        reg+pMWin->nBufferCol, pMWin->regPart, pMWin->pPartition->nExpr-1
    );
  }




  regArg = windowInitAccum(pParse, pMWin);


  sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  windowCheckIntValue(pParse, regStart, 0);
  sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  windowCheckIntValue(pParse, regEnd, 1);

  if( pMWin->eStart==TK_FOLLOWING || pMWin->eEnd==TK_PRECEDING ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    windowAggFinal(pParse, pMWin, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
    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);

  /* This block is run for the second and subsequent rows of each partition */
  sqlite3VdbeJumpHere(v, addrIf);

  if( pMWin->eStart==TK_FOLLOWING ){
    addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    windowAggFinal(pParse, pMWin, 0);
    sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    sqlite3VdbeJumpHere(v, addrIfEnd);

    addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
    sqlite3VdbeJumpHere(v, addrIfStart);
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0);
    sqlite3VdbeJumpHere(v, addrIfEnd);

    windowAggFinal(pParse, pMWin, 0);
    sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);

    addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
    sqlite3VdbeJumpHere(v, addrIfStart);
  }else{
    addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    windowAggFinal(pParse, pMWin, 0);
    sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
    sqlite3VdbeJumpHere(v, addrIfStart);
    sqlite3VdbeJumpHere(v, addrIfEnd);
  }

  sqlite3VdbeJumpHere(v, addrGoto);
  if( pMWin->eEnd!=TK_PRECEDING ){
    sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1);
    windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0);
  }

  /* End of the main input loop */
  if( addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
  sqlite3WhereEnd(pWInfo);

  /* Fall through */

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

  if( pMWin->eStart==TK_FOLLOWING ){
    int addrBreak;
    addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+2);
    addrBreak = sqlite3VdbeAddOp0(v, OP_Goto);
    windowAggFinal(pParse, pMWin, 0);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    sqlite3VdbeJumpHere(v, addrIfEnd);

    addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3VdbeAddOp0(v, OP_Goto);
    windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
    sqlite3VdbeJumpHere(v, addrIfStart);
    sqlite3VdbeJumpHere(v, addrIfStart+2);

    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrIfEnd);
    sqlite3VdbeJumpHere(v, addrBreak);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+2);
    addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
    if( pMWin->eEnd==TK_PRECEDING ){
      addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
      sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1);
      windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0);
      sqlite3VdbeJumpHere(v, addrIfEnd);
      windowAggFinal(pParse, pMWin, 0);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    }else{
      windowAggFinal(pParse, pMWin, 0);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
      addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
      sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
      windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
      sqlite3VdbeJumpHere(v, addrIfStart);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, addrGoto-1);
    }
    sqlite3VdbeJumpHere(v, addrGoto);
  }


  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  if( pMWin->pPartition ){
    sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  }
................................................................................
        || (pFunc->zName==leadName)
        || (pFunc->zName==lagName)
      ){
        bCache = 1;
        break;
      }
    }
    if( bCache 
    || (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);
      VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));

This page was generated in about 0.008s by Fossil 2.9 [5b6be64760] 2019-06-14 00:24:04