Index: ext/misc/json1.c ================================================================== --- ext/misc/json1.c +++ ext/misc/json1.c @@ -2502,13 +2502,13 @@ { "json_tree", &jsonTreeModule }, }; #endif for(i=0; idb; + int i; + for(i=0; inExpr; i++){ + Expr *p = pList->a[i].pExpr; + if( p->op==TK_FUNCTION ){ + FuncDef *pDef; + int nArg = 0; + if( !ExprHasProperty(p, EP_TokenOnly) && p->x.pList ){ + nArg = p->x.pList->nExpr; + } + pDef = sqlite3FindFunction(db, p->u.zToken, nArg, db->enc, 0); + if( pDef && (pDef->funcFlags & SQLITE_FUNC_SUBTYPE) ){ + return 1; + } + } + } + } + return 0; +} /* ** Append a copy of each expression in expression-list pAppend to ** expression list pList. Return a pointer to the result list. */ @@ -963,12 +989,19 @@ /* Append the arguments passed to each window function to the ** sub-select expression list. Also allocate two registers for each ** window function - one for the accumulator, another for interim ** results. */ for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - pWin->iArgCol = (pSublist ? pSublist->nExpr : 0); - pSublist = exprListAppendList(pParse, pSublist, pWin->pOwner->x.pList, 0); + ExprList *pArgs = pWin->pOwner->x.pList; + if( exprListContainsSubtype(pParse, pArgs) ){ + selectWindowRewriteEList(pParse, pMWin, pSrc, pArgs, pTab, &pSublist); + pWin->iArgCol = (pSublist ? pSublist->nExpr : 0); + pWin->bExprArgs = 1; + }else{ + pWin->iArgCol = (pSublist ? pSublist->nExpr : 0); + pSublist = exprListAppendList(pParse, pSublist, pArgs, 0); + } if( pWin->pFilter ){ Expr *pFilter = sqlite3ExprDup(db, pWin->pFilter, 0); pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter); } pWin->regAccum = ++pParse->nMem; @@ -1430,11 +1463,11 @@ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; int regArg; - int nArg = windowArgCount(pWin); + int nArg = pWin->bExprArgs ? 0 : windowArgCount(pWin); int i; assert( bInverse==0 || pWin->eStart!=TK_UNBOUNDED ); for(i=0; iiArgCol+nArg,regTmp); addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1); VdbeCoverage(v); sqlite3ReleaseTempReg(pParse, regTmp); } + if( pWin->bExprArgs ){ + nArg = pWin->pOwner->x.pList->nExpr; + regArg = sqlite3GetTempRange(pParse, nArg); + sqlite3ExprCodeExprList(pParse, pWin->pOwner->x.pList, regArg, 0, 0); + } if( pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl; assert( nArg>0 ); pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr); sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ); @@ -1490,10 +1528,13 @@ } sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, bInverse, regArg, pWin->regAccum); sqlite3VdbeAppendP4(v, pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nArg); + if( pWin->bExprArgs ){ + sqlite3ReleaseTempRange(pParse, regArg, nArg); + } if( addrIf ) sqlite3VdbeJumpHere(v, addrIf); } } } ADDED test/windowB.test Index: test/windowB.test ================================================================== --- /dev/null +++ test/windowB.test @@ -0,0 +1,106 @@ +# 2019-08-30 +# +# 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. +# +#*********************************************************************** +# Test cases for RANGE BETWEEN and especially with NULLS LAST +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix windowB + +ifcapable !windowfunc { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(NULL, 1); + INSERT INTO t1 VALUES(NULL, 2); + INSERT INTO t1 VALUES(NULL, 3); +} {} + +foreach {tn win} { + 1 { ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING } + 2 { ORDER BY a NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING } + 3 { ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING } + 4 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING } + + 5 { ORDER BY a NULLS LAST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING } + 6 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING } + + 7 { ORDER BY a NULLS LAST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING } + 8 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING } +} { + do_execsql_test 1.$tn " + SELECT sum(b) OVER win FROM t1 + WINDOW win AS ( $win ) + " {6 6 6} +} + +do_execsql_test 1.2 { + SELECT sum(b) OVER win FROM t1 + WINDOW win AS ( + ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) +} {6 6 6} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, NULL); + INSERT INTO t1 VALUES(2, 45); + INSERT INTO t1 VALUES(3, 66.2); + INSERT INTO t1 VALUES(4, 'hello world'); + INSERT INTO t1 VALUES(5, 'hello world'); + INSERT INTO t1 VALUES(6, X'1234'); + INSERT INTO t1 VALUES(7, X'1234'); + INSERT INTO t1 VALUES(8, NULL); +} + +foreach {tn win} { + 1 "ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING" + 2 "ORDER BY b RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING" + 3 "ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING" + 4 "ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING" +} { + do_execsql_test 2.1.$tn " + SELECT a, sum(a) OVER win FROM t1 + WINDOW win AS ( $win ) + ORDER BY 1 + " {1 9 2 {} 3 {} 4 9 5 9 6 13 7 13 8 9} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 3.0 { + CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT); + INSERT INTO testjson VALUES(1, '{"a":1}'); + INSERT INTO testjson VALUES(2, '{"b":2}'); +} + +do_execsql_test 3.1 { + SELECT json_group_array(json(j)) FROM testjson; +} { + {[{"a":1},{"b":2}]} +} + +breakpoint +do_execsql_test 3.2 { + SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson; +} { + {[{"a":1}]} + {[{"a":1},{"b":2}]} +} + + +finish_test +