Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -417,10 +417,11 @@ $(TOP)/src/test_tclsh.c \ $(TOP)/src/test_tclvar.c \ $(TOP)/src/test_thread.c \ $(TOP)/src/test_vfs.c \ $(TOP)/src/test_windirent.c \ + $(TOP)/src/test_window.c \ $(TOP)/src/test_wsd.c \ $(TOP)/ext/fts3/fts3_term.c \ $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/session/test_session.c \ $(TOP)/ext/rbu/test_rbu.c Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -1477,10 +1477,11 @@ $(TOP)\src\test_tclsh.c \ $(TOP)\src\test_tclvar.c \ $(TOP)\src\test_thread.c \ $(TOP)\src\test_vfs.c \ $(TOP)\src\test_windirent.c \ + $(TOP)\src\test_window.c \ $(TOP)\src\test_wsd.c \ $(TOP)\ext\fts3\fts3_term.c \ $(TOP)\ext\fts3\fts3_test.c \ $(TOP)\ext\rbu\test_rbu.c \ $(TOP)\ext\session\test_session.c Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -347,10 +347,11 @@ $(TOP)/src/test_tclsh.c \ $(TOP)/src/test_tclvar.c \ $(TOP)/src/test_thread.c \ $(TOP)/src/test_vfs.c \ $(TOP)/src/test_windirent.c \ + $(TOP)/src/test_window.c \ $(TOP)/src/test_wsd.c # Extensions to be statically loaded. # TESTSRC += \ Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1769,14 +1769,14 @@ if( caseSensitive ){ pInfo = (struct compareInfo*)&likeInfoAlt; }else{ pInfo = (struct compareInfo*)&likeInfoNorm; } - sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); - sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); + sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0); + sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0); sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, - (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0); + (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0, 0, 0); setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE); setLikeOptFlag(db, "like", caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE); } Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -1681,10 +1681,12 @@ int enc, void *pUserData, void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), void (*xStep)(sqlite3_context*,int,sqlite3_value **), void (*xFinal)(sqlite3_context*), + void (*xValue)(sqlite3_context*), + void (*xInverse)(sqlite3_context*,int,sqlite3_value **), FuncDestructor *pDestructor ){ FuncDef *p; int nName; int extraFlags; @@ -1714,14 +1716,14 @@ if( enc==SQLITE_UTF16 ){ enc = SQLITE_UTF16NATIVE; }else if( enc==SQLITE_ANY ){ int rc; rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF8|extraFlags, - pUserData, xSFunc, xStep, xFinal, pDestructor); + pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor); if( rc==SQLITE_OK ){ rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF16LE|extraFlags, - pUserData, xSFunc, xStep, xFinal, pDestructor); + pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor); } if( rc!=SQLITE_OK ){ return rc; } enc = SQLITE_UTF16BE; @@ -1763,42 +1765,36 @@ p->u.pDestructor = pDestructor; p->funcFlags = (p->funcFlags & SQLITE_FUNC_ENCMASK) | extraFlags; testcase( p->funcFlags & SQLITE_DETERMINISTIC ); p->xSFunc = xSFunc ? xSFunc : xStep; p->xFinalize = xFinal; + p->xValue = xValue; + p->xInverse = xInverse; p->pUserData = pUserData; p->nArg = (u16)nArg; return SQLITE_OK; } /* -** Create new user functions. +** Worker function used by utf-8 APIs that create new functions: +** +** sqlite3_create_function() +** sqlite3_create_function_v2() +** sqlite3_create_window_function() */ -int sqlite3_create_function( +static int createFunctionApi( sqlite3 *db, const char *zFunc, int nArg, int enc, void *p, - void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), - void (*xStep)(sqlite3_context*,int,sqlite3_value **), - void (*xFinal)(sqlite3_context*) -){ - return sqlite3_create_function_v2(db, zFunc, nArg, enc, p, xSFunc, xStep, - xFinal, 0); -} - -int sqlite3_create_function_v2( - sqlite3 *db, - const char *zFunc, - int nArg, - int enc, - void *p, - void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), - void (*xStep)(sqlite3_context*,int,sqlite3_value **), + void (*xSFunc)(sqlite3_context*,int,sqlite3_value**), + void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*), - void (*xDestroy)(void *) + void (*xValue)(sqlite3_context*), + void (*xInverse)(sqlite3_context*,int,sqlite3_value**), + void(*xDestroy)(void*) ){ int rc = SQLITE_ERROR; FuncDestructor *pArg = 0; #ifdef SQLITE_ENABLE_API_ARMOR @@ -1816,11 +1812,13 @@ } pArg->nRef = 0; pArg->xDestroy = xDestroy; pArg->pUserData = p; } - rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, xSFunc, xStep, xFinal, pArg); + rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, + xSFunc, xStep, xFinal, xValue, xInverse, pArg + ); if( pArg && pArg->nRef==0 ){ assert( rc!=SQLITE_OK ); xDestroy(p); sqlite3_free(pArg); } @@ -1828,10 +1826,56 @@ out: rc = sqlite3ApiExit(db, rc); sqlite3_mutex_leave(db->mutex); return rc; } + +/* +** Create new user functions. +*/ +int sqlite3_create_function( + sqlite3 *db, + const char *zFunc, + int nArg, + int enc, + void *p, + void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), + void (*xStep)(sqlite3_context*,int,sqlite3_value **), + void (*xFinal)(sqlite3_context*) +){ + return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep, + xFinal, 0, 0, 0); +} +int sqlite3_create_function_v2( + sqlite3 *db, + const char *zFunc, + int nArg, + int enc, + void *p, + void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), + void (*xStep)(sqlite3_context*,int,sqlite3_value **), + void (*xFinal)(sqlite3_context*), + void (*xDestroy)(void *) +){ + return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep, + xFinal, 0, 0, xDestroy); +} +int sqlite3_create_window_function( + sqlite3 *db, + const char *zFunc, + int nArg, + int enc, + void *p, + void (*xStep)(sqlite3_context*,int,sqlite3_value **), + void (*xFinal)(sqlite3_context*), + void (*xValue)(sqlite3_context*), + void (*xInverse)(sqlite3_context*,int,sqlite3_value **), + void (*xDestroy)(void *) +){ + return createFunctionApi(db, zFunc, nArg, enc, p, 0, xStep, + xFinal, xValue, xInverse, xDestroy); +} #ifndef SQLITE_OMIT_UTF16 int sqlite3_create_function16( sqlite3 *db, const void *zFunctionName, @@ -1849,11 +1893,11 @@ if( !sqlite3SafetyCheckOk(db) || zFunctionName==0 ) return SQLITE_MISUSE_BKPT; #endif sqlite3_mutex_enter(db->mutex); assert( !db->mallocFailed ); zFunc8 = sqlite3Utf16to8(db, zFunctionName, -1, SQLITE_UTF16NATIVE); - rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0); + rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0,0,0); sqlite3DbFree(db, zFunc8); rc = sqlite3ApiExit(db, rc); sqlite3_mutex_leave(db->mutex); return rc; } Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -4738,10 +4738,22 @@ void *pApp, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*), void(*xDestroy)(void*) +); +int sqlite3_create_window_function( + sqlite3 *db, + const char *zFunctionName, + int nArg, + int eTextRep, + void *pApp, + void (*xStep)(sqlite3_context*,int,sqlite3_value**), + void (*xFinal)(sqlite3_context*), + void (*xValue)(sqlite3_context*), + void (*xInverse)(sqlite3_context*,int,sqlite3_value**), + void(*xDestroy)(void*) ); /* ** CAPI3REF: Text Encodings ** Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -4242,11 +4242,14 @@ #ifdef SQLITE_DEBUG int sqlite3KeyInfoIsWriteable(KeyInfo*); #endif int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *, void (*)(sqlite3_context*,int,sqlite3_value **), - void (*)(sqlite3_context*,int,sqlite3_value **), void (*)(sqlite3_context*), + void (*)(sqlite3_context*,int,sqlite3_value **), + void (*)(sqlite3_context*), + void (*)(sqlite3_context*), + void (*)(sqlite3_context*,int,sqlite3_value **), FuncDestructor *pDestructor ); void sqlite3NoopDestructor(void*); void sqlite3OomFault(sqlite3*); void sqlite3OomClear(sqlite3*); Index: src/test_tclsh.c ================================================================== --- src/test_tclsh.c +++ src/test_tclsh.c @@ -103,10 +103,11 @@ #endif #ifdef SQLITE_ENABLE_ZIPVFS extern int Zipvfs_Init(Tcl_Interp*); #endif extern int TestExpert_Init(Tcl_Interp*); + extern int Sqlitetest_window_Init(Tcl_Interp *); Tcl_CmdInfo cmdInfo; /* Since the primary use case for this binary is testing of SQLite, ** be sure to generate core files if we crash */ @@ -167,10 +168,11 @@ #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif TestExpert_Init(interp); + Sqlitetest_window_Init(interp); Tcl_CreateObjCommand( interp, "load_testfixture_extensions", load_testfixture_extensions,0,0 ); return 0; ADDED src/test_window.c Index: src/test_window.c ================================================================== --- /dev/null +++ src/test_window.c @@ -0,0 +1,194 @@ +/* +** 2018 June 17 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +*/ + +#include "sqlite3.h" + +#ifdef SQLITE_TEST + +#include "sqliteInt.h" +#include + +extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb); +extern const char *sqlite3ErrName(int); + +typedef struct TestWindow TestWindow; +struct TestWindow { + Tcl_Obj *xStep; + Tcl_Obj *xFinal; + Tcl_Obj *xValue; + Tcl_Obj *xInverse; + Tcl_Interp *interp; +}; + +typedef struct TestWindowCtx TestWindowCtx; +struct TestWindowCtx { + Tcl_Obj *pVal; +}; + +static void doTestWindowStep( + int bInverse, + sqlite3_context *ctx, + int nArg, + sqlite3_value **apArg +){ + int i; + TestWindow *p = (TestWindow*)sqlite3_user_data(ctx); + Tcl_Obj *pEval = Tcl_DuplicateObj(bInverse ? p->xInverse : p->xStep); + TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx)); + + Tcl_IncrRefCount(pEval); + if( pCtx ){ + const char *zResult; + int rc; + if( pCtx->pVal ){ + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal)); + }else{ + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1)); + } + for(i=0; iinterp, pEval, pArg); + } + rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); + if( rc!=TCL_OK ){ + zResult = Tcl_GetStringResult(p->interp); + sqlite3_result_error(ctx, zResult, -1); + }else{ + if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal); + pCtx->pVal = Tcl_DuplicateObj(Tcl_GetObjResult(p->interp)); + Tcl_IncrRefCount(pCtx->pVal); + } + } + Tcl_DecrRefCount(pEval); +} + +static void doTestWindowFinalize(int bValue, sqlite3_context *ctx){ + TestWindow *p = (TestWindow*)sqlite3_user_data(ctx); + Tcl_Obj *pEval = Tcl_DuplicateObj(bValue ? p->xValue : p->xFinal); + TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx)); + + Tcl_IncrRefCount(pEval); + if( pCtx ){ + const char *zResult; + int rc; + if( pCtx->pVal ){ + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal)); + }else{ + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1)); + } + + rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); + zResult = Tcl_GetStringResult(p->interp); + if( rc!=TCL_OK ){ + sqlite3_result_error(ctx, zResult, -1); + }else{ + sqlite3_result_text(ctx, zResult, -1, SQLITE_TRANSIENT); + } + + if( bValue==0 ){ + if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal); + pCtx->pVal = 0; + } + } + Tcl_DecrRefCount(pEval); +} + +static void testWindowStep( + sqlite3_context *ctx, + int nArg, + sqlite3_value **apArg +){ + doTestWindowStep(0, ctx, nArg, apArg); +} +static void testWindowInverse( + sqlite3_context *ctx, + int nArg, + sqlite3_value **apArg +){ + doTestWindowStep(1, ctx, nArg, apArg); +} + +static void testWindowFinal(sqlite3_context *ctx){ + doTestWindowFinalize(0, ctx); +} +static void testWindowValue(sqlite3_context *ctx){ + doTestWindowFinalize(1, ctx); +} + +static void testWindowDestroy(void *pCtx){ + ckfree(pCtx); +} + +/* +** Usage: sqlite3_create_window_function DB NAME XSTEP XFINAL XVALUE XINVERSE +*/ +static int SQLITE_TCLAPI test_create_window( + void * clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + TestWindow *pNew; + sqlite3 *db; + const char *zName; + int rc; + + if( objc!=7 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB NAME XSTEP XFINAL XVALUE XINVERSE"); + return TCL_ERROR; + } + + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; + zName = Tcl_GetString(objv[2]); + pNew = ckalloc(sizeof(TestWindow)); + memset(pNew, 0, sizeof(TestWindow)); + pNew->xStep = Tcl_DuplicateObj(objv[3]); + pNew->xFinal = Tcl_DuplicateObj(objv[4]); + pNew->xValue = Tcl_DuplicateObj(objv[5]); + pNew->xInverse = Tcl_DuplicateObj(objv[6]); + pNew->interp = interp; + + Tcl_IncrRefCount(pNew->xStep); + Tcl_IncrRefCount(pNew->xFinal); + Tcl_IncrRefCount(pNew->xValue); + Tcl_IncrRefCount(pNew->xInverse); + + rc = sqlite3_create_window_function(db, zName, -1, SQLITE_UTF8, (void*)pNew, + testWindowStep, testWindowFinal, testWindowValue, testWindowInverse, + testWindowDestroy + ); + if( rc!=SQLITE_OK ){ + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + return TCL_ERROR; + } + + return TCL_OK; +} + +int Sqlitetest_window_Init(Tcl_Interp *interp){ + static struct { + char *zName; + Tcl_ObjCmdProc *xProc; + int clientData; + } aObjCmd[] = { + { "sqlite3_create_window_function", test_create_window, 0 }, + }; + int i; + for(i=0; ip1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) ); pMem = &aMem[pOp->p1]; assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 ); if( pOp->p3 ){ rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc); + pMem = &aMem[pOp->p3]; }else{ rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc); } if( rc ){ sqlite3VdbeError(p, "%s", sqlite3_value_text(pMem)); Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -298,11 +298,11 @@ ){ } static void cume_distValueFunc(sqlite3_context *pCtx){ struct CallCount *p; p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); - if( p ){ + if( p && p->nTotal ){ double r = (double)(p->nStep) / (double)(p->nTotal); sqlite3_result_double(pCtx, r); } } @@ -682,11 +682,10 @@ */ int sqlite3WindowRewrite(Parse *pParse, Select *p){ int rc = SQLITE_OK; if( p->pWin ){ Vdbe *v = sqlite3GetVdbe(pParse); - int i; sqlite3 *db = pParse->db; Select *pSub = 0; /* The subquery */ SrcList *pSrc = p->pSrc; Expr *pWhere = p->pWhere; ExprList *pGroupBy = p->pGroupBy; @@ -741,12 +740,10 @@ pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0 ); p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0); assert( p->pSrc || db->mallocFailed ); if( p->pSrc ){ - int iTab; - ExprList *pList = 0; p->pSrc->a[0].pSelect = pSub; sqlite3SrcListAssignCursors(pParse, p->pSrc); if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){ rc = SQLITE_NOMEM; }else{ @@ -1086,11 +1083,10 @@ int lblFlushPart, /* Subroutine to Gosub to */ int *pRegSize /* OUT: Register containing partition size */ ){ Window *pMWin = p->pWin; Vdbe *v = sqlite3GetVdbe(pParse); - Window *pWin; int iSubCsr = p->pSrc->a[0].iCursor; int nSub = p->pSrc->a[0].pTab->nCol; int k; int reg = pParse->nMem+1; @@ -1408,34 +1404,24 @@ int regGosub, int addrGosub ){ Window *pMWin = p->pWin; Vdbe *v = sqlite3GetVdbe(pParse); - Window *pWin; - int k; - int nSub = p->pSrc->a[0].pTab->nCol; int regFlushPart; /* Register for "Gosub flush_partition" */ int lblFlushPart; /* Label for "Gosub flush_partition" */ int lblFlushDone; /* Label for "Gosub flush_partition_done" */ int regArg; - int nArg; int addr; int csrStart = pParse->nTab++; int csrEnd = pParse->nTab++; int regStart; /* Value of PRECEDING */ int regEnd; /* Value of FOLLOWING */ - int addrNext; int addrGoto; int addrTop; int addrIfPos1; int addrIfPos2; - - int regPeer = 0; /* Number of peers in current group */ - int regPeerVal = 0; /* Array of values identifying peer group */ - int iPeer = 0; /* Column offset in eph-table of peer vals */ - int nPeerVal; /* Number of peer values */ int regSize = 0; assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT || pMWin->eStart==TK_FOLLOWING @@ -1677,11 +1663,10 @@ int regGosub, int addrGosub ){ Window *pMWin = p->pWin; Vdbe *v = sqlite3GetVdbe(pParse); - Window *pWin; int k; int addr; ExprList *pPart = pMWin->pPartition; ExprList *pOrderBy = pMWin->pOrderBy; int nPeer = pOrderBy ? pOrderBy->nExpr : 0; @@ -1693,11 +1678,10 @@ int lblFlushPart; int csrLead; int regCtr; int regArg; /* Register array to martial function args */ int regSize; - int nArg; int lblEmpty; int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED; assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) @@ -1820,11 +1804,10 @@ int regGosub, int addrGosub ){ Window *pMWin = p->pWin; Vdbe *v = sqlite3GetVdbe(pParse); - Window *pWin; int k; int iSubCsr = p->pSrc->a[0].iCursor; int nSub = p->pSrc->a[0].pTab->nCol; int reg = pParse->nMem+1; int regRecord = reg+nSub; Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -267,8 +267,15 @@ do_execsql_test 7.3 { SELECT row_number() OVER (ORDER BY x) FROM t1 } {1 2 3 4 5} +do_execsql_test 7.4 { + SELECT + row_number() OVER win, + lead(x) OVER win + FROM t1 + WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +} {1 3 2 5 3 7 4 9 5 {}} finish_test ADDED test/window5.test Index: test/window5.test ================================================================== --- /dev/null +++ test/window5.test @@ -0,0 +1,68 @@ +# 2018 May 8 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. Specifically, +# it tests the sqlite3_create_window_function() API. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix window1 + +proc m_step {ctx val} { + lappend ctx $val + return $ctx +} +proc m_value {ctx} { + set lSort [lsort $ctx] + + set nVal [llength $lSort] + set n [expr $nVal/2] + + if {($nVal % 2)==0 && $nVal>0} { + set a [lindex $lSort $n] + set b [lindex $lSort $n-1] + if {($a+$b) % 2} { + set ret [expr ($a+$b)/2.0] + } else { + set ret [expr ($a+$b)/2] + } + } else { + set ret [lindex $lSort $n] + } + return $ret +} +proc m_inverse {ctx val} { + set ctx [lrange $ctx 1 end] + return $ctx +} +proc w_value {ctx} { + lsort $ctx +} + +sqlite3_create_window_function db median m_step m_value m_value m_inverse +sqlite3_create_window_function db win m_step w_value w_value m_inverse + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(4, 'a'); + INSERT INTO t1 VALUES(6, 'b'); + INSERT INTO t1 VALUES(1, 'c'); + INSERT INTO t1 VALUES(5, 'd'); + INSERT INTO t1 VALUES(2, 'e'); + INSERT INTO t1 VALUES(3, 'f'); +} + +do_execsql_test 1.1 { + SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1; +} {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} + +finish_test +