SQLite

Check-in [af0ea13635]
Login

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
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: af0ea1363548461b2aad8fd54ee3f2f616111dcae2d6480f5294da44c87a0a5d
User & Date: dan 2019-03-05 19:29:36.660
Context
2019-03-06
17:12
Improvements to the way built-in window functions are handled. (check-in: e8eee566df user: dan tags: window-functions)
2019-03-05
19:29
Extend windowCodeStep() to handle any ROWS PRECEDING/FOLLOWING frame specification. (check-in: af0ea13635 user: dan tags: window-functions)
2019-03-04
21:08
Merge trunk changes into this branch. (check-in: 9b4d561f68 user: dan tags: window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/window.c.
︙︙
1867
1868
1869
1870
1871
1872
1873

1874
1875
1876
1877
1878
1879
1880
1881


  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;









>
|








1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882


  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;
︙︙
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

  /* 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);
  }








|
<
<


<




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




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

>
>
|
>


>



>





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








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

  /* 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);
  }
︙︙
2461
2462
2463
2464
2465
2466
2467

2468


2469
2470
2471
2472
2473
2474
2475

        || (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()"));








>
|
>
>








2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557

        || (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()"));
︙︙