/ Check-in [90046456]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add further comments to window.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 900464567b2a5ce0c278a3297e4be1968f609258608aa0fbe2fc67709aa22a4b
User & Date: dan 2019-08-30 17:28:55
Context
2019-08-30
18:02
Back out the change at [47cd634c98b502d4] which was incorrect. Add a test case so that we don't accidently back out that change again. check-in: 596ac2a4 user: drh tags: trunk
17:28
Add further comments to window.c. check-in: 90046456 user: dan tags: trunk
16:46
New test cases for window functions with RANGE BETWEEN and DESC NULLS FIRST. check-in: f7002f86 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

  1495   1495         if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  1496   1496       }
  1497   1497     }
  1498   1498   }
  1499   1499   
  1500   1500   typedef struct WindowCodeArg WindowCodeArg;
  1501   1501   typedef struct WindowCsrAndReg WindowCsrAndReg;
         1502  +
         1503  +/*
         1504  +** See comments above struct WindowCodeArg.
         1505  +*/
  1502   1506   struct WindowCsrAndReg {
  1503         -  int csr;
  1504         -  int reg;
         1507  +  int csr;                        /* Cursor number */
         1508  +  int reg;                        /* First in array of peer values */
  1505   1509   };
  1506   1510   
         1511  +/*
         1512  +** A single instance of this structure is allocated on the stack by 
         1513  +** sqlite3WindowCodeStep() and a pointer to it passed to the various helper
         1514  +** routines. This is to reduce the number of arguments required by each
         1515  +** helper function.
         1516  +**
         1517  +** regArg:
         1518  +**   Each window function requires an accumulator register (just as an
         1519  +**   ordinary aggregate function does). This variable is set to the first
         1520  +**   in an array of accumulator registers - one for each window function
         1521  +**   in the WindowCodeArg.pMWin list.
         1522  +**
         1523  +** eDelete:
         1524  +**   The window functions implementation sometimes caches the input rows
         1525  +**   that it processes in a temporary table. If it is not zero, this
         1526  +**   variable indicates when rows may be removed from the temp table (in
         1527  +**   order to reduce memory requirements - it would always be safe just
         1528  +**   to leave them there). Possible values for eDelete are:
         1529  +**
         1530  +**      WINDOW_RETURN_ROW:
         1531  +**        An input row can be discarded after it is returned to the caller.
         1532  +**
         1533  +**      WINDOW_AGGINVERSE:
         1534  +**        An input row can be discarded after the window functions xInverse()
         1535  +**        callbacks have been invoked in it.
         1536  +**
         1537  +**      WINDOW_AGGSTEP:
         1538  +**        An input row can be discarded after the window functions xStep()
         1539  +**        callbacks have been invoked in it.
         1540  +**
         1541  +** start,current,end
         1542  +**   Consider a window-frame similar to the following:
         1543  +**
         1544  +**     (ORDER BY a, b GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
         1545  +**
         1546  +**   The windows functions implmentation caches the input rows in a temp
         1547  +**   table, sorted by "a, b" (it actually populates the cache lazily, and
         1548  +**   aggressively removes rows once they are no longer required, but that's
         1549  +**   a mere detail). It keeps three cursors open on the temp table. One
         1550  +**   (current) that points to the next row to return to the query engine
         1551  +**   once its window function values have been calculated. Another (end)
         1552  +**   points to the next row to call the xStep() method of each window function
         1553  +**   on (so that it is 2 groups ahead of current). And a third (start) that
         1554  +**   points to the next row to call the xInverse() method of each window
         1555  +**   function on.
         1556  +**
         1557  +**   Each cursor (start, current and end) consists of a VDBE cursor
         1558  +**   (WindowCsrAndReg.csr) and an array of registers (starting at
         1559  +**   WindowCodeArg.reg) that always contains a copy of the peer values 
         1560  +**   read from the corresponding cursor.
         1561  +**
         1562  +**   Depending on the window-frame in question, all three cursors may not
         1563  +**   be required. In this case both WindowCodeArg.csr and reg are set to
         1564  +**   0.
         1565  +*/
  1507   1566   struct WindowCodeArg {
  1508         -  Parse *pParse;
  1509         -  Window *pMWin;
  1510         -  Vdbe *pVdbe;
  1511         -  int regGosub;
  1512         -  int addrGosub;
  1513         -  int regArg;
  1514         -  int eDelete;
         1567  +  Parse *pParse;             /* Parse context */
         1568  +  Window *pMWin;             /* First in list of functions being processed */
         1569  +  Vdbe *pVdbe;               /* VDBE object */
         1570  +  int addrGosub;             /* OP_Gosub to this address to return one row */
         1571  +  int regGosub;              /* Register used with OP_Gosub(addrGosub) */
         1572  +  int regArg;                /* First in array of accumulator registers */
         1573  +  int eDelete;               /* See above */
  1515   1574   
  1516   1575     WindowCsrAndReg start;
  1517   1576     WindowCsrAndReg current;
  1518   1577     WindowCsrAndReg end;
  1519   1578   };
  1520   1579   
  1521   1580   /*
................................................................................
  1872   1931   **
  1873   1932   ** A special type of arithmetic is used such that if csr1.peerVal is not
  1874   1933   ** a numeric type (real or integer), then the result of the addition addition
  1875   1934   ** or subtraction is a a copy of csr1.peerVal.
  1876   1935   */
  1877   1936   static void windowCodeRangeTest(
  1878   1937     WindowCodeArg *p, 
  1879         -  int op,                          /* OP_Ge, OP_Gt, or OP_Le */
  1880         -  int csr1,                        /* Cursor number for cursor 1 */
  1881         -  int regVal,                      /* Register containing non-negative number */
  1882         -  int csr2,                        /* Cursor number for cursor 2 */
  1883         -  int lbl                          /* Jump destination if the condition is true */
         1938  +  int op,                         /* OP_Ge, OP_Gt, or OP_Le */
         1939  +  int csr1,                       /* Cursor number for cursor 1 */
         1940  +  int regVal,                     /* Register containing non-negative number */
         1941  +  int csr2,                       /* Cursor number for cursor 2 */
         1942  +  int lbl                         /* Jump destination if condition is true */
  1884   1943   ){
  1885   1944     Parse *pParse = p->pParse;
  1886   1945     Vdbe *v = sqlite3GetVdbe(pParse);
  1887         -  ExprList *pOrderBy = p->pMWin->pOrderBy;  /* ORDER BY clause for this window */
  1888         -  int reg1 = sqlite3GetTempReg(pParse);     /* Register for csr1.peerVal+regVal */
  1889         -  int reg2 = sqlite3GetTempReg(pParse);     /* Regiser for csr2.peerVal */
  1890         -  int regString = ++pParse->nMem;           /* Register for constant value '' */
         1946  +  ExprList *pOrderBy = p->pMWin->pOrderBy;  /* ORDER BY clause for window */
         1947  +  int reg1 = sqlite3GetTempReg(pParse);     /* Reg. for csr1.peerVal+regVal */
         1948  +  int reg2 = sqlite3GetTempReg(pParse);     /* Reg. for csr2.peerVal */
         1949  +  int regString = ++pParse->nMem;           /* Reg. for constant value '' */
  1891   1950     int arith = OP_Add;                       /* OP_Add or OP_Subtract */
  1892   1951     int addrGe;                               /* Jump destination */
  1893   1952   
  1894   1953     assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
  1895   1954     assert( pOrderBy && pOrderBy->nExpr==1 );
  1896   1955     if( pOrderBy->a[0].sortFlags & KEYINFO_ORDER_DESC ){
  1897   1956       switch( op ){