/ Check-in [ffc32b24]
Login

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

Overview
Comment:Add support for RANGE window frames. Some cases still do not work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: ffc32b246d92d53c66094afe11950b53ffab6a1c230c602eebbfedafb2eb57f4
User & Date: dan 2019-03-09 20:49:17
Wiki:window-functions
Context
2019-03-11
11:12
Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. check-in: e7bced73 user: dan tags: window-functions
2019-03-09
20:49
Add support for RANGE window frames. Some cases still do not work. check-in: ffc32b24 user: dan tags: window-functions
07:38
Merge latest trunk changes into this branch. check-in: 53ea550c user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

555
556
557
558
559
560
561








562
563
564
565
566
567
568
...
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
...
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
....
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
....
1534
1535
1536
1537
1538
1539
1540

































1541
1542
1543
1544
1545
1546
1547
....
1550
1551
1552
1553
1554
1555
1556



1557
1558
1559
1560
1561
1562
1563
1564
1565































1566

1567
1568
1569
1570
1571
1572
1573
....
1606
1607
1608
1609
1610
1611
1612




1613
1614
1615
1616
1617
1618
1619
....
1719
1720
1721
1722
1723
1724
1725







1726
1727
1728
1729
1730
1731
1732
....
1741
1742
1743
1744
1745
1746
1747
1748
1749

1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
....
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
....
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
....
1884
1885
1886
1887
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
....
1934
1935
1936
1937
1938
1939
1940





1941
1942
1943
1944
1945
1946
1947
    pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
    pWin->eStart = p->eStart;
    pWin->eEnd = p->eEnd;
    pWin->eType = p->eType;
  }else{
    sqlite3WindowChain(pParse, pWin, pList);
  }








  if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
    sqlite3 *db = pParse->db;
    if( pWin->pFilter ){
      sqlite3ErrorMsg(pParse, 
          "FILTER clause may only be used with aggregate window functions"
      );
    }else
................................................................................
  assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );

  if( eType==0 ){
    bImplicitFrame = 1;
    eType = TK_RANGE;
  }

  /* If a frame is declared "RANGE" (not "ROWS"), then it may not use
  ** either "<expr> PRECEDING" or "<expr> FOLLOWING".
  */
  if( eType==TK_RANGE && (pStart!=0 || pEnd!=0) ){
    sqlite3ErrorMsg(pParse, "RANGE must use only UNBOUNDED or CURRENT ROW");
    goto windowAllocErr;
  }

  /* Additionally, the
  ** starting boundary type may not occur earlier in the following list than
  ** the ending boundary type:
  **
  **   UNBOUNDED PRECEDING
  **   <expr> PRECEDING
  **   CURRENT ROW
................................................................................
  ** The parser ensures that "UNBOUNDED PRECEDING" cannot be used as an ending
  ** boundary, and than "UNBOUNDED FOLLOWING" cannot be used as a starting
  ** frame boundary.
  */
  if( (eStart==TK_CURRENT && eEnd==TK_PRECEDING)
   || (eStart==TK_FOLLOWING && (eEnd==TK_PRECEDING || eEnd==TK_CURRENT))
  ){
    sqlite3ErrorMsg(pParse, "unsupported frame delimiter for ROWS");
    goto windowAllocErr;
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
................................................................................
  if( pOrderBy ){
    int nVal = pOrderBy->nExpr;
    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
    sqlite3VdbeAddOp3(v, OP_Compare, regOld, regNew, nVal);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    addr = sqlite3VdbeAddOp3(
        v, OP_Jump, sqlite3VdbeCurrentAddr(v)+1, 0, sqlite3VdbeCurrentAddr(v)+1
        );
    VdbeCoverageEqNe(v);
    sqlite3VdbeAddOp3(v, OP_Copy, regNew, regOld, nVal-1);
  }else{
    addr = sqlite3VdbeAddOp0(v, OP_Goto);
  }
  return addr;
}
................................................................................
    int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
    int i;
    for(i=0; i<pOrderBy->nExpr; i++){
      sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i);
    }
  }
}


































static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int regCountdown,
 int jumpOnEof
){
................................................................................
  Window *pMWin = p->pMWin;
  int ret = 0;
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 
  int addrContinue = 0;
  int addrGoto = 0;
  int bPeer = (pMWin->eType!=TK_ROWS);




  /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
  ** starts with UNBOUNDED PRECEDING. */
  if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
    assert( regCountdown==0 && jumpOnEof==0 );
    return 0;
  }

  if( regCountdown>0 ){































    addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);

  }

  if( op==WINDOW_RETURN_ROW ){
    windowAggFinal(pParse, pMWin, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);
  switch( op ){
................................................................................
    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    addr = windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg);
    sqlite3VdbeChangeP2(v, addr, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }





  if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
  if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  return ret;
}

/*
** This function - windowCodeStep() - generates the VM code that reads data
................................................................................

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








  memset(&s, 0, sizeof(WindowCodeArg));
  s.pParse = pParse;
  s.pMWin = pMWin;
  s.pVdbe = v;
  s.regGosub = regGosub;
  s.addrGosub = addrGosub;
................................................................................
  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
    regStart = ++pParse->nMem;
  }
  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
    regEnd = ++pParse->nMem;
  }

  /* If this is not a "ROWS BETWEEN ..." frame, then allocate registers to
  ** store a copy of the current ORDER BY expressions. */

  if( pMWin->eType!=TK_ROWS ){
    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
    regNewPeer = reg + pMWin->nBufferCol;
    if( pMWin->pPartition ) regNewPeer += pMWin->pPartition->nExpr;

    regPeer = pParse->nMem+1;       pParse->nMem += nPeer;
    s.start.reg = pParse->nMem+1;   pParse->nMem += nPeer;
    s.current.reg = pParse->nMem+1; pParse->nMem += nPeer;
    s.end.reg = pParse->nMem+1;     pParse->nMem += nPeer;
  }

  assert( pMWin->eStart==TK_PRECEDING 
       || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING 
       || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING 
       || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED 
       || pMWin->eEnd==TK_PRECEDING 
  );


  /* Load the column values for the row returned by the sub-select
  ** into an array of registers starting at reg. Assemble them into
  ** a record in register regRecord. TODO: An optimization here? */
  for(iCol=0; iCol<nSub; iCol++){
    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, iCol, reg+iCol);
  }
................................................................................
  if( pMWin->pPartition ){
    int addr;
    ExprList *pPart = pMWin->pPartition;
    int nPart = pPart->nExpr;
    int regNewPart = reg + pMWin->nBufferCol;
    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);

    addrIf = sqlite3VdbeAddOp1(v, OP_If, pMWin->regFirst);
    addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
    VdbeCoverageEqNe(v);
    addrGosubFlush = sqlite3VdbeAddOp1(v, OP_Gosub, regFlushPart);
    VdbeComment((v, "call flush_partition"));
    sqlite3VdbeJumpHere(v, addrIf);
    sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
  }

  /* Insert the new row into the ephemeral table */
  sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
  sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regSize, 1);
................................................................................
      sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
      sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    }
    addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && regEnd ){
    assert( pMWin->eEnd==TK_FOLLOWING );
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  if( pMWin->eStart!=TK_UNBOUNDED ){
    sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
  }
................................................................................
  }
  if( regPeer ){
    addrPeerJump = windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){









      windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);

    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  }else{
    int addr;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){














      if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
      windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
      if( regEnd ) sqlite3VdbeJumpHere(v, addr);

    }
  }
  if( addrPeerJump ){
    sqlite3VdbeJumpHere(v, addrPeerJump);
  }
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));

................................................................................
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);





    if( pMWin->eEnd==TK_UNBOUNDED ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
    }else{
      assert( pMWin->eEnd==TK_FOLLOWING );
      addrStart = sqlite3VdbeCurrentAddr(v);







>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<







 







|







 







|







 







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







 







>
>
>









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







 







>
>
>
>







 







>
>
>
>
>
>
>







 







|
|
>










<
<
<
<
<
<
<
<
<
<
<
<







 







<






<







 







|







 







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










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







 







>
>
>
>
>







555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
...
931
932
933
934
935
936
937








938
939
940
941
942
943
944
...
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
....
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
....
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
....
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
....
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
....
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
....
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839












1840
1841
1842
1843
1844
1845
1846
....
1855
1856
1857
1858
1859
1860
1861

1862
1863
1864
1865
1866
1867

1868
1869
1870
1871
1872
1873
1874
....
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
....
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
....
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
    pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
    pWin->eStart = p->eStart;
    pWin->eEnd = p->eEnd;
    pWin->eType = p->eType;
  }else{
    sqlite3WindowChain(pParse, pWin, pList);
  }
  if( (pWin->eType==TK_RANGE)
   && (pWin->pStart || pWin->pEnd) 
   && (pWin->pOrderBy==0 || pWin->pOrderBy->nExpr!=1)
  ){
    sqlite3ErrorMsg(pParse, 
      "RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression"
    );
  }else
  if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
    sqlite3 *db = pParse->db;
    if( pWin->pFilter ){
      sqlite3ErrorMsg(pParse, 
          "FILTER clause may only be used with aggregate window functions"
      );
    }else
................................................................................
  assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );

  if( eType==0 ){
    bImplicitFrame = 1;
    eType = TK_RANGE;
  }









  /* Additionally, the
  ** starting boundary type may not occur earlier in the following list than
  ** the ending boundary type:
  **
  **   UNBOUNDED PRECEDING
  **   <expr> PRECEDING
  **   CURRENT ROW
................................................................................
  ** The parser ensures that "UNBOUNDED PRECEDING" cannot be used as an ending
  ** boundary, and than "UNBOUNDED FOLLOWING" cannot be used as a starting
  ** frame boundary.
  */
  if( (eStart==TK_CURRENT && eEnd==TK_PRECEDING)
   || (eStart==TK_FOLLOWING && (eEnd==TK_PRECEDING || eEnd==TK_CURRENT))
  ){
    sqlite3ErrorMsg(pParse, "unsupported frame specification");
    goto windowAllocErr;
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
................................................................................
  if( pOrderBy ){
    int nVal = pOrderBy->nExpr;
    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
    sqlite3VdbeAddOp3(v, OP_Compare, regOld, regNew, nVal);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    addr = sqlite3VdbeAddOp3(
        v, OP_Jump, sqlite3VdbeCurrentAddr(v)+1, 0, sqlite3VdbeCurrentAddr(v)+1
    );
    VdbeCoverageEqNe(v);
    sqlite3VdbeAddOp3(v, OP_Copy, regNew, regOld, nVal-1);
  }else{
    addr = sqlite3VdbeAddOp0(v, OP_Goto);
  }
  return addr;
}
................................................................................
    int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
    int i;
    for(i=0; i<pOrderBy->nExpr; i++){
      sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i);
    }
  }
}

/*
** This function is called as part of generating VM programs for RANGE
** offset PRECEDING/FOLLOWING frame boundaries. It generates code equivalent
** to:
**
**   if( csr1.peerVal + regVal >= csr2.peerVal ) goto lbl;
**   if( csr1.rowid >= csr2.rowid ) goto lbl;
*/
static void windowCodeRangeTest(
  WindowCodeArg *p, 
  int op,                          /* OP_Ge or OP_Gt */
  int csr1, 
  int regVal, 
  int csr2,
  int lbl
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int reg1 = sqlite3GetTempReg(pParse);
  int reg2 = sqlite3GetTempReg(pParse);
  windowReadPeerValues(p, csr1, reg1);
  windowReadPeerValues(p, csr2, reg2);
  sqlite3VdbeAddOp3(v, OP_Add, reg1, regVal, reg1);
  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
  sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);

  assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
}

static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int regCountdown,
 int jumpOnEof
){
................................................................................
  Window *pMWin = p->pMWin;
  int ret = 0;
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 
  int addrContinue = 0;
  int addrGoto = 0;
  int bPeer = (pMWin->eType!=TK_ROWS);

  int lblDone = sqlite3VdbeMakeLabel(pParse);
  int addrNextRange = 0;

  /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
  ** starts with UNBOUNDED PRECEDING. */
  if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
    assert( regCountdown==0 && jumpOnEof==0 );
    return 0;
  }

  if( regCountdown>0 ){
    if( pMWin->eType==TK_RANGE ){
      addrNextRange = sqlite3VdbeCurrentAddr(v);

      switch( op ){
        case WINDOW_RETURN_ROW: {
          assert( 0 );
          break;
        }

        case WINDOW_AGGINVERSE: {
          if( pMWin->eStart==TK_FOLLOWING ){
            windowCodeRangeTest(
                p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
            );
          }else{
            windowCodeRangeTest(
                p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
            );
          }
          break;
        }

        case WINDOW_AGGSTEP: {
          windowCodeRangeTest(
            p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone
          );
          break;
        }
      }

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

  if( op==WINDOW_RETURN_ROW ){
    windowAggFinal(pParse, pMWin, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);
  switch( op ){
................................................................................
    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    addr = windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg);
    sqlite3VdbeChangeP2(v, addr, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }

  if( addrNextRange ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNextRange);
  }
  sqlite3VdbeResolveLabel(v, lblDone);
  if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
  if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  return ret;
}

/*
** This function - windowCodeStep() - generates the VM code that reads data
................................................................................

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

  assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED || pMWin->eEnd==TK_PRECEDING 
  );

  memset(&s, 0, sizeof(WindowCodeArg));
  s.pParse = pParse;
  s.pMWin = pMWin;
  s.pVdbe = v;
  s.regGosub = regGosub;
  s.addrGosub = addrGosub;
................................................................................
  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
    regStart = ++pParse->nMem;
  }
  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
    regEnd = ++pParse->nMem;
  }

  /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of
  ** registers to store a copies of the ORDER BY expressions for the
  ** main loop, and for each cursor (start, current and end). */
  if( pMWin->eType!=TK_ROWS ){
    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
    regNewPeer = reg + pMWin->nBufferCol;
    if( pMWin->pPartition ) regNewPeer += pMWin->pPartition->nExpr;

    regPeer = pParse->nMem+1;       pParse->nMem += nPeer;
    s.start.reg = pParse->nMem+1;   pParse->nMem += nPeer;
    s.current.reg = pParse->nMem+1; pParse->nMem += nPeer;
    s.end.reg = pParse->nMem+1;     pParse->nMem += nPeer;
  }













  /* Load the column values for the row returned by the sub-select
  ** into an array of registers starting at reg. Assemble them into
  ** a record in register regRecord. TODO: An optimization here? */
  for(iCol=0; iCol<nSub; iCol++){
    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, iCol, reg+iCol);
  }
................................................................................
  if( pMWin->pPartition ){
    int addr;
    ExprList *pPart = pMWin->pPartition;
    int nPart = pPart->nExpr;
    int regNewPart = reg + pMWin->nBufferCol;
    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);


    addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
    VdbeCoverageEqNe(v);
    addrGosubFlush = sqlite3VdbeAddOp1(v, OP_Gosub, regFlushPart);
    VdbeComment((v, "call flush_partition"));

    sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
  }

  /* Insert the new row into the ephemeral table */
  sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
  sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regSize, 1);
................................................................................
      sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
      sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    }
    addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && pMWin->eType!=TK_RANGE && regEnd ){
    assert( pMWin->eEnd==TK_FOLLOWING );
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  if( pMWin->eStart!=TK_UNBOUNDED ){
    sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
  }
................................................................................
  }
  if( regPeer ){
    addrPeerJump = windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eType==TK_RANGE ){
        int lbl = sqlite3VdbeMakeLabel(pParse);
        int addrNext = sqlite3VdbeCurrentAddr(v);
        windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
        sqlite3VdbeResolveLabel(v, lbl);
      }else{
        windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
      }
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  }else{
    int addr;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eType==TK_RANGE ){
        int lbl;
        addr = sqlite3VdbeCurrentAddr(v);
        if( regEnd ){
          lbl = sqlite3VdbeMakeLabel(pParse);
          windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
        }
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        if( regEnd ){
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          sqlite3VdbeResolveLabel(v, lbl);
        }
      }else{
        if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        if( regEnd ) sqlite3VdbeJumpHere(v, addr);
      }
    }
  }
  if( addrPeerJump ){
    sqlite3VdbeJumpHere(v, addrPeerJump);
  }
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));

................................................................................
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eType==TK_RANGE ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    }else
    if( pMWin->eEnd==TK_UNBOUNDED ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
    }else{
      assert( pMWin->eEnd==TK_FOLLOWING );
      addrStart = sqlite3VdbeCurrentAddr(v);

Changes to test/pg_common.tcl.

68
69
70
71
72
73
74

75
76
77
78
79
80
81
82
}

proc errorsql_test {tn sql} {
  set rc [catch {execsql $sql} msg]
  if {$rc==0} {
    error "errorsql_test SQL did not cause an error!"
  }

  puts $::fd "# PG says \"[string trim $msg]\""
  set sql [string map {string_agg group_concat} $sql]
  puts $::fd "do_test $tn { catch { execsql {"
  puts $::fd "  [string trim $sql]"
  puts $::fd "} } } 1"
  puts $::fd ""
}








>
|







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
}

proc errorsql_test {tn sql} {
  set rc [catch {execsql $sql} msg]
  if {$rc==0} {
    error "errorsql_test SQL did not cause an error!"
  }
  set msg [lindex [split [string trim $msg] "\n"] 0]
  puts $::fd "# PG says $msg"
  set sql [string map {string_agg group_concat} $sql]
  puts $::fd "do_test $tn { catch { execsql {"
  puts $::fd "  [string trim $sql]"
  puts $::fd "} } } 1"
  puts $::fd ""
}

Changes to test/window6.test.

215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
do_execsql_test 9.0 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING)
  FROM c;
} {
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
do_catchsql_test 9.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
  FROM c;
} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}

do_catchsql_test 9.2 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
  FROM c;
} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}

do_catchsql_test 9.3 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
} {1 {DISTINCT is not supported for window functions}}

do_catchsql_test 9.4 {
................................................................................
  4 "BETWEEN 4 FOLLOWING AND 2 PRECEDING"
} {
  do_catchsql_test 9.7.$tn "
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
    SELECT count() OVER (
        ORDER BY x ROWS $frame 
    ) FROM c;
  " {1 {unsupported frame delimiter for ROWS}}
}

do_catchsql_test 9.8.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count() OVER (
      ORDER BY x ROWS BETWEEN a PRECEDING AND 2 FOLLOWING
  ) FROM c;







|
|
|
|
|
|
|
|
|
|
|







 







|







215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
do_execsql_test 9.0 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING)
  FROM c;
} {
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
#do_catchsql_test 9.1 {
#  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
#  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
#  FROM c;
#} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
#
#do_catchsql_test 9.2 {
#  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
#  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
#  FROM c;
#} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}

do_catchsql_test 9.3 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
} {1 {DISTINCT is not supported for window functions}}

do_catchsql_test 9.4 {
................................................................................
  4 "BETWEEN 4 FOLLOWING AND 2 PRECEDING"
} {
  do_catchsql_test 9.7.$tn "
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
    SELECT count() OVER (
        ORDER BY x ROWS $frame 
    ) FROM c;
  " {1 {unsupported frame specification}}
}

do_catchsql_test 9.8.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count() OVER (
      ORDER BY x ROWS BETWEEN a PRECEDING AND 2 FOLLOWING
  ) FROM c;

Changes to test/window8.tcl.

84
85
86
87
88
89
90


















91
92
93
94
  execsql_test 1.$tn.4 "
    SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
  execsql_test 1.$tn.5 "
    SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
}



















finish_test









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




84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
  execsql_test 1.$tn.4 "
    SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
  execsql_test 1.$tn.5 "
    SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
}

==========

execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (13, 26), (15, 30);
}

foreach {tn frame} {
  1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
  2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
  3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
} {
  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
}


finish_test


Changes to test/window8.test.

428
429
430
431
432
433
434
435





















436
do_execsql_test 1.19.4 {
  SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 979   AA aa 979   AA aa 979   AA aa 979   AA bb 979   AA bb 979   AA bb 979   AA bb 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   CC aa 979   CC aa 979   CC aa 979   CC aa 979   CC bb 979   CC bb 979   DD aa 979   DD aa 979   DD aa 979   DD bb 979   DD bb 979   DD bb 979   DD bb 979   EE aa 979   EE aa 979   EE bb 979   EE bb 979   EE bb 979   FF aa 979   FF aa 979   FF aa 979   FF aa 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   GG aa 979   GG aa 979   GG aa 979   GG aa 979   GG bb 979   GG bb 979   GG bb 979   GG bb 979   HH aa 963   HH aa 963   HH aa 963   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   II aa 899   II aa 899   II bb 899   II bb 899   II bb 899   II bb 899   II bb 899   JJ aa 839   JJ aa 839   JJ aa 839   JJ aa 839   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

do_execsql_test 1.19.5 {
  SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}






















finish_test








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

428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
do_execsql_test 1.19.4 {
  SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 979   AA aa 979   AA aa 979   AA aa 979   AA bb 979   AA bb 979   AA bb 979   AA bb 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   CC aa 979   CC aa 979   CC aa 979   CC aa 979   CC bb 979   CC bb 979   DD aa 979   DD aa 979   DD aa 979   DD bb 979   DD bb 979   DD bb 979   DD bb 979   EE aa 979   EE aa 979   EE bb 979   EE bb 979   EE bb 979   FF aa 979   FF aa 979   FF aa 979   FF aa 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   GG aa 979   GG aa 979   GG aa 979   GG aa 979   GG bb 979   GG bb 979   GG bb 979   GG bb 979   HH aa 963   HH aa 963   HH aa 963   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   II aa 899   II aa 899   II bb 899   II bb 899   II bb 899   II bb 899   II bb 899   JJ aa 839   JJ aa 839   JJ aa 839   JJ aa 839   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

do_execsql_test 1.19.5 {
  SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

#==========================================================================

do_execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (13, 26), (15, 30);
} {}

do_execsql_test 2.1 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {13 56   15 56}

do_execsql_test 2.2 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {13 {}   15 {}}

do_execsql_test 2.3 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {13 30   15 {}}

finish_test

Changes to test/windowerr.tcl.

16
17
18
19
20
21
22





23
24
25
26
27
28
29
30
31
32
33


34
35
36
37
38
39
40
41
42
43

start_test windowerr "2019 March 01"
ifcapable !windowfunc

execsql_test 1.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);





}

foreach {tn frame} {
  1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  2 "ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"


} {
  errorsql_test 1.$tn "
  SELECT a, sum(b) OVER (
    $frame
  ) FROM t3 ORDER BY 1
  "
}

finish_test








>
>
>
>
>











>
>




|





16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

start_test windowerr "2019 March 01"
ifcapable !windowfunc

execsql_test 1.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t1 VALUES(2, 2);
  INSERT INTO t1 VALUES(3, 3);
  INSERT INTO t1 VALUES(4, 4);
  INSERT INTO t1 VALUES(5, 5);
}

foreach {tn frame} {
  1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  2 "ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
} {
  errorsql_test 1.$tn "
  SELECT a, sum(b) OVER (
    $frame
  ) FROM t1 ORDER BY 1
  "
}

finish_test

Changes to test/windowerr.test.

19
20
21
22
23
24
25





26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67







68
69
70
source $testdir/tester.tcl
set testprefix windowerr

ifcapable !windowfunc { finish_test ; return }
do_execsql_test 1.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);





} {}

# PG says "ERROR:  frame starting offset must not be negative"
do_test 1.1 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t3 ORDER BY 1
} } } 1

# PG says "ERROR:  frame ending offset must not be negative"
do_test 1.2 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t3 ORDER BY 1
} } } 1

# PG says "ERROR:  invalid preceding or following size in window function"
do_test 1.3 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t3 ORDER BY 1
} } } 1

# PG says "ERROR:  invalid preceding or following size in window function"
do_test 1.4 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t3 ORDER BY 1
} } } 1

# PG says "ERROR:  frame starting offset must not be negative"
do_test 1.5 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t3 ORDER BY 1
} } } 1

# PG says "ERROR:  frame ending offset must not be negative"
do_test 1.6 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t3 ORDER BY 1







} } } 1

finish_test







>
>
>
>
>


|



|


|



|


|



|


|



|


|



|


|



|
>
>
>
>
>
>
>



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
source $testdir/tester.tcl
set testprefix windowerr

ifcapable !windowfunc { finish_test ; return }
do_execsql_test 1.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t1 VALUES(2, 2);
  INSERT INTO t1 VALUES(3, 3);
  INSERT INTO t1 VALUES(4, 4);
  INSERT INTO t1 VALUES(5, 5);
} {}

# PG says ERROR:  frame starting offset must not be negative
do_test 1.1 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  frame ending offset must not be negative
do_test 1.2 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  invalid preceding or following size in window function
do_test 1.3 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  invalid preceding or following size in window function
do_test 1.4 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  frame starting offset must not be negative
do_test 1.5 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  frame ending offset must not be negative
do_test 1.6 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
do_test 1.7 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

finish_test