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

Changes to src/window.c.

   555    555       pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
   556    556       pWin->eStart = p->eStart;
   557    557       pWin->eEnd = p->eEnd;
   558    558       pWin->eType = p->eType;
   559    559     }else{
   560    560       sqlite3WindowChain(pParse, pWin, pList);
   561    561     }
          562  +  if( (pWin->eType==TK_RANGE)
          563  +   && (pWin->pStart || pWin->pEnd) 
          564  +   && (pWin->pOrderBy==0 || pWin->pOrderBy->nExpr!=1)
          565  +  ){
          566  +    sqlite3ErrorMsg(pParse, 
          567  +      "RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression"
          568  +    );
          569  +  }else
   562    570     if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
   563    571       sqlite3 *db = pParse->db;
   564    572       if( pWin->pFilter ){
   565    573         sqlite3ErrorMsg(pParse, 
   566    574             "FILTER clause may only be used with aggregate window functions"
   567    575         );
   568    576       }else
................................................................................
   923    931     assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );
   924    932   
   925    933     if( eType==0 ){
   926    934       bImplicitFrame = 1;
   927    935       eType = TK_RANGE;
   928    936     }
   929    937   
   930         -  /* If a frame is declared "RANGE" (not "ROWS"), then it may not use
   931         -  ** either "<expr> PRECEDING" or "<expr> FOLLOWING".
   932         -  */
   933         -  if( eType==TK_RANGE && (pStart!=0 || pEnd!=0) ){
   934         -    sqlite3ErrorMsg(pParse, "RANGE must use only UNBOUNDED or CURRENT ROW");
   935         -    goto windowAllocErr;
   936         -  }
   937         -
   938    938     /* Additionally, the
   939    939     ** starting boundary type may not occur earlier in the following list than
   940    940     ** the ending boundary type:
   941    941     **
   942    942     **   UNBOUNDED PRECEDING
   943    943     **   <expr> PRECEDING
   944    944     **   CURRENT ROW
................................................................................
   948    948     ** The parser ensures that "UNBOUNDED PRECEDING" cannot be used as an ending
   949    949     ** boundary, and than "UNBOUNDED FOLLOWING" cannot be used as a starting
   950    950     ** frame boundary.
   951    951     */
   952    952     if( (eStart==TK_CURRENT && eEnd==TK_PRECEDING)
   953    953      || (eStart==TK_FOLLOWING && (eEnd==TK_PRECEDING || eEnd==TK_CURRENT))
   954    954     ){
   955         -    sqlite3ErrorMsg(pParse, "unsupported frame delimiter for ROWS");
          955  +    sqlite3ErrorMsg(pParse, "unsupported frame specification");
   956    956       goto windowAllocErr;
   957    957     }
   958    958   
   959    959     pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
   960    960     if( pWin==0 ) goto windowAllocErr;
   961    961     pWin->eType = eType;
   962    962     pWin->eStart = eStart;
................................................................................
  1481   1481     if( pOrderBy ){
  1482   1482       int nVal = pOrderBy->nExpr;
  1483   1483       KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
  1484   1484       sqlite3VdbeAddOp3(v, OP_Compare, regOld, regNew, nVal);
  1485   1485       sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1486   1486       addr = sqlite3VdbeAddOp3(
  1487   1487           v, OP_Jump, sqlite3VdbeCurrentAddr(v)+1, 0, sqlite3VdbeCurrentAddr(v)+1
  1488         -        );
         1488  +    );
  1489   1489       VdbeCoverageEqNe(v);
  1490   1490       sqlite3VdbeAddOp3(v, OP_Copy, regNew, regOld, nVal-1);
  1491   1491     }else{
  1492   1492       addr = sqlite3VdbeAddOp0(v, OP_Goto);
  1493   1493     }
  1494   1494     return addr;
  1495   1495   }
................................................................................
  1534   1534       int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
  1535   1535       int i;
  1536   1536       for(i=0; i<pOrderBy->nExpr; i++){
  1537   1537         sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i);
  1538   1538       }
  1539   1539     }
  1540   1540   }
         1541  +
         1542  +/*
         1543  +** This function is called as part of generating VM programs for RANGE
         1544  +** offset PRECEDING/FOLLOWING frame boundaries. It generates code equivalent
         1545  +** to:
         1546  +**
         1547  +**   if( csr1.peerVal + regVal >= csr2.peerVal ) goto lbl;
         1548  +**   if( csr1.rowid >= csr2.rowid ) goto lbl;
         1549  +*/
         1550  +static void windowCodeRangeTest(
         1551  +  WindowCodeArg *p, 
         1552  +  int op,                          /* OP_Ge or OP_Gt */
         1553  +  int csr1, 
         1554  +  int regVal, 
         1555  +  int csr2,
         1556  +  int lbl
         1557  +){
         1558  +  Parse *pParse = p->pParse;
         1559  +  Vdbe *v = sqlite3GetVdbe(pParse);
         1560  +  int reg1 = sqlite3GetTempReg(pParse);
         1561  +  int reg2 = sqlite3GetTempReg(pParse);
         1562  +  windowReadPeerValues(p, csr1, reg1);
         1563  +  windowReadPeerValues(p, csr2, reg2);
         1564  +  sqlite3VdbeAddOp3(v, OP_Add, reg1, regVal, reg1);
         1565  +  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
         1566  +  sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
         1567  +  sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
         1568  +  sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
         1569  +  sqlite3ReleaseTempReg(pParse, reg1);
         1570  +  sqlite3ReleaseTempReg(pParse, reg2);
         1571  +
         1572  +  assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
         1573  +}
  1541   1574   
  1542   1575   static int windowCodeOp(
  1543   1576    WindowCodeArg *p,
  1544   1577    int op,
  1545   1578    int regCountdown,
  1546   1579    int jumpOnEof
  1547   1580   ){
................................................................................
  1550   1583     Window *pMWin = p->pMWin;
  1551   1584     int ret = 0;
  1552   1585     Vdbe *v = p->pVdbe;
  1553   1586     int addrIf = 0; 
  1554   1587     int addrContinue = 0;
  1555   1588     int addrGoto = 0;
  1556   1589     int bPeer = (pMWin->eType!=TK_ROWS);
         1590  +
         1591  +  int lblDone = sqlite3VdbeMakeLabel(pParse);
         1592  +  int addrNextRange = 0;
  1557   1593   
  1558   1594     /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
  1559   1595     ** starts with UNBOUNDED PRECEDING. */
  1560   1596     if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
  1561   1597       assert( regCountdown==0 && jumpOnEof==0 );
  1562   1598       return 0;
  1563   1599     }
  1564   1600   
  1565   1601     if( regCountdown>0 ){
  1566         -    addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
         1602  +    if( pMWin->eType==TK_RANGE ){
         1603  +      addrNextRange = sqlite3VdbeCurrentAddr(v);
         1604  +
         1605  +      switch( op ){
         1606  +        case WINDOW_RETURN_ROW: {
         1607  +          assert( 0 );
         1608  +          break;
         1609  +        }
         1610  +
         1611  +        case WINDOW_AGGINVERSE: {
         1612  +          if( pMWin->eStart==TK_FOLLOWING ){
         1613  +            windowCodeRangeTest(
         1614  +                p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
         1615  +            );
         1616  +          }else{
         1617  +            windowCodeRangeTest(
         1618  +                p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
         1619  +            );
         1620  +          }
         1621  +          break;
         1622  +        }
         1623  +
         1624  +        case WINDOW_AGGSTEP: {
         1625  +          windowCodeRangeTest(
         1626  +            p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone
         1627  +          );
         1628  +          break;
         1629  +        }
         1630  +      }
         1631  +
         1632  +    }else{
         1633  +      addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
         1634  +    }
  1567   1635     }
  1568   1636   
  1569   1637     if( op==WINDOW_RETURN_ROW ){
  1570   1638       windowAggFinal(pParse, pMWin, 0);
  1571   1639     }
  1572   1640     addrContinue = sqlite3VdbeCurrentAddr(v);
  1573   1641     switch( op ){
................................................................................
  1606   1674       int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
  1607   1675       windowReadPeerValues(p, csr, regTmp);
  1608   1676       addr = windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg);
  1609   1677       sqlite3VdbeChangeP2(v, addr, addrContinue);
  1610   1678       sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  1611   1679     }
  1612   1680   
         1681  +  if( addrNextRange ){
         1682  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNextRange);
         1683  +  }
         1684  +  sqlite3VdbeResolveLabel(v, lblDone);
  1613   1685     if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
  1614   1686     if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  1615   1687     return ret;
  1616   1688   }
  1617   1689   
  1618   1690   /*
  1619   1691   ** This function - windowCodeStep() - generates the VM code that reads data
................................................................................
  1719   1791   
  1720   1792     int reg = pParse->nMem+1;
  1721   1793     int regRecord = reg+nSub;
  1722   1794     int regRowid = regRecord+1;
  1723   1795     int regPeer = 0;
  1724   1796     int regNewPeer = 0;
  1725   1797     WindowCodeArg s;
         1798  +
         1799  +  assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT 
         1800  +       || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED 
         1801  +  );
         1802  +  assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT 
         1803  +       || pMWin->eEnd==TK_UNBOUNDED || pMWin->eEnd==TK_PRECEDING 
         1804  +  );
  1726   1805   
  1727   1806     memset(&s, 0, sizeof(WindowCodeArg));
  1728   1807     s.pParse = pParse;
  1729   1808     s.pMWin = pMWin;
  1730   1809     s.pVdbe = v;
  1731   1810     s.regGosub = regGosub;
  1732   1811     s.addrGosub = addrGosub;
................................................................................
  1741   1820     if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
  1742   1821       regStart = ++pParse->nMem;
  1743   1822     }
  1744   1823     if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
  1745   1824       regEnd = ++pParse->nMem;
  1746   1825     }
  1747   1826   
  1748         -  /* If this is not a "ROWS BETWEEN ..." frame, then allocate registers to
  1749         -  ** store a copy of the current ORDER BY expressions. */
         1827  +  /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of
         1828  +  ** registers to store a copies of the ORDER BY expressions for the
         1829  +  ** main loop, and for each cursor (start, current and end). */
  1750   1830     if( pMWin->eType!=TK_ROWS ){
  1751   1831       int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
  1752   1832       regNewPeer = reg + pMWin->nBufferCol;
  1753   1833       if( pMWin->pPartition ) regNewPeer += pMWin->pPartition->nExpr;
  1754   1834   
  1755   1835       regPeer = pParse->nMem+1;       pParse->nMem += nPeer;
  1756   1836       s.start.reg = pParse->nMem+1;   pParse->nMem += nPeer;
  1757   1837       s.current.reg = pParse->nMem+1; pParse->nMem += nPeer;
  1758   1838       s.end.reg = pParse->nMem+1;     pParse->nMem += nPeer;
  1759   1839     }
  1760         -
  1761         -  assert( pMWin->eStart==TK_PRECEDING 
  1762         -       || pMWin->eStart==TK_CURRENT 
  1763         -       || pMWin->eStart==TK_FOLLOWING 
  1764         -       || pMWin->eStart==TK_UNBOUNDED 
  1765         -  );
  1766         -  assert( pMWin->eEnd==TK_FOLLOWING 
  1767         -       || pMWin->eEnd==TK_CURRENT 
  1768         -       || pMWin->eEnd==TK_UNBOUNDED 
  1769         -       || pMWin->eEnd==TK_PRECEDING 
  1770         -  );
  1771         -
  1772   1840   
  1773   1841     /* Load the column values for the row returned by the sub-select
  1774   1842     ** into an array of registers starting at reg. Assemble them into
  1775   1843     ** a record in register regRecord. TODO: An optimization here? */
  1776   1844     for(iCol=0; iCol<nSub; iCol++){
  1777   1845       sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, iCol, reg+iCol);
  1778   1846     }
................................................................................
  1787   1855     if( pMWin->pPartition ){
  1788   1856       int addr;
  1789   1857       ExprList *pPart = pMWin->pPartition;
  1790   1858       int nPart = pPart->nExpr;
  1791   1859       int regNewPart = reg + pMWin->nBufferCol;
  1792   1860       KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
  1793   1861   
  1794         -    addrIf = sqlite3VdbeAddOp1(v, OP_If, pMWin->regFirst);
  1795   1862       addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart);
  1796   1863       sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1797   1864       sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
  1798   1865       VdbeCoverageEqNe(v);
  1799   1866       addrGosubFlush = sqlite3VdbeAddOp1(v, OP_Gosub, regFlushPart);
  1800   1867       VdbeComment((v, "call flush_partition"));
  1801         -    sqlite3VdbeJumpHere(v, addrIf);
  1802   1868       sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
  1803   1869     }
  1804   1870   
  1805   1871     /* Insert the new row into the ephemeral table */
  1806   1872     sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
  1807   1873     sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  1808   1874     sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regSize, 1);
................................................................................
  1844   1910         sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
  1845   1911         windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1846   1912         sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  1847   1913       }
  1848   1914       addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
  1849   1915       sqlite3VdbeJumpHere(v, addrGe);
  1850   1916     }
  1851         -  if( pMWin->eStart==TK_FOLLOWING && regEnd ){
         1917  +  if( pMWin->eStart==TK_FOLLOWING && pMWin->eType!=TK_RANGE && regEnd ){
  1852   1918       assert( pMWin->eEnd==TK_FOLLOWING );
  1853   1919       sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  1854   1920     }
  1855   1921   
  1856   1922     if( pMWin->eStart!=TK_UNBOUNDED ){
  1857   1923       sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
  1858   1924     }
................................................................................
  1884   1950     }
  1885   1951     if( regPeer ){
  1886   1952       addrPeerJump = windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer);
  1887   1953     }
  1888   1954     if( pMWin->eStart==TK_FOLLOWING ){
  1889   1955       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1890   1956       if( pMWin->eEnd!=TK_UNBOUNDED ){
  1891         -      windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
  1892         -      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         1957  +      if( pMWin->eType==TK_RANGE ){
         1958  +        int lbl = sqlite3VdbeMakeLabel(pParse);
         1959  +        int addrNext = sqlite3VdbeCurrentAddr(v);
         1960  +        windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
         1961  +        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         1962  +        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
         1963  +        sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
         1964  +        sqlite3VdbeResolveLabel(v, lbl);
         1965  +      }else{
         1966  +        windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
         1967  +        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         1968  +      }
  1893   1969       }
  1894   1970     }else
  1895   1971     if( pMWin->eEnd==TK_PRECEDING ){
  1896   1972       windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
  1897   1973       windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  1898   1974       windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1899   1975     }else{
  1900   1976       int addr;
  1901   1977       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1902   1978       if( pMWin->eEnd!=TK_UNBOUNDED ){
  1903         -      if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
  1904         -      windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  1905         -      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1906         -      if( regEnd ) sqlite3VdbeJumpHere(v, addr);
         1979  +      if( pMWin->eType==TK_RANGE ){
         1980  +        int lbl;
         1981  +        addr = sqlite3VdbeCurrentAddr(v);
         1982  +        if( regEnd ){
         1983  +          lbl = sqlite3VdbeMakeLabel(pParse);
         1984  +          windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
         1985  +        }
         1986  +        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
         1987  +        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         1988  +        if( regEnd ){
         1989  +          sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
         1990  +          sqlite3VdbeResolveLabel(v, lbl);
         1991  +        }
         1992  +      }else{
         1993  +        if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
         1994  +        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
         1995  +        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         1996  +        if( regEnd ) sqlite3VdbeJumpHere(v, addr);
         1997  +      }
  1907   1998       }
  1908   1999     }
  1909   2000     if( addrPeerJump ){
  1910   2001       sqlite3VdbeJumpHere(v, addrPeerJump);
  1911   2002     }
  1912   2003     VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));
  1913   2004   
................................................................................
  1934   2025       windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  1935   2026     }else if( pMWin->eStart==TK_FOLLOWING ){
  1936   2027       int addrStart;
  1937   2028       int addrBreak1;
  1938   2029       int addrBreak2;
  1939   2030       int addrBreak3;
  1940   2031       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
         2032  +    if( pMWin->eType==TK_RANGE ){
         2033  +      addrStart = sqlite3VdbeCurrentAddr(v);
         2034  +      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1);
         2035  +      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
         2036  +    }else
  1941   2037       if( pMWin->eEnd==TK_UNBOUNDED ){
  1942   2038         addrStart = sqlite3VdbeCurrentAddr(v);
  1943   2039         addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1);
  1944   2040         addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
  1945   2041       }else{
  1946   2042         assert( pMWin->eEnd==TK_FOLLOWING );
  1947   2043         addrStart = sqlite3VdbeCurrentAddr(v);

Changes to test/pg_common.tcl.

    68     68   }
    69     69   
    70     70   proc errorsql_test {tn sql} {
    71     71     set rc [catch {execsql $sql} msg]
    72     72     if {$rc==0} {
    73     73       error "errorsql_test SQL did not cause an error!"
    74     74     }
    75         -  puts $::fd "# PG says \"[string trim $msg]\""
           75  +  set msg [lindex [split [string trim $msg] "\n"] 0]
           76  +  puts $::fd "# PG says $msg"
    76     77     set sql [string map {string_agg group_concat} $sql]
    77     78     puts $::fd "do_test $tn { catch { execsql {"
    78     79     puts $::fd "  [string trim $sql]"
    79     80     puts $::fd "} } } 1"
    80     81     puts $::fd ""
    81     82   }
    82     83   

Changes to test/window6.test.

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

Changes to test/window8.tcl.

    84     84     execsql_test 1.$tn.4 "
    85     85       SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
    86     86     "
    87     87     execsql_test 1.$tn.5 "
    88     88       SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
    89     89     "
    90     90   }
           91  +
           92  +==========
           93  +
           94  +execsql_test 2.0 {
           95  +  DROP TABLE IF EXISTS t1;
           96  +  CREATE TABLE t1(a INTEGER, b INTEGER);
           97  +  INSERT INTO t1 VALUES
           98  +      (13, 26), (15, 30);
           99  +}
          100  +
          101  +foreach {tn frame} {
          102  +  1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
          103  +  2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
          104  +  3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
          105  +} {
          106  +  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
          107  +}
          108  +
    91    109   
    92    110   finish_test
    93    111   
    94    112   

Changes to test/window8.test.

   428    428   do_execsql_test 1.19.4 {
   429    429     SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
   430    430   } {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 {}}
   431    431   
   432    432   do_execsql_test 1.19.5 {
   433    433     SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
   434    434   } {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 {}}
          435  +
          436  +#==========================================================================
          437  +
          438  +do_execsql_test 2.0 {
          439  +  DROP TABLE IF EXISTS t1;
          440  +  CREATE TABLE t1(a INTEGER, b INTEGER);
          441  +  INSERT INTO t1 VALUES
          442  +      (13, 26), (15, 30);
          443  +} {}
          444  +
          445  +do_execsql_test 2.1 {
          446  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
          447  +} {13 56   15 56}
          448  +
          449  +do_execsql_test 2.2 {
          450  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
          451  +} {13 {}   15 {}}
          452  +
          453  +do_execsql_test 2.3 {
          454  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
          455  +} {13 30   15 {}}
   435    456   
   436    457   finish_test

Changes to test/windowerr.tcl.

    16     16   
    17     17   start_test windowerr "2019 March 01"
    18     18   ifcapable !windowfunc
    19     19   
    20     20   execsql_test 1.0 {
    21     21     DROP TABLE IF EXISTS t1;
    22     22     CREATE TABLE t1(a INTEGER, b INTEGER);
           23  +  INSERT INTO t1 VALUES(1, 1);
           24  +  INSERT INTO t1 VALUES(2, 2);
           25  +  INSERT INTO t1 VALUES(3, 3);
           26  +  INSERT INTO t1 VALUES(4, 4);
           27  +  INSERT INTO t1 VALUES(5, 5);
    23     28   }
    24     29   
    25     30   foreach {tn frame} {
    26     31     1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
    27     32     2 "ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
    28     33   
    29     34     3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
    30     35     4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"
    31     36   
    32     37     5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
    33     38     6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
           39  +
           40  +  7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
    34     41   } {
    35     42     errorsql_test 1.$tn "
    36     43     SELECT a, sum(b) OVER (
    37     44       $frame
    38         -  ) FROM t3 ORDER BY 1
           45  +  ) FROM t1 ORDER BY 1
    39     46     "
    40     47   }
    41     48   
    42     49   finish_test
    43     50   

Changes to test/windowerr.test.

    19     19   source $testdir/tester.tcl
    20     20   set testprefix windowerr
    21     21   
    22     22   ifcapable !windowfunc { finish_test ; return }
    23     23   do_execsql_test 1.0 {
    24     24     DROP TABLE IF EXISTS t1;
    25     25     CREATE TABLE t1(a INTEGER, b INTEGER);
           26  +  INSERT INTO t1 VALUES(1, 1);
           27  +  INSERT INTO t1 VALUES(2, 2);
           28  +  INSERT INTO t1 VALUES(3, 3);
           29  +  INSERT INTO t1 VALUES(4, 4);
           30  +  INSERT INTO t1 VALUES(5, 5);
    26     31   } {}
    27     32   
    28         -# PG says "ERROR:  frame starting offset must not be negative"
           33  +# PG says ERROR:  frame starting offset must not be negative
    29     34   do_test 1.1 { catch { execsql {
    30     35     SELECT a, sum(b) OVER (
    31     36       ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING
    32         -  ) FROM t3 ORDER BY 1
           37  +  ) FROM t1 ORDER BY 1
    33     38   } } } 1
    34     39   
    35         -# PG says "ERROR:  frame ending offset must not be negative"
           40  +# PG says ERROR:  frame ending offset must not be negative
    36     41   do_test 1.2 { catch { execsql {
    37     42     SELECT a, sum(b) OVER (
    38     43       ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING
    39         -  ) FROM t3 ORDER BY 1
           44  +  ) FROM t1 ORDER BY 1
    40     45   } } } 1
    41     46   
    42         -# PG says "ERROR:  invalid preceding or following size in window function"
           47  +# PG says ERROR:  invalid preceding or following size in window function
    43     48   do_test 1.3 { catch { execsql {
    44     49     SELECT a, sum(b) OVER (
    45     50       ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING
    46         -  ) FROM t3 ORDER BY 1
           51  +  ) FROM t1 ORDER BY 1
    47     52   } } } 1
    48     53   
    49         -# PG says "ERROR:  invalid preceding or following size in window function"
           54  +# PG says ERROR:  invalid preceding or following size in window function
    50     55   do_test 1.4 { catch { execsql {
    51     56     SELECT a, sum(b) OVER (
    52     57       ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING
    53         -  ) FROM t3 ORDER BY 1
           58  +  ) FROM t1 ORDER BY 1
    54     59   } } } 1
    55     60   
    56         -# PG says "ERROR:  frame starting offset must not be negative"
           61  +# PG says ERROR:  frame starting offset must not be negative
    57     62   do_test 1.5 { catch { execsql {
    58     63     SELECT a, sum(b) OVER (
    59     64       ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING
    60         -  ) FROM t3 ORDER BY 1
           65  +  ) FROM t1 ORDER BY 1
    61     66   } } } 1
    62     67   
    63         -# PG says "ERROR:  frame ending offset must not be negative"
           68  +# PG says ERROR:  frame ending offset must not be negative
    64     69   do_test 1.6 { catch { execsql {
    65     70     SELECT a, sum(b) OVER (
    66     71       ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING
    67         -  ) FROM t3 ORDER BY 1
           72  +  ) FROM t1 ORDER BY 1
           73  +} } } 1
           74  +
           75  +# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
           76  +do_test 1.7 { catch { execsql {
           77  +  SELECT a, sum(b) OVER (
           78  +    ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
           79  +  ) FROM t1 ORDER BY 1
    68     80   } } } 1
    69     81   
    70     82   finish_test