Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch window-functions-subtype-fix Excluding Merge-Ins
This is equivalent to a diff from 090cd07d37 to 7f424ec9a3
2019-09-13
| ||
17:51 | Add the SQLITE_SUBTYPE flag, which may be passed to sqlite3_create_window_function() to indicate to the core that the window function may call sqlite3_value_subtype() on its arguments. (Closed-Leaf check-in: c12653d3d2 user: dan tags: mistake) | |
17:36 | Change the meaning of the SQLITE_SUBTYPE flag so that it indicates that the function may use sqlite3_value_subtype() on its arguments. (Closed-Leaf check-in: 7f424ec9a3 user: dan tags: window-functions-subtype-fix) | |
17:30 | Fix a typo in the carray extension header comment - the comment that serves as the documentation to this extension. (check-in: 658a42d363 user: drh tags: trunk) | |
16:19 | Merge latest trunk changes with this branch. (check-in: 14ef754346 user: dan tags: window-functions-subtype-fix) | |
13:23 | Ensure that the idxStr for FTS5 is always zero-terminated. Fix for check-in [9d418a7a491761ee] (check-in: 090cd07d37 user: drh tags: trunk) | |
12:24 | Fix harmless compiler warnings. (check-in: a8927d14f8 user: drh tags: trunk) | |
Changes to ext/misc/json1.c.
︙ | ︙ | |||
2500 2501 2502 2503 2504 2505 2506 | } aMod[] = { { "json_each", &jsonEachModule }, { "json_tree", &jsonTreeModule }, }; #endif for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){ rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg, | | | | | 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 | } aMod[] = { { "json_each", &jsonEachModule }, { "json_tree", &jsonTreeModule }, }; #endif for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){ rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg, SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_SUBTYPE, (void*)&aFunc[i].flag, aFunc[i].xFunc, 0, 0); } #ifndef SQLITE_OMIT_WINDOWFUNC for(i=0; i<sizeof(aAgg)/sizeof(aAgg[0]) && rc==SQLITE_OK; i++){ rc = sqlite3_create_window_function(db, aAgg[i].zName, aAgg[i].nArg, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, aAgg[i].xStep, aAgg[i].xFinal, aAgg[i].xValue, jsonGroupInverse, 0); |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
1715 1716 1717 1718 1719 1720 1721 | || (255<(nName = sqlite3Strlen30( zFunctionName))) ){ return SQLITE_MISUSE_BKPT; } assert( SQLITE_FUNC_CONSTANT==SQLITE_DETERMINISTIC ); assert( SQLITE_FUNC_DIRECT==SQLITE_DIRECTONLY ); | | | 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 | || (255<(nName = sqlite3Strlen30( zFunctionName))) ){ return SQLITE_MISUSE_BKPT; } assert( SQLITE_FUNC_CONSTANT==SQLITE_DETERMINISTIC ); assert( SQLITE_FUNC_DIRECT==SQLITE_DIRECTONLY ); extraFlags = enc & (SQLITE_DETERMINISTIC|SQLITE_DIRECTONLY|SQLITE_SUBTYPE); enc &= (SQLITE_FUNC_ENCMASK|SQLITE_ANY); #ifndef SQLITE_OMIT_UTF16 /* If SQLITE_UTF16 is specified as the encoding type, transform this ** to one of SQLITE_UTF16LE or SQLITE_UTF16BE using the ** SQLITE_UTF16NATIVE macro. SQLITE_UTF16 is not used internally. ** |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
4983 4984 4985 4986 4987 4988 4989 4990 4991 4992 4993 4994 4995 4996 4997 4998 4999 | ** ** The SQLITE_DETERMINISTIC flag means that the new function will always ** maps the same inputs into the same output. The abs() function is ** deterministic, for example, but randomblob() is not. ** ** The SQLITE_DIRECTONLY flag means that the function may only be invoked ** from top-level SQL, and cannot be used in VIEWs or TRIGGERs. */ #define SQLITE_DETERMINISTIC 0x000000800 #define SQLITE_DIRECTONLY 0x000080000 /* ** CAPI3REF: Deprecated Functions ** DEPRECATED ** ** These functions are [deprecated]. In order to maintain ** backwards compatibility with older code, these functions continue | > > > > > | 4983 4984 4985 4986 4987 4988 4989 4990 4991 4992 4993 4994 4995 4996 4997 4998 4999 5000 5001 5002 5003 5004 | ** ** The SQLITE_DETERMINISTIC flag means that the new function will always ** maps the same inputs into the same output. The abs() function is ** deterministic, for example, but randomblob() is not. ** ** The SQLITE_DIRECTONLY flag means that the function may only be invoked ** from top-level SQL, and cannot be used in VIEWs or TRIGGERs. ** ** The SQLITE_SUBTYPE flag indicates to SQLite that the function may call ** [sqlite3_result_subtype()] in order to configure its return value with ** a sub-type. */ #define SQLITE_DETERMINISTIC 0x000000800 #define SQLITE_DIRECTONLY 0x000080000 #define SQLITE_SUBTYPE 0x000100000 /* ** CAPI3REF: Deprecated Functions ** DEPRECATED ** ** These functions are [deprecated]. In order to maintain ** backwards compatibility with older code, these functions continue |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 | #define SQLITE_FUNC_SLOCHNG 0x2000 /* "Slow Change". Value constant during a ** single query - might change over time */ #define SQLITE_FUNC_AFFINITY 0x4000 /* Built-in affinity() function */ #define SQLITE_FUNC_OFFSET 0x8000 /* Built-in sqlite_offset() function */ #define SQLITE_FUNC_WINDOW 0x00010000 /* Built-in window-only function */ #define SQLITE_FUNC_INTERNAL 0x00040000 /* For use by NestedParse() only */ #define SQLITE_FUNC_DIRECT 0x00080000 /* Not for use in TRIGGERs or VIEWs */ /* ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are ** used to create the initializers for the FuncDef structures. ** ** FUNCTION(zName, nArg, iArg, bNC, xFunc) ** Used to create a scalar function definition of a function zName | > | 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 | #define SQLITE_FUNC_SLOCHNG 0x2000 /* "Slow Change". Value constant during a ** single query - might change over time */ #define SQLITE_FUNC_AFFINITY 0x4000 /* Built-in affinity() function */ #define SQLITE_FUNC_OFFSET 0x8000 /* Built-in sqlite_offset() function */ #define SQLITE_FUNC_WINDOW 0x00010000 /* Built-in window-only function */ #define SQLITE_FUNC_INTERNAL 0x00040000 /* For use by NestedParse() only */ #define SQLITE_FUNC_DIRECT 0x00080000 /* Not for use in TRIGGERs or VIEWs */ #define SQLITE_FUNC_SUBTYPE 0x00100000 /* Result likely to have sub-type */ /* ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are ** used to create the initializers for the FuncDef structures. ** ** FUNCTION(zName, nArg, iArg, bNC, xFunc) ** Used to create a scalar function definition of a function zName |
︙ | ︙ | |||
3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 | int regPart; /* Array of registers for PARTITION BY values */ Expr *pOwner; /* Expression object this window is attached to */ int nBufferCol; /* Number of columns in buffer table */ int iArgCol; /* Offset of first argument for this function */ int regOne; /* Register containing constant value 1 */ int regStartRowid; int regEndRowid; }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); void sqlite3WindowUnlinkFromSelect(Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); | > | 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 | int regPart; /* Array of registers for PARTITION BY values */ Expr *pOwner; /* Expression object this window is attached to */ int nBufferCol; /* Number of columns in buffer table */ int iArgCol; /* Offset of first argument for this function */ int regOne; /* Register containing constant value 1 */ int regStartRowid; int regEndRowid; u8 bExprArgs; }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); void sqlite3WindowUnlinkFromSelect(Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
863 864 865 866 867 868 869 870 871 872 873 874 875 876 | sWalker.xSelectCallback = selectWindowRewriteSelectCb; sWalker.u.pRewrite = &sRewrite; (void)sqlite3WalkExprList(&sWalker, pEList); *ppSub = sRewrite.pSub; } /* ** Append a copy of each expression in expression-list pAppend to ** expression list pList. Return a pointer to the result list. */ static ExprList *exprListAppendList( Parse *pParse, /* Parsing context */ | > > > > > > > > > > > > > > > > > > > > > > > > > > | 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 | sWalker.xSelectCallback = selectWindowRewriteSelectCb; sWalker.u.pRewrite = &sRewrite; (void)sqlite3WalkExprList(&sWalker, pEList); *ppSub = sRewrite.pSub; } /* ** Return true if the top-level of list pList contains an SQL function ** with the SQLITE_FUNC_SUBTYPE flag set. Return false otherwise. */ int exprListContainsSubtype(Parse *pParse, ExprList *pList){ if( pList ){ sqlite3 *db = pParse->db; int i; for(i=0; i<pList->nExpr; 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. */ static ExprList *exprListAppendList( Parse *pParse, /* Parsing context */ |
︙ | ︙ | |||
961 962 963 964 965 966 967 | pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy, 0); /* 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){ | > > > | > > > | > | 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 | pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy, 0); /* 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){ 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; pWin->regResult = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); |
︙ | ︙ | |||
1428 1429 1430 1431 1432 1433 1434 | int reg /* Array of registers */ ){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; int regArg; | | | 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 | int reg /* Array of registers */ ){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; int regArg; int nArg = pWin->bExprArgs ? 0 : windowArgCount(pWin); int i; assert( bInverse==0 || pWin->eStart!=TK_UNBOUNDED ); for(i=0; i<nArg; i++){ if( i!=1 || pFunc->zName!=nth_valueName ){ sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i); |
︙ | ︙ | |||
1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 | assert( nArg || pWin->pOwner->x.pList==0 ); regTmp = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp); addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1); VdbeCoverage(v); sqlite3ReleaseTempReg(pParse, regTmp); } 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); } sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, bInverse, regArg, pWin->regAccum); sqlite3VdbeAppendP4(v, pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nArg); if( addrIf ) sqlite3VdbeJumpHere(v, addrIf); } } } typedef struct WindowCodeArg WindowCodeArg; typedef struct WindowCsrAndReg WindowCsrAndReg; | > > > > > > > > | 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 | assert( nArg || pWin->pOwner->x.pList==0 ); regTmp = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+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); } 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); } } } typedef struct WindowCodeArg WindowCodeArg; typedef struct WindowCsrAndReg WindowCsrAndReg; |
︙ | ︙ |
Added test/windowB.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 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 |