Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1922,20 +1922,19 @@ if( pOp->p5 & SQLITE_NULLEQ ){ /* If SQLITE_NULLEQ is set (which will only happen if the operator is ** OP_Eq or OP_Ne) then take the jump or not depending on whether ** or not both operands are null. */ - assert( pOp->opcode==OP_Eq || pOp->opcode==OP_Ne ); assert( (flags1 & MEM_Cleared)==0 ); assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 || CORRUPT_DB ); testcase( (pOp->p5 & SQLITE_JUMPIFNULL)!=0 ); if( (flags1&flags3&MEM_Null)!=0 && (flags3&MEM_Cleared)==0 ){ res = 0; /* Operands are equal */ }else{ - res = 1; /* Operands are not equal */ + res = ((flags3 & MEM_Null) ? -1 : +1); /* Operands are not equal */ } }else{ /* SQLITE_NULLEQ is clear and at least one operand is NULL, ** then the result is always NULL. ** The jump is taken if the SQLITE_JUMPIFNULL bit is set. Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -1781,25 +1781,18 @@ windowReadPeerValues(p, csr1, reg1); windowReadPeerValues(p, csr2, reg2); /* Check if the peer value for csr1 value is a text or blob by comparing - ** it to the smallest possible string - ''. */ + ** it to the smallest possible string - ''. If it is, jump over the + ** OP_Add or OP_Subtract operation and proceed directly to the comparison. */ sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC); addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1); - - if( op==OP_Le ){ - sqlite3VdbeAddOp2(v, OP_IsNull, reg1, lbl); - } - if( op==OP_Ge ){ - sqlite3VdbeAddOp2(v, OP_NotNull, reg1, sqlite3VdbeCurrentAddr(v)+2); - sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl); - } - sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1); sqlite3VdbeJumpHere(v, addrGe); sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1); + sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); sqlite3ReleaseTempReg(pParse, reg1); sqlite3ReleaseTempReg(pParse, reg2); } @@ -2365,17 +2358,19 @@ ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row ** has been returned to the caller (WINDOW_RETURN_ROW), or they may ** be deleted after they enter the frame (WINDOW_AGGSTEP). */ switch( pMWin->eStart ){ case TK_FOLLOWING: { - sqlite3 *db = pParse->db; - sqlite3_value *pVal = 0; - sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal); - if( pVal && sqlite3_value_int(pVal)>0 ){ - s.eDelete = WINDOW_RETURN_ROW; + if( pMWin->eType!=TK_RANGE ){ + sqlite3 *db = pParse->db; + sqlite3_value *pVal = 0; + sqlite3ValueFromExpr(db,pMWin->pStart,db->enc,SQLITE_AFF_NUMERIC,&pVal); + if( pVal && sqlite3_value_int(pVal)>0 ){ + s.eDelete = WINDOW_RETURN_ROW; + } + sqlite3ValueFree(pVal); } - sqlite3ValueFree(pVal); break; } case TK_UNBOUNDED: if( windowCacheFrame(pMWin)==0 ){ if( pMWin->eEnd==TK_PRECEDING ){ @@ -2387,10 +2382,12 @@ break; default: s.eDelete = WINDOW_AGGINVERSE; break; } + + s.eDelete = 0; /* Allocate registers for the array of values from the sub-query, the ** samve values in record form, and the rowid used to insert said record ** into the ephemeral table. */ regNew = pParse->nMem+1; Index: test/pg_common.tcl ================================================================== --- test/pg_common.tcl +++ test/pg_common.tcl @@ -68,10 +68,12 @@ } proc execsql_test {tn sql} { set res [execsql $sql] set sql [string map {string_agg group_concat} $sql] + set sql [string map [list {NULLS FIRST} {}] $sql] + set sql [string map [list {NULLS LAST} {}] $sql] puts $::fd "do_execsql_test $tn {" puts $::fd " [string trim $sql]" puts $::fd "} {$res}" puts $::fd "" } Index: test/window8.tcl ================================================================== --- test/window8.tcl +++ test/window8.tcl @@ -169,14 +169,48 @@ CREATE TABLE t1(a INTEGER, b INTEGER); INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5); } -execsql_test 4.1 { +execsql_test 4.1.1 { SELECT sum(b) OVER ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING ) FROM t1 ORDER BY 1; } +execsql_test 4.1.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1; +} + +execsql_test 4.2.1 { + SELECT sum(b) OVER ( + ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 NULLS FIRST; +} + +execsql_test 4.2.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 NULLS FIRST; +} + +execsql_test 4.3.1 { + SELECT sum(b) OVER ( + ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 NULLS FIRST; +} + +execsql_test 4.4.1 { + SELECT sum(b) OVER ( + ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 ORDER BY 1 NULLS FIRST; +} +execsql_test 4.4.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 ORDER BY 1 NULLS FIRST; +} finish_test Index: test/window8.test ================================================================== --- test/window8.test +++ test/window8.test @@ -2620,12 +2620,48 @@ CREATE TABLE t1(a INTEGER, b INTEGER); INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5); } {} -do_execsql_test 4.1 { +do_execsql_test 4.1.1 { SELECT sum(b) OVER ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING ) FROM t1 ORDER BY 1; } {6 6 6 9 9} + +do_execsql_test 4.1.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1; +} {6 6 6 9 9} + +do_execsql_test 4.2.1 { + SELECT sum(b) OVER ( + ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 ; +} {{} {} 6 6 6} + +do_execsql_test 4.2.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 ; +} {{} {} 6 6 6} + +do_execsql_test 4.3.1 { + SELECT sum(b) OVER ( + ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING + ) FROM t1 ORDER BY 1 ; +} {6 6 6 15 15} + +do_execsql_test 4.4.1 { + SELECT sum(b) OVER ( + ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 ORDER BY 1 ; +} {3 6 9 9 12} + +do_execsql_test 4.4.2 { + SELECT sum(b) OVER ( + ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 ORDER BY 1 ; +} {5 6 8 9 10} finish_test