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: |
ffc32b246d92d53c66094afe11950b53 |
| User & Date: | dan 2019-03-09 20:49:17 |
Context
|
2019-03-11
| ||
| 11:12 | Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. (check-in: e7bced731a user: dan tags: window-functions) | |
|
2019-03-09
| ||
| 20:49 | Add support for RANGE window frames. Some cases still do not work. (check-in: ffc32b246d user: dan tags: window-functions) | |
| 07:38 | Merge latest trunk changes into this branch. (check-in: 53ea550ce7 user: dan tags: window-functions) | |
Changes
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
|