Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -557,10 +557,18 @@ 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" @@ -925,18 +933,10 @@ if( eType==0 ){ bImplicitFrame = 1; eType = TK_RANGE; } - /* If a frame is declared "RANGE" (not "ROWS"), then it may not use - ** either " PRECEDING" or " 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 @@ -950,11 +950,11 @@ ** 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"); + sqlite3ErrorMsg(pParse, "unsupported frame specification"); goto windowAllocErr; } pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( pWin==0 ) goto windowAllocErr; @@ -1483,11 +1483,11 @@ 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); } @@ -1536,10 +1536,43 @@ for(i=0; inExpr; 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, @@ -1552,20 +1585,55 @@ 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 ){ - addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1); + 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); } @@ -1608,10 +1676,14 @@ 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; } @@ -1721,10 +1793,17 @@ 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; @@ -1743,12 +1822,13 @@ } 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 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; @@ -1755,22 +1835,10 @@ 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; iColpPartition; 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); @@ -1846,11 +1912,11 @@ sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr); } addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, addrGe); } - if( pMWin->eStart==TK_FOLLOWING && regEnd ){ + 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 ){ @@ -1886,12 +1952,22 @@ 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); + 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); @@ -1898,14 +1974,29 @@ 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( 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); } @@ -1936,10 +2027,15 @@ 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{ Index: test/pg_common.tcl ================================================================== --- test/pg_common.tcl +++ test/pg_common.tcl @@ -70,11 +70,12 @@ 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 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 "" Index: test/window6.test ================================================================== --- test/window6.test +++ test/window6.test @@ -217,21 +217,21 @@ 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.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}} @@ -260,11 +260,11 @@ 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}} + " {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 ( Index: test/window8.tcl ================================================================== --- test/window8.tcl +++ test/window8.tcl @@ -86,9 +86,27 @@ " 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 Index: test/window8.test ================================================================== --- test/window8.test +++ test/window8.test @@ -430,7 +430,28 @@ } {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 Index: test/windowerr.tcl ================================================================== --- test/windowerr.tcl +++ test/windowerr.tcl @@ -18,10 +18,15 @@ 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" @@ -29,15 +34,17 @@ 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 t3 ORDER BY 1 + ) FROM t1 ORDER BY 1 " } finish_test Index: test/windowerr.test ================================================================== --- test/windowerr.test +++ test/windowerr.test @@ -21,50 +21,62 @@ 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" +# 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 + ) FROM t1 ORDER BY 1 } } } 1 -# PG says "ERROR: frame ending offset must not be negative" +# 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 + ) FROM t1 ORDER BY 1 } } } 1 -# PG says "ERROR: invalid preceding or following size in window function" +# 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 + ) FROM t1 ORDER BY 1 } } } 1 -# PG says "ERROR: invalid preceding or following size in window function" +# 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 + ) FROM t1 ORDER BY 1 } } } 1 -# PG says "ERROR: frame starting offset must not be negative" +# 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 + ) FROM t1 ORDER BY 1 } } } 1 -# PG says "ERROR: frame ending offset must not be negative" +# 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 + ) 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