/ Check-in [da03fb43]
Login

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

Overview
Comment:Add new API function sqlite3_create_window_function(), for creating new aggregate window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: da03fb4318fd2613ec5c5b109a3974ac1120c19ed16bed4ca85bbdc4b35c998c
User & Date: dan 2018-06-18 16:55:22
Context
2018-06-18
17:36
Ensure that all four callbacks are provided when registering a window function (otherwise SQLITE_MISUSE is returned). check-in: 5720dcd8 user: dan tags: exp-window-functions
16:55
Add new API function sqlite3_create_window_function(), for creating new aggregate window functions. check-in: da03fb43 user: dan tags: exp-window-functions
2018-06-15
20:46
Add extra OOM test. check-in: ac251f72 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   415    415     $(TOP)/src/test_superlock.c \
   416    416     $(TOP)/src/test_syscall.c \
   417    417     $(TOP)/src/test_tclsh.c \
   418    418     $(TOP)/src/test_tclvar.c \
   419    419     $(TOP)/src/test_thread.c \
   420    420     $(TOP)/src/test_vfs.c \
   421    421     $(TOP)/src/test_windirent.c \
          422  +  $(TOP)/src/test_window.c \
   422    423     $(TOP)/src/test_wsd.c       \
   423    424     $(TOP)/ext/fts3/fts3_term.c \
   424    425     $(TOP)/ext/fts3/fts3_test.c  \
   425    426     $(TOP)/ext/session/test_session.c \
   426    427     $(TOP)/ext/rbu/test_rbu.c 
   427    428   
   428    429   # Statically linked extensions

Changes to Makefile.msc.

  1475   1475     $(TOP)\src\test_superlock.c \
  1476   1476     $(TOP)\src\test_syscall.c \
  1477   1477     $(TOP)\src\test_tclsh.c \
  1478   1478     $(TOP)\src\test_tclvar.c \
  1479   1479     $(TOP)\src\test_thread.c \
  1480   1480     $(TOP)\src\test_vfs.c \
  1481   1481     $(TOP)\src\test_windirent.c \
         1482  +  $(TOP)\src\test_window.c \
  1482   1483     $(TOP)\src\test_wsd.c \
  1483   1484     $(TOP)\ext\fts3\fts3_term.c \
  1484   1485     $(TOP)\ext\fts3\fts3_test.c \
  1485   1486     $(TOP)\ext\rbu\test_rbu.c \
  1486   1487     $(TOP)\ext\session\test_session.c
  1487   1488   
  1488   1489   # Statically linked extensions.

Changes to main.mk.

   345    345     $(TOP)/src/test_superlock.c \
   346    346     $(TOP)/src/test_syscall.c \
   347    347     $(TOP)/src/test_tclsh.c \
   348    348     $(TOP)/src/test_tclvar.c \
   349    349     $(TOP)/src/test_thread.c \
   350    350     $(TOP)/src/test_vfs.c \
   351    351     $(TOP)/src/test_windirent.c \
          352  +  $(TOP)/src/test_window.c \
   352    353     $(TOP)/src/test_wsd.c
   353    354   
   354    355   # Extensions to be statically loaded.
   355    356   #
   356    357   TESTSRC += \
   357    358     $(TOP)/ext/misc/amatch.c \
   358    359     $(TOP)/ext/misc/carray.c \

Changes to src/func.c.

  1767   1767   void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
  1768   1768     struct compareInfo *pInfo;
  1769   1769     if( caseSensitive ){
  1770   1770       pInfo = (struct compareInfo*)&likeInfoAlt;
  1771   1771     }else{
  1772   1772       pInfo = (struct compareInfo*)&likeInfoNorm;
  1773   1773     }
  1774         -  sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
  1775         -  sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
         1774  +  sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0);
         1775  +  sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0);
  1776   1776     sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, 
  1777         -      (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0);
         1777  +      (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0, 0, 0);
  1778   1778     setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
  1779   1779     setLikeOptFlag(db, "like", 
  1780   1780         caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
  1781   1781   }
  1782   1782   
  1783   1783   /*
  1784   1784   ** pExpr points to an expression which implements a function.  If

Changes to src/main.c.

  1679   1679     const char *zFunctionName,
  1680   1680     int nArg,
  1681   1681     int enc,
  1682   1682     void *pUserData,
  1683   1683     void (*xSFunc)(sqlite3_context*,int,sqlite3_value **),
  1684   1684     void (*xStep)(sqlite3_context*,int,sqlite3_value **),
  1685   1685     void (*xFinal)(sqlite3_context*),
         1686  +  void (*xValue)(sqlite3_context*),
         1687  +  void (*xInverse)(sqlite3_context*,int,sqlite3_value **),
  1686   1688     FuncDestructor *pDestructor
  1687   1689   ){
  1688   1690     FuncDef *p;
  1689   1691     int nName;
  1690   1692     int extraFlags;
  1691   1693   
  1692   1694     assert( sqlite3_mutex_held(db->mutex) );
................................................................................
  1712   1714     ** to the hash table.
  1713   1715     */
  1714   1716     if( enc==SQLITE_UTF16 ){
  1715   1717       enc = SQLITE_UTF16NATIVE;
  1716   1718     }else if( enc==SQLITE_ANY ){
  1717   1719       int rc;
  1718   1720       rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF8|extraFlags,
  1719         -         pUserData, xSFunc, xStep, xFinal, pDestructor);
         1721  +         pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor);
  1720   1722       if( rc==SQLITE_OK ){
  1721   1723         rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF16LE|extraFlags,
  1722         -          pUserData, xSFunc, xStep, xFinal, pDestructor);
         1724  +          pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor);
  1723   1725       }
  1724   1726       if( rc!=SQLITE_OK ){
  1725   1727         return rc;
  1726   1728       }
  1727   1729       enc = SQLITE_UTF16BE;
  1728   1730     }
  1729   1731   #else
................................................................................
  1761   1763       pDestructor->nRef++;
  1762   1764     }
  1763   1765     p->u.pDestructor = pDestructor;
  1764   1766     p->funcFlags = (p->funcFlags & SQLITE_FUNC_ENCMASK) | extraFlags;
  1765   1767     testcase( p->funcFlags & SQLITE_DETERMINISTIC );
  1766   1768     p->xSFunc = xSFunc ? xSFunc : xStep;
  1767   1769     p->xFinalize = xFinal;
         1770  +  p->xValue = xValue;
         1771  +  p->xInverse = xInverse;
  1768   1772     p->pUserData = pUserData;
  1769   1773     p->nArg = (u16)nArg;
  1770   1774     return SQLITE_OK;
  1771   1775   }
  1772   1776   
  1773   1777   /*
  1774         -** Create new user functions.
         1778  +** Worker function used by utf-8 APIs that create new functions:
         1779  +**
         1780  +**    sqlite3_create_function()
         1781  +**    sqlite3_create_function_v2()
         1782  +**    sqlite3_create_window_function()
  1775   1783   */
  1776         -int sqlite3_create_function(
         1784  +static int createFunctionApi(
  1777   1785     sqlite3 *db,
  1778   1786     const char *zFunc,
  1779   1787     int nArg,
  1780   1788     int enc,
  1781   1789     void *p,
  1782         -  void (*xSFunc)(sqlite3_context*,int,sqlite3_value **),
  1783         -  void (*xStep)(sqlite3_context*,int,sqlite3_value **),
  1784         -  void (*xFinal)(sqlite3_context*)
  1785         -){
  1786         -  return sqlite3_create_function_v2(db, zFunc, nArg, enc, p, xSFunc, xStep,
  1787         -                                    xFinal, 0);
  1788         -}
  1789         -
  1790         -int sqlite3_create_function_v2(
  1791         -  sqlite3 *db,
  1792         -  const char *zFunc,
  1793         -  int nArg,
  1794         -  int enc,
  1795         -  void *p,
  1796         -  void (*xSFunc)(sqlite3_context*,int,sqlite3_value **),
  1797         -  void (*xStep)(sqlite3_context*,int,sqlite3_value **),
         1790  +  void (*xSFunc)(sqlite3_context*,int,sqlite3_value**),
         1791  +  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  1798   1792     void (*xFinal)(sqlite3_context*),
  1799         -  void (*xDestroy)(void *)
         1793  +  void (*xValue)(sqlite3_context*),
         1794  +  void (*xInverse)(sqlite3_context*,int,sqlite3_value**),
         1795  +  void(*xDestroy)(void*)
  1800   1796   ){
  1801   1797     int rc = SQLITE_ERROR;
  1802   1798     FuncDestructor *pArg = 0;
  1803   1799   
  1804   1800   #ifdef SQLITE_ENABLE_API_ARMOR
  1805   1801     if( !sqlite3SafetyCheckOk(db) ){
  1806   1802       return SQLITE_MISUSE_BKPT;
................................................................................
  1814   1810         xDestroy(p);
  1815   1811         goto out;
  1816   1812       }
  1817   1813       pArg->nRef = 0;
  1818   1814       pArg->xDestroy = xDestroy;
  1819   1815       pArg->pUserData = p;
  1820   1816     }
  1821         -  rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, xSFunc, xStep, xFinal, pArg);
         1817  +  rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, 
         1818  +      xSFunc, xStep, xFinal, xValue, xInverse, pArg
         1819  +  );
  1822   1820     if( pArg && pArg->nRef==0 ){
  1823   1821       assert( rc!=SQLITE_OK );
  1824   1822       xDestroy(p);
  1825   1823       sqlite3_free(pArg);
  1826   1824     }
  1827   1825   
  1828   1826    out:
  1829   1827     rc = sqlite3ApiExit(db, rc);
  1830   1828     sqlite3_mutex_leave(db->mutex);
  1831   1829     return rc;
  1832   1830   }
         1831  +
         1832  +/*
         1833  +** Create new user functions.
         1834  +*/
         1835  +int sqlite3_create_function(
         1836  +  sqlite3 *db,
         1837  +  const char *zFunc,
         1838  +  int nArg,
         1839  +  int enc,
         1840  +  void *p,
         1841  +  void (*xSFunc)(sqlite3_context*,int,sqlite3_value **),
         1842  +  void (*xStep)(sqlite3_context*,int,sqlite3_value **),
         1843  +  void (*xFinal)(sqlite3_context*)
         1844  +){
         1845  +  return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep,
         1846  +                                    xFinal, 0, 0, 0);
         1847  +}
         1848  +int sqlite3_create_function_v2(
         1849  +  sqlite3 *db,
         1850  +  const char *zFunc,
         1851  +  int nArg,
         1852  +  int enc,
         1853  +  void *p,
         1854  +  void (*xSFunc)(sqlite3_context*,int,sqlite3_value **),
         1855  +  void (*xStep)(sqlite3_context*,int,sqlite3_value **),
         1856  +  void (*xFinal)(sqlite3_context*),
         1857  +  void (*xDestroy)(void *)
         1858  +){
         1859  +  return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep,
         1860  +                                    xFinal, 0, 0, xDestroy);
         1861  +}
         1862  +int sqlite3_create_window_function(
         1863  +  sqlite3 *db,
         1864  +  const char *zFunc,
         1865  +  int nArg,
         1866  +  int enc,
         1867  +  void *p,
         1868  +  void (*xStep)(sqlite3_context*,int,sqlite3_value **),
         1869  +  void (*xFinal)(sqlite3_context*),
         1870  +  void (*xValue)(sqlite3_context*),
         1871  +  void (*xInverse)(sqlite3_context*,int,sqlite3_value **),
         1872  +  void (*xDestroy)(void *)
         1873  +){
         1874  +  return createFunctionApi(db, zFunc, nArg, enc, p, 0, xStep,
         1875  +                                    xFinal, xValue, xInverse, xDestroy);
         1876  +}
  1833   1877   
  1834   1878   #ifndef SQLITE_OMIT_UTF16
  1835   1879   int sqlite3_create_function16(
  1836   1880     sqlite3 *db,
  1837   1881     const void *zFunctionName,
  1838   1882     int nArg,
  1839   1883     int eTextRep,
................................................................................
  1847   1891   
  1848   1892   #ifdef SQLITE_ENABLE_API_ARMOR
  1849   1893     if( !sqlite3SafetyCheckOk(db) || zFunctionName==0 ) return SQLITE_MISUSE_BKPT;
  1850   1894   #endif
  1851   1895     sqlite3_mutex_enter(db->mutex);
  1852   1896     assert( !db->mallocFailed );
  1853   1897     zFunc8 = sqlite3Utf16to8(db, zFunctionName, -1, SQLITE_UTF16NATIVE);
  1854         -  rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0);
         1898  +  rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0,0,0);
  1855   1899     sqlite3DbFree(db, zFunc8);
  1856   1900     rc = sqlite3ApiExit(db, rc);
  1857   1901     sqlite3_mutex_leave(db->mutex);
  1858   1902     return rc;
  1859   1903   }
  1860   1904   #endif
  1861   1905   

Changes to src/sqlite.h.in.

  4736   4736     int nArg,
  4737   4737     int eTextRep,
  4738   4738     void *pApp,
  4739   4739     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  4740   4740     void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  4741   4741     void (*xFinal)(sqlite3_context*),
  4742   4742     void(*xDestroy)(void*)
         4743  +);
         4744  +int sqlite3_create_window_function(
         4745  +  sqlite3 *db,
         4746  +  const char *zFunctionName,
         4747  +  int nArg,
         4748  +  int eTextRep,
         4749  +  void *pApp,
         4750  +  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
         4751  +  void (*xFinal)(sqlite3_context*),
         4752  +  void (*xValue)(sqlite3_context*),
         4753  +  void (*xInverse)(sqlite3_context*,int,sqlite3_value**),
         4754  +  void(*xDestroy)(void*)
  4743   4755   );
  4744   4756   
  4745   4757   /*
  4746   4758   ** CAPI3REF: Text Encodings
  4747   4759   **
  4748   4760   ** These constant define integer codes that represent the various
  4749   4761   ** text encodings supported by SQLite.

Changes to src/sqliteInt.h.

  4240   4240   KeyInfo *sqlite3KeyInfoFromExprList(Parse*, ExprList*, int, int);
  4241   4241   
  4242   4242   #ifdef SQLITE_DEBUG
  4243   4243   int sqlite3KeyInfoIsWriteable(KeyInfo*);
  4244   4244   #endif
  4245   4245   int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *,
  4246   4246     void (*)(sqlite3_context*,int,sqlite3_value **),
  4247         -  void (*)(sqlite3_context*,int,sqlite3_value **), void (*)(sqlite3_context*),
         4247  +  void (*)(sqlite3_context*,int,sqlite3_value **), 
         4248  +  void (*)(sqlite3_context*),
         4249  +  void (*)(sqlite3_context*),
         4250  +  void (*)(sqlite3_context*,int,sqlite3_value **), 
  4248   4251     FuncDestructor *pDestructor
  4249   4252   );
  4250   4253   void sqlite3NoopDestructor(void*);
  4251   4254   void sqlite3OomFault(sqlite3*);
  4252   4255   void sqlite3OomClear(sqlite3*);
  4253   4256   int sqlite3ApiExit(sqlite3 *db, int);
  4254   4257   int sqlite3OpenTempDatabase(Parse *);

Changes to src/test_tclsh.c.

   101    101   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
   102    102     extern int Sqlitetestfts3_Init(Tcl_Interp *interp);
   103    103   #endif
   104    104   #ifdef SQLITE_ENABLE_ZIPVFS
   105    105     extern int Zipvfs_Init(Tcl_Interp*);
   106    106   #endif
   107    107     extern int TestExpert_Init(Tcl_Interp*);
          108  +  extern int Sqlitetest_window_Init(Tcl_Interp *);
   108    109   
   109    110     Tcl_CmdInfo cmdInfo;
   110    111   
   111    112     /* Since the primary use case for this binary is testing of SQLite,
   112    113     ** be sure to generate core files if we crash */
   113    114   #if defined(unix)
   114    115     { struct rlimit x;
................................................................................
   165    166     SqliteRbu_Init(interp);
   166    167     Sqlitetesttcl_Init(interp);
   167    168   
   168    169   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
   169    170     Sqlitetestfts3_Init(interp);
   170    171   #endif
   171    172     TestExpert_Init(interp);
          173  +  Sqlitetest_window_Init(interp);
   172    174   
   173    175     Tcl_CreateObjCommand(
   174    176         interp, "load_testfixture_extensions", load_testfixture_extensions,0,0
   175    177     );
   176    178     return 0;
   177    179   }
   178    180   

Added src/test_window.c.

            1  +/*
            2  +** 2018 June 17
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +#include "sqlite3.h"
           15  +
           16  +#ifdef SQLITE_TEST
           17  +
           18  +#include "sqliteInt.h"
           19  +#include <tcl.h>
           20  +
           21  +extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb);
           22  +extern const char *sqlite3ErrName(int);
           23  +
           24  +typedef struct TestWindow TestWindow;
           25  +struct TestWindow {
           26  +  Tcl_Obj *xStep;
           27  +  Tcl_Obj *xFinal;
           28  +  Tcl_Obj *xValue;
           29  +  Tcl_Obj *xInverse;
           30  +  Tcl_Interp *interp;
           31  +};
           32  +
           33  +typedef struct TestWindowCtx TestWindowCtx;
           34  +struct TestWindowCtx {
           35  +  Tcl_Obj *pVal;
           36  +};
           37  +
           38  +static void doTestWindowStep(
           39  +  int bInverse,
           40  +  sqlite3_context *ctx, 
           41  +  int nArg, 
           42  +  sqlite3_value **apArg
           43  +){
           44  +  int i;
           45  +  TestWindow *p = (TestWindow*)sqlite3_user_data(ctx);
           46  +  Tcl_Obj *pEval = Tcl_DuplicateObj(bInverse ? p->xInverse : p->xStep);
           47  +  TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx));
           48  +
           49  +  Tcl_IncrRefCount(pEval);
           50  +  if( pCtx ){
           51  +    const char *zResult;
           52  +    int rc;
           53  +    if( pCtx->pVal ){
           54  +      Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal));
           55  +    }else{
           56  +      Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1));
           57  +    }
           58  +    for(i=0; i<nArg; i++){
           59  +      Tcl_Obj *pArg;
           60  +      pArg = Tcl_NewStringObj((const char*)sqlite3_value_text(apArg[i]), -1);
           61  +      Tcl_ListObjAppendElement(p->interp, pEval, pArg);
           62  +    }
           63  +    rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL);
           64  +    if( rc!=TCL_OK ){
           65  +      zResult = Tcl_GetStringResult(p->interp);
           66  +      sqlite3_result_error(ctx, zResult, -1);
           67  +    }else{
           68  +      if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal);
           69  +      pCtx->pVal = Tcl_DuplicateObj(Tcl_GetObjResult(p->interp));
           70  +      Tcl_IncrRefCount(pCtx->pVal);
           71  +    }
           72  +  }
           73  +  Tcl_DecrRefCount(pEval);
           74  +}
           75  +
           76  +static void doTestWindowFinalize(int bValue, sqlite3_context *ctx){
           77  +  TestWindow *p = (TestWindow*)sqlite3_user_data(ctx);
           78  +  Tcl_Obj *pEval = Tcl_DuplicateObj(bValue ? p->xValue : p->xFinal);
           79  +  TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx));
           80  +
           81  +  Tcl_IncrRefCount(pEval);
           82  +  if( pCtx ){
           83  +    const char *zResult;
           84  +    int rc;
           85  +    if( pCtx->pVal ){
           86  +      Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal));
           87  +    }else{
           88  +      Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1));
           89  +    }
           90  +
           91  +    rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL);
           92  +    zResult = Tcl_GetStringResult(p->interp);
           93  +    if( rc!=TCL_OK ){
           94  +      sqlite3_result_error(ctx, zResult, -1);
           95  +    }else{
           96  +      sqlite3_result_text(ctx, zResult, -1, SQLITE_TRANSIENT);
           97  +    }
           98  +
           99  +    if( bValue==0 ){
          100  +      if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal);
          101  +      pCtx->pVal = 0;
          102  +    }
          103  +  }
          104  +  Tcl_DecrRefCount(pEval);
          105  +}
          106  +
          107  +static void testWindowStep(
          108  +  sqlite3_context *ctx, 
          109  +  int nArg, 
          110  +  sqlite3_value **apArg
          111  +){
          112  +  doTestWindowStep(0, ctx, nArg, apArg);
          113  +}
          114  +static void testWindowInverse(
          115  +  sqlite3_context *ctx, 
          116  +  int nArg, 
          117  +  sqlite3_value **apArg
          118  +){
          119  +  doTestWindowStep(1, ctx, nArg, apArg);
          120  +}
          121  +
          122  +static void testWindowFinal(sqlite3_context *ctx){
          123  +  doTestWindowFinalize(0, ctx);
          124  +}
          125  +static void testWindowValue(sqlite3_context *ctx){
          126  +  doTestWindowFinalize(1, ctx);
          127  +}
          128  +
          129  +static void testWindowDestroy(void *pCtx){
          130  +  ckfree(pCtx);
          131  +}
          132  +
          133  +/*
          134  +** Usage: sqlite3_create_window_function DB NAME XSTEP XFINAL XVALUE XINVERSE
          135  +*/
          136  +static int SQLITE_TCLAPI test_create_window(
          137  +  void * clientData,
          138  +  Tcl_Interp *interp,
          139  +  int objc,
          140  +  Tcl_Obj *CONST objv[]
          141  +){
          142  +  TestWindow *pNew;
          143  +  sqlite3 *db;
          144  +  const char *zName;
          145  +  int rc;
          146  +
          147  +  if( objc!=7 ){
          148  +    Tcl_WrongNumArgs(interp, 1, objv, "DB NAME XSTEP XFINAL XVALUE XINVERSE");
          149  +    return TCL_ERROR;
          150  +  }
          151  +
          152  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
          153  +  zName = Tcl_GetString(objv[2]);
          154  +  pNew = ckalloc(sizeof(TestWindow));
          155  +  memset(pNew, 0, sizeof(TestWindow));
          156  +  pNew->xStep = Tcl_DuplicateObj(objv[3]);
          157  +  pNew->xFinal = Tcl_DuplicateObj(objv[4]);
          158  +  pNew->xValue = Tcl_DuplicateObj(objv[5]);
          159  +  pNew->xInverse = Tcl_DuplicateObj(objv[6]);
          160  +  pNew->interp = interp;
          161  +
          162  +  Tcl_IncrRefCount(pNew->xStep);
          163  +  Tcl_IncrRefCount(pNew->xFinal);
          164  +  Tcl_IncrRefCount(pNew->xValue);
          165  +  Tcl_IncrRefCount(pNew->xInverse);
          166  +
          167  +  rc = sqlite3_create_window_function(db, zName, -1, SQLITE_UTF8, (void*)pNew,
          168  +      testWindowStep, testWindowFinal, testWindowValue, testWindowInverse,
          169  +      testWindowDestroy
          170  +  );
          171  +  if( rc!=SQLITE_OK ){
          172  +    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          173  +    return TCL_ERROR;
          174  +  }
          175  +
          176  +  return TCL_OK;
          177  +}
          178  +
          179  +int Sqlitetest_window_Init(Tcl_Interp *interp){
          180  +  static struct {
          181  +     char *zName;
          182  +     Tcl_ObjCmdProc *xProc;
          183  +     int clientData;
          184  +  } aObjCmd[] = {
          185  +     { "sqlite3_create_window_function", test_create_window, 0 },
          186  +  };
          187  +  int i;
          188  +  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
          189  +    ClientData c = (ClientData)SQLITE_INT_TO_PTR(aObjCmd[i].clientData);
          190  +    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, c, 0);
          191  +  }
          192  +  return TCL_OK;
          193  +}
          194  +#endif

Changes to src/vdbe.c.

  6410   6410   case OP_AggFinal: {
  6411   6411     Mem *pMem;
  6412   6412     assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
  6413   6413     pMem = &aMem[pOp->p1];
  6414   6414     assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );
  6415   6415     if( pOp->p3 ){
  6416   6416       rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc);
         6417  +    pMem = &aMem[pOp->p3];
  6417   6418     }else{
  6418   6419       rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc);
  6419   6420     }
  6420   6421     if( rc ){
  6421   6422       sqlite3VdbeError(p, "%s", sqlite3_value_text(pMem));
  6422   6423       goto abort_due_to_error;
  6423   6424     }

Changes to src/window.c.

   296    296     int nArg,
   297    297     sqlite3_value **apArg
   298    298   ){
   299    299   }
   300    300   static void cume_distValueFunc(sqlite3_context *pCtx){
   301    301     struct CallCount *p;
   302    302     p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   303         -  if( p ){
          303  +  if( p && p->nTotal ){
   304    304       double r = (double)(p->nStep) / (double)(p->nTotal);
   305    305       sqlite3_result_double(pCtx, r);
   306    306     }
   307    307   }
   308    308   
   309    309   /*
   310    310   ** Context object for ntile() window function.
................................................................................
   680    680   ** are invoked in the correct order as described under "SELECT REWRITING"
   681    681   ** at the top of this file.
   682    682   */
   683    683   int sqlite3WindowRewrite(Parse *pParse, Select *p){
   684    684     int rc = SQLITE_OK;
   685    685     if( p->pWin ){
   686    686       Vdbe *v = sqlite3GetVdbe(pParse);
   687         -    int i;
   688    687       sqlite3 *db = pParse->db;
   689    688       Select *pSub = 0;             /* The subquery */
   690    689       SrcList *pSrc = p->pSrc;
   691    690       Expr *pWhere = p->pWhere;
   692    691       ExprList *pGroupBy = p->pGroupBy;
   693    692       Expr *pHaving = p->pHaving;
   694    693       ExprList *pSort = 0;
................................................................................
   739    738   
   740    739       pSub = sqlite3SelectNew(
   741    740           pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
   742    741       );
   743    742       p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
   744    743       assert( p->pSrc || db->mallocFailed );
   745    744       if( p->pSrc ){
   746         -      int iTab;
   747         -      ExprList *pList = 0;
   748    745         p->pSrc->a[0].pSelect = pSub;
   749    746         sqlite3SrcListAssignCursors(pParse, p->pSrc);
   750    747         if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
   751    748           rc = SQLITE_NOMEM;
   752    749         }else{
   753    750           pSub->selFlags |= SF_Expanded;
   754    751           p->selFlags &= ~SF_Aggregate;
................................................................................
  1084   1081     WhereInfo *pWInfo,              /* WhereInfo to call WhereEnd() on */
  1085   1082     int regFlushPart,               /* Register to use with Gosub lblFlushPart */
  1086   1083     int lblFlushPart,               /* Subroutine to Gosub to */
  1087   1084     int *pRegSize                   /* OUT: Register containing partition size */
  1088   1085   ){
  1089   1086     Window *pMWin = p->pWin;
  1090   1087     Vdbe *v = sqlite3GetVdbe(pParse);
  1091         -  Window *pWin;
  1092   1088     int iSubCsr = p->pSrc->a[0].iCursor;
  1093   1089     int nSub = p->pSrc->a[0].pTab->nCol;
  1094   1090     int k;
  1095   1091   
  1096   1092     int reg = pParse->nMem+1;
  1097   1093     int regRecord = reg+nSub;
  1098   1094     int regRowid = regRecord+1;
................................................................................
  1406   1402     Select *p,
  1407   1403     WhereInfo *pWInfo,
  1408   1404     int regGosub, 
  1409   1405     int addrGosub
  1410   1406   ){
  1411   1407     Window *pMWin = p->pWin;
  1412   1408     Vdbe *v = sqlite3GetVdbe(pParse);
  1413         -  Window *pWin;
  1414         -  int k;
  1415         -  int nSub = p->pSrc->a[0].pTab->nCol;
  1416   1409     int regFlushPart;               /* Register for "Gosub flush_partition" */
  1417   1410     int lblFlushPart;               /* Label for "Gosub flush_partition" */
  1418   1411     int lblFlushDone;               /* Label for "Gosub flush_partition_done" */
  1419   1412   
  1420   1413     int regArg;
  1421         -  int nArg;
  1422   1414     int addr;
  1423   1415     int csrStart = pParse->nTab++;
  1424   1416     int csrEnd = pParse->nTab++;
  1425   1417     int regStart;                    /* Value of <expr> PRECEDING */
  1426   1418     int regEnd;                      /* Value of <expr> FOLLOWING */
  1427         -  int addrNext;
  1428   1419     int addrGoto;
  1429   1420     int addrTop;
  1430   1421     int addrIfPos1;
  1431   1422     int addrIfPos2;
  1432         -
  1433         -  int regPeer = 0;                 /* Number of peers in current group */
  1434         -  int regPeerVal = 0;              /* Array of values identifying peer group */
  1435         -  int iPeer = 0;                   /* Column offset in eph-table of peer vals */
  1436         -  int nPeerVal;                    /* Number of peer values */
  1437   1423     int regSize = 0;
  1438   1424   
  1439   1425     assert( pMWin->eStart==TK_PRECEDING 
  1440   1426          || pMWin->eStart==TK_CURRENT 
  1441   1427          || pMWin->eStart==TK_FOLLOWING 
  1442   1428          || pMWin->eStart==TK_UNBOUNDED 
  1443   1429     );
................................................................................
  1675   1661     Select *p,
  1676   1662     WhereInfo *pWInfo,
  1677   1663     int regGosub, 
  1678   1664     int addrGosub
  1679   1665   ){
  1680   1666     Window *pMWin = p->pWin;
  1681   1667     Vdbe *v = sqlite3GetVdbe(pParse);
  1682         -  Window *pWin;
  1683   1668     int k;
  1684   1669     int addr;
  1685   1670     ExprList *pPart = pMWin->pPartition;
  1686   1671     ExprList *pOrderBy = pMWin->pOrderBy;
  1687   1672     int nPeer = pOrderBy ? pOrderBy->nExpr : 0;
  1688   1673     int regNewPeer;
  1689   1674   
................................................................................
  1691   1676     int addrNext;                   /* Jump here for next iteration of loop */
  1692   1677     int regFlushPart;
  1693   1678     int lblFlushPart;
  1694   1679     int csrLead;
  1695   1680     int regCtr;
  1696   1681     int regArg;                     /* Register array to martial function args */
  1697   1682     int regSize;
  1698         -  int nArg;
  1699   1683     int lblEmpty;
  1700   1684     int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT 
  1701   1685             && pMWin->eEnd==TK_UNBOUNDED;
  1702   1686   
  1703   1687     assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 
  1704   1688          || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 
  1705   1689          || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) 
................................................................................
  1818   1802     Select *p,
  1819   1803     WhereInfo *pWInfo,
  1820   1804     int regGosub, 
  1821   1805     int addrGosub
  1822   1806   ){
  1823   1807     Window *pMWin = p->pWin;
  1824   1808     Vdbe *v = sqlite3GetVdbe(pParse);
  1825         -  Window *pWin;
  1826   1809     int k;
  1827   1810     int iSubCsr = p->pSrc->a[0].iCursor;
  1828   1811     int nSub = p->pSrc->a[0].pTab->nCol;
  1829   1812     int reg = pParse->nMem+1;
  1830   1813     int regRecord = reg+nSub;
  1831   1814     int regRowid = regRecord+1;
  1832   1815     int addr;

Changes to test/window1.test.

   265    265     4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
   266    266   }
   267    267   
   268    268   do_execsql_test 7.3 {
   269    269     SELECT row_number() OVER (ORDER BY x) FROM t1
   270    270   } {1 2 3 4 5}
   271    271   
          272  +do_execsql_test 7.4 {
          273  +  SELECT 
          274  +    row_number() OVER win,
          275  +    lead(x) OVER win
          276  +  FROM t1
          277  +  WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          278  +} {1 3  2 5  3 7  4 9   5 {}}
   272    279   
   273    280   finish_test
   274    281   

Added test/window5.test.

            1  +# 2018 May 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. Specifically,
           12  +# it tests the sqlite3_create_window_function() API.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix window1
           18  +
           19  +proc m_step {ctx val} {
           20  +  lappend ctx $val
           21  +  return $ctx
           22  +}
           23  +proc m_value {ctx} {
           24  +  set lSort [lsort $ctx]
           25  +
           26  +  set nVal [llength $lSort]
           27  +  set n [expr $nVal/2]
           28  +  
           29  +  if {($nVal % 2)==0 && $nVal>0} {
           30  +    set a [lindex $lSort $n]
           31  +    set b [lindex $lSort $n-1]
           32  +    if {($a+$b) % 2} {
           33  +      set ret [expr ($a+$b)/2.0]
           34  +    } else {
           35  +      set ret [expr ($a+$b)/2]
           36  +    }
           37  +  } else {
           38  +    set ret [lindex $lSort $n]
           39  +  }
           40  +  return $ret
           41  +}
           42  +proc m_inverse {ctx val} {
           43  +  set ctx [lrange $ctx 1 end]
           44  +  return $ctx
           45  +}
           46  +proc w_value {ctx} {
           47  +  lsort $ctx
           48  +}
           49  +
           50  +sqlite3_create_window_function db median m_step m_value m_value m_inverse
           51  +sqlite3_create_window_function db win m_step w_value w_value m_inverse
           52  +
           53  +do_execsql_test 1.0 {
           54  +  CREATE TABLE t1(a, b);
           55  +  INSERT INTO t1 VALUES(4, 'a');
           56  +  INSERT INTO t1 VALUES(6, 'b');
           57  +  INSERT INTO t1 VALUES(1, 'c');
           58  +  INSERT INTO t1 VALUES(5, 'd');
           59  +  INSERT INTO t1 VALUES(2, 'e');
           60  +  INSERT INTO t1 VALUES(3, 'f');
           61  +}
           62  +
           63  +do_execsql_test 1.1 {
           64  +  SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1;
           65  +} {4 4  {4 6} 5  {1 4 6} 4  {1 4 5 6} 4.5  {1 2 4 5 6} 4 {1 2 3 4 5 6} 3.5}
           66  +
           67  +finish_test
           68  +