From 569d4f7891e6a277b958b69d54de053a78a836f3 Mon Sep 17 00:00:00 2001 From: Dan Kennedy Date: Mon, 18 Jun 2018 16:55:22 +0000 Subject: [PATCH] Add new API function sqlite3_create_window_function(), for creating new aggregate window functions. --- Makefile.in | 1 + Makefile.msc | 1 + main.mk | 1 + src/func.c | 6 +- src/main.c | 90 ++++++++++++++++++------- src/sqlite.h.in | 12 ++++ src/sqliteInt.h | 5 +- src/test_tclsh.c | 2 + src/test_window.c | 194 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ src/vdbe.c | 1 + src/window.c | 19 +----- test/window1.test | 7 ++ test/window5.test | 68 +++++++++++++++++++ 13 files changed, 362 insertions(+), 45 deletions(-) create mode 100644 src/test_window.c create mode 100644 test/window5.test diff --git a/Makefile.in b/Makefile.in index 1757a049ac..8679a64173 100644 --- a/Makefile.in +++ b/Makefile.in @@ -419,6 +419,7 @@ TESTSRC = \ $(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 \ diff --git a/Makefile.msc b/Makefile.msc index d31c117f56..e423631fea 100644 --- a/Makefile.msc +++ b/Makefile.msc @@ -1479,6 +1479,7 @@ TESTSRC = \ $(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 \ diff --git a/main.mk b/main.mk index 0e46ed39c0..eb66473481 100644 --- a/main.mk +++ b/main.mk @@ -349,6 +349,7 @@ TESTSRC = \ $(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. diff --git a/src/func.c b/src/func.c index f9903095d2..772276e783 100644 --- a/src/func.c +++ b/src/func.c @@ -1771,10 +1771,10 @@ void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){ }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); diff --git a/src/main.c b/src/main.c index a2b994f9be..9f6061bea4 100644 --- a/src/main.c +++ b/src/main.c @@ -1683,6 +1683,8 @@ int sqlite3CreateFunc( 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; @@ -1716,10 +1718,10 @@ int sqlite3CreateFunc( }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; @@ -1765,38 +1767,32 @@ int sqlite3CreateFunc( 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( - 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( +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 (*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; @@ -1818,7 +1814,9 @@ int sqlite3_create_function_v2( 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); @@ -1831,6 +1829,52 @@ int sqlite3_create_function_v2( 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, @@ -1851,7 +1895,7 @@ int sqlite3_create_function16( 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); diff --git a/src/sqlite.h.in b/src/sqlite.h.in index 7d664177e4..9df546a262 100644 --- a/src/sqlite.h.in +++ b/src/sqlite.h.in @@ -4741,6 +4741,18 @@ int sqlite3_create_function_v2( 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 diff --git a/src/sqliteInt.h b/src/sqliteInt.h index ce84441a13..61f4f924af 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -4244,7 +4244,10 @@ 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*); diff --git a/src/test_tclsh.c b/src/test_tclsh.c index 97f7f5d7a1..ff0ac5742f 100644 --- a/src/test_tclsh.c +++ b/src/test_tclsh.c @@ -105,6 +105,7 @@ const char *sqlite3TestInit(Tcl_Interp *interp){ extern int Zipvfs_Init(Tcl_Interp*); #endif extern int TestExpert_Init(Tcl_Interp*); + extern int Sqlitetest_window_Init(Tcl_Interp *); Tcl_CmdInfo cmdInfo; @@ -169,6 +170,7 @@ const char *sqlite3TestInit(Tcl_Interp *interp){ Sqlitetestfts3_Init(interp); #endif TestExpert_Init(interp); + Sqlitetest_window_Init(interp); Tcl_CreateObjCommand( interp, "load_testfixture_extensions", load_testfixture_extensions,0,0 diff --git a/src/test_window.c b/src/test_window.c new file mode 100644 index 0000000000..e04de5eb0c --- /dev/null +++ b/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; iflags & ~(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); } diff --git a/src/window.c b/src/window.c index 00f3f44c8b..a61821847d 100644 --- a/src/window.c +++ b/src/window.c @@ -300,7 +300,7 @@ static void cume_distInvFunc( 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); } @@ -684,7 +684,6 @@ 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; @@ -743,8 +742,6 @@ int sqlite3WindowRewrite(Parse *pParse, Select *p){ 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]) ){ @@ -1088,7 +1085,6 @@ static void windowPartitionCache( ){ 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; @@ -1410,30 +1406,20 @@ static void windowCodeRowExprStep( ){ 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 @@ -1679,7 +1665,6 @@ static void windowCodeCacheStep( ){ Window *pMWin = p->pWin; Vdbe *v = sqlite3GetVdbe(pParse); - Window *pWin; int k; int addr; ExprList *pPart = pMWin->pPartition; @@ -1695,7 +1680,6 @@ static void windowCodeCacheStep( 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; @@ -1822,7 +1806,6 @@ static void windowCodeDefaultStep( ){ 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; diff --git a/test/window1.test b/test/window1.test index 9c2a0a5583..8daa97c1bd 100644 --- a/test/window1.test +++ b/test/window1.test @@ -269,6 +269,13 @@ 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 diff --git a/test/window5.test b/test/window5.test new file mode 100644 index 0000000000..9f082f234f --- /dev/null +++ b/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 + -- 2.11.4.GIT