Fix a bug in RANGE window functions that use "ORDER BY <expr> DESC NULLS FIRST" as the window-frame ORDER BY clause.

FossilOrigin-Name: 39b4cad4a51bb5116d62ffb16ac36d96a9280321b049eb2d008605392f52a459

5 files changed, 119 insertions, 35 deletions

diff --git a/manifest b/manifest
index f9cae012c..69eb8d41d 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C The\sexpression\s"(X\sIS\sFALSE)\sIN\s(FALSE)"\sdoes\snot\simply\sthat\sX\sis\sNOT\sNULL.\nTicket\s[f8f472cbc77ba9c9]
-D 2019-08-30T16:00:58.750
+C Fix\sa\sbug\sin\sRANGE\swindow\sfunctions\sthat\suse\s"ORDER\sBY\s<expr>\sDESC\sNULLS\sFIRST"\sas\sthe\swindow-frame\sORDER\sBY\sclause.
+D 2019-08-30T16:14:58.448
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -613,7 +613,7 @@ F src/where.c fb546afbdbedc77a6193a236db92f6f85bc8e17412ec596230dd8aee03a93716
 F src/whereInt.h 4a296fd4fa79fdcbc2b5e8c1b898901617655811223e1082b899c23ecb092217
 F src/wherecode.c 535c8e228478fd971b9a5b6cb6773995b0fbf7020d5989508a5094ce5b8cd95b
 F src/whereexpr.c 2757afbd5cfdbb420f9d0392e1bd5f5c0e33dee50a8c692befc7e502308e449f
-F src/window.c 7d74882b41db7f44300489b3513f94f3f95684725e6d8e4e8cede44f015bb4b0
+F src/window.c 701bea99097fd9f2c49e0af54446c986853e639fa297248ea62fac5e3f3b8dba
 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
 F test/affinity2.test b03930d288e38b07f55023a58538ad174605695e98934bdab1facf6bd9ecc436
 F test/affinity3.test 6a101af2fc945ce2912f6fe54dd646018551710d
@@ -1711,8 +1711,8 @@ F test/window5.test d328dd18221217c49c144181975eea17339eaeaf0e9aa558cee3afb84652
 F test/window6.test f8d674254b23289cc17c84d79dec7eda7caa1dfb7836c43122cfdf3640d1df32
 F test/window7.tcl 6a1210f05d40ec89c22960213a22cd3f98d4e2f2eb20646c83c8c30d4d76108f
 F test/window7.test 1d31276961ae7801edc72173edaf7593e3cbc79c06d1f1f09e20d8418af403cd
-F test/window8.tcl 5884cc884f9605bf88c0d18a534894bf9342f72687bf1bc43ed0cab4c8af7973
-F test/window8.test 48590f3737d17eec503d77769c13ead15d12e8819820b1dc68afe8a3c5bc3250
+F test/window8.tcl 3b63931d608b6f00a9d26368207a7ffc9370c96e3e137ae2aff35284ade69d13
+F test/window8.test 4531204bfb5d833efbd8a5a1527c9871e62f8ae479386f42a7b55bdb5be39df3
 F test/window9.test 20a6b590be718b6bc98a5356d4396d6cdf19329c547da084fa225b92d68e1693
 F test/windowerr.tcl f5acd6fbc210d7b5546c0e879d157888455cd4a17a1d3f28f07c1c8a387019e0
 F test/windowerr.test a8b752402109c15aa1c5efe1b93ccb0ce1ef84fa964ae1cd6684dd0b3cc1819b
@@ -1837,7 +1837,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 057fb8b1809b8b9c8fff0fd0804153b9644f0545c23c6ddc4758bda3381094b9
-R dbd65dd02f8837e1f86624f44fe9aa97
-U drh
-Z 96ee8b9606bd233ac998576dd644ea25
+P dd66134817ecbda01c59a05ad0d6ac44bee700ab10cd2119c869dd69af293fe2
+R f9d7b459685c1da5bc61b772c0f2da65
+U dan
+Z 05bbd4877d54c35c485e8a33829586c4
diff --git a/manifest.uuid b/manifest.uuid
index 98bf5de4c..7d6528b57 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-dd66134817ecbda01c59a05ad0d6ac44bee700ab10cd2119c869dd69af293fe2
\ No newline at end of file
+39b4cad4a51bb5116d62ffb16ac36d96a9280321b049eb2d008605392f52a459
\ No newline at end of file
diff --git a/src/window.c b/src/window.c
index ac8d1080f..453755995 100644
--- a/src/window.c
+++ b/src/window.c
@@ -1854,31 +1854,42 @@ static void windowIfNewPeer(
 /*
 ** This function is called as part of generating VM programs for RANGE
 ** offset PRECEDING/FOLLOWING frame boundaries. Assuming "ASC" order for
-** the ORDER BY term in the window, it generates code equivalent to:
+** the ORDER BY term in the window, and that argument op is OP_Ge, it generates
+** code equivalent to:
 **
 **   if( csr1.peerVal + regVal >= csr2.peerVal ) goto lbl;
 **
-** A special type of arithmetic is used such that if csr.peerVal is not
-** a numeric type (real or integer), then the result of the addition is
-** a copy of csr1.peerVal.
+** The value of parameter op may also be OP_Gt or OP_Le. In these cases the
+** operator in the above pseudo-code is replaced with ">" or "<=", respectively.
+**
+** If the sort-order for the ORDER BY term in the window is DESC, then the
+** comparison is reversed. Instead of adding regVal to csr1.peerVal, it is
+** subtracted. And the comparison operator is inverted to - ">=" becomes "<=",
+** ">" becomes "<", and so on. So, with DESC sort order, if the argument op
+** is OP_Ge, the generated code is equivalent to:
+**
+**   if( csr1.peerVal - regVal <= csr2.peerVal ) goto lbl;
+**
+** A special type of arithmetic is used such that if csr1.peerVal is not
+** a numeric type (real or integer), then the result of the addition addition
+** or subtraction is a a copy of csr1.peerVal.
 */
 static void windowCodeRangeTest(
   WindowCodeArg *p, 
   int op,                          /* OP_Ge, OP_Gt, or OP_Le */
-  int csr1, 
-  int regVal, 
-  int csr2,
-  int lbl
+  int csr1,                        /* Cursor number for cursor 1 */
+  int regVal,                      /* Register containing non-negative number */
+  int csr2,                        /* Cursor number for cursor 2 */
+  int lbl                          /* Jump destination if the condition is true */
 ){
   Parse *pParse = p->pParse;
   Vdbe *v = sqlite3GetVdbe(pParse);
-  int reg1 = sqlite3GetTempReg(pParse);
-  int reg2 = sqlite3GetTempReg(pParse);
-  int arith = OP_Add;
-  int addrGe;
-  ExprList *pOrderBy = p->pMWin->pOrderBy;
-
-  int regString = ++pParse->nMem;
+  ExprList *pOrderBy = p->pMWin->pOrderBy;  /* ORDER BY clause for this window */
+  int reg1 = sqlite3GetTempReg(pParse);     /* Register for csr1.peerVal+regVal */
+  int reg2 = sqlite3GetTempReg(pParse);     /* Regiser for csr2.peerVal */
+  int regString = ++pParse->nMem;           /* Register for constant value '' */
+  int arith = OP_Add;                       /* OP_Add or OP_Subtract */
+  int addrGe;                               /* Jump destination */
 
   assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
   assert( pOrderBy && pOrderBy->nExpr==1 );
@@ -1891,47 +1902,92 @@ static void windowCodeRangeTest(
     arith = OP_Subtract;
   }
 
+  /* Read the peer-value from each cursor into a register */
   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 - ''. If it is, jump over the
-  ** OP_Add or OP_Subtract operation and proceed directly to the comparison. */
+  VdbeModuleComment((v, "CodeRangeTest: if( R%d %s R%d %s R%d ) goto lbl",
+      reg1, (arith==OP_Add ? "+" : "-"), regVal,
+      ((op==OP_Ge) ? ">=" : (op==OP_Le) ? "<=" : (op==OP_Gt) ? ">" : "<"), reg2
+  ));
+
+  /* Register reg1 currently contains csr1.peerVal (the peer-value from csr1).
+  ** This block adds (or subtracts for DESC) the numeric value in regVal
+  ** from it. Or, if reg1 is not numeric (it is a NULL, a text value or a blob),
+  ** then leave reg1 as it is. In pseudo-code, this is implemented as:
+  **
+  **   if( reg1>='' ) goto addrGe;
+  **   reg1 = reg1 +/- regVal
+  **   addrGe:
+  **
+  ** Since all strings and blobs are greater-than-or-equal-to an empty string,
+  ** the add/subtract is skipped for these, as required. If reg1 is a NULL,
+  ** then the arithmetic is performed, but since adding or subtracting from
+  ** NULL is always NULL anyway, this case is handled as required too.  */
   sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
   addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);
   VdbeCoverage(v);
   sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
   sqlite3VdbeJumpHere(v, addrGe);
+
+  /* If the BIGNULL flag is set for the ORDER BY, then it is required to 
+  ** consider NULL values to be larger than all other values, instead of 
+  ** the usual smaller. The VDBE opcodes OP_Ge and so on do not handle this
+  ** (and adding that capability causes a performance regression), so
+  ** instead if the BIGNULL flag is set then cases where either reg1 or
+  ** reg2 are NULL are handled separately in the following block. The code
+  ** generated is equivalent to:
+  **
+  **   if( reg1 IS NULL ){
+  **     if( op==OP_Gt ) goto lbl;
+  **     if( op==OP_Ge && reg2 IS NOT NULL ) goto lbl;
+  **     if( op==OP_Le && reg2 IS NULL ) goto lbl;
+  **   }else if( reg2 IS NULL ){
+  **     if( op==OP_Le ) goto lbl;
+  **   }
+  **
+  ** Additionally, if either reg1 or reg2 are NULL but the jump to lbl is 
+  ** not taken, control jumps over the comparison operator coded below this
+  ** block.  */
   if( pOrderBy->a[0].sortFlags & KEYINFO_ORDER_BIGNULL ){
-    int addr;
-    addr = sqlite3VdbeAddOp1(v, OP_NotNull, reg1); VdbeCoverage(v);
+    /* This block runs if reg1 contains a NULL. */
+    int addr = sqlite3VdbeAddOp1(v, OP_NotNull, reg1); VdbeCoverage(v);
     switch( op ){
-      case OP_Ge: sqlite3VdbeAddOp2(v, OP_Goto, 0, lbl); break;
+      case OP_Ge: 
+        sqlite3VdbeAddOp2(v, OP_Goto, 0, lbl); 
+        break;
       case OP_Gt: 
         sqlite3VdbeAddOp2(v, OP_NotNull, reg2, lbl); VdbeCoverage(v); 
         break;
-      default:
-        assert( op==OP_Le );
+      default: assert( op==OP_Le );
         sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl); VdbeCoverage(v); 
         break;
     }
-    sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+2);
+    sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
+
+    /* This block runs if reg1 is not NULL, but reg2 is. */
     sqlite3VdbeJumpHere(v, addr);
     sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl); VdbeCoverage(v);
     if( op==OP_Gt || op==OP_Ge ){
       sqlite3VdbeChangeP2(v, -1, sqlite3VdbeCurrentAddr(v)+1);
     }
   }
+
+  /* Compare registers reg2 and reg1, taking the jump if required. Note that
+  ** control skips over this test if the BIGNULL flag is set and either
+  ** reg1 or reg2 contain a NULL value.  */
   sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1); VdbeCoverage(v);
   sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
+
   assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
   testcase(op==OP_Ge); VdbeCoverageIf(v, op==OP_Ge);
   testcase(op==OP_Lt); VdbeCoverageIf(v, op==OP_Lt);
   testcase(op==OP_Le); VdbeCoverageIf(v, op==OP_Le);
   testcase(op==OP_Gt); VdbeCoverageIf(v, op==OP_Gt);
-
   sqlite3ReleaseTempReg(pParse, reg1);
   sqlite3ReleaseTempReg(pParse, reg2);
+
+  VdbeModuleComment((v, "CodeRangeTest: end"));
 }
 
 /*
diff --git a/test/window8.tcl b/test/window8.tcl
index 64fd98aea..d881fd8e4 100644
--- a/test/window8.tcl
+++ b/test/window8.tcl
@@ -247,6 +247,18 @@ execsql_test 4.4.4 {
   ) FROM t1 ORDER BY 1 NULLS LAST;
 }
 
+execsql_test 4.5.1 {
+  SELECT sum(b) OVER (
+    ORDER BY a ASC  NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
+  ) FROM t1 ORDER BY 1 NULLS LAST;
+}
+execsql_test 4.5.2 {
+  SELECT sum(b) OVER (
+    ORDER BY a DESC NULLS FIRST RANGE 
+    BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
+  ) FROM t1 ORDER BY 1 NULLS LAST;
+}
+
 ==========
 
 execsql_test 5.0 {
@@ -329,6 +341,7 @@ execsql_test 6.2 {
   FROM t2
 }
 
+==========
 
 
 finish_test
diff --git a/test/window8.test b/test/window8.test
index e149979cd..53d3e3002 100644
--- a/test/window8.test
+++ b/test/window8.test
@@ -3579,6 +3579,19 @@ do_execsql_test 4.4.4 {
   ) FROM t1 ORDER BY 1 NULLS LAST;
 } {5   6   8   9   10}
 
+do_execsql_test 4.5.1 {
+  SELECT sum(b) OVER (
+    ORDER BY a ASC  NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
+  ) FROM t1 ORDER BY 1 NULLS LAST;
+} {9   9   15   15   15}
+
+do_execsql_test 4.5.2 {
+  SELECT sum(b) OVER (
+    ORDER BY a DESC NULLS FIRST RANGE 
+    BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
+  ) FROM t1 ORDER BY 1 NULLS LAST;
+} {6   6   6   15   15}
+
 #==========================================================================
 
 do_execsql_test 5.0 {
@@ -6190,4 +6203,6 @@ do_execsql_test 6.2 {
   FROM t2
 } {{}   A.B   A.B}
 
+#==========================================================================
+
 finish_test