/ Check-in [6aa438ce]
Login

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

Overview
Comment:Add the SQLITE_SUBTYPE flag, which can be passed to sqlite3_create_function() and similar to indicate to the core that a user function is likely to use sqlite3_result_subtype().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions-subtype-fix
Files: files | file ages | folders
SHA3-256: 6aa438ce41d460a6782ae63503128b9140c28ff59c2b2eed48b004acf83e0560
User & Date: dan 2019-09-07 18:20:43
Context
2019-09-13
16:19
Merge latest trunk changes with this branch. check-in: 14ef7543 user: dan tags: window-functions-subtype-fix
2019-09-07
18:20
Add the SQLITE_SUBTYPE flag, which can be passed to sqlite3_create_function() and similar to indicate to the core that a user function is likely to use sqlite3_result_subtype(). check-in: 6aa438ce user: dan tags: window-functions-subtype-fix
2019-09-04
06:56
Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. check-in: cb3e2be6 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

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, 
                                 (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);







|
|
|







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
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);
  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.
  **







|







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
....
3607
3608
3609
3610
3611
3612
3613

3614
3615
3616
3617
3618
3619
3620
#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
................................................................................
  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);







>







 







>







1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
....
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
#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
................................................................................
  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
...
961
962
963
964
965
966
967



968



969

970
971
972
973
974
975
976
....
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
....
1478
1479
1480
1481
1482
1483
1484





1485
1486
1487
1488
1489
1490
1491
1492
1493
1494



1495
1496
1497
1498
1499
1500
1501
  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 */
................................................................................
    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){



      pWin->iArgCol = (pSublist ? pSublist->nExpr : 0);



      pSublist = exprListAppendList(pParse, pSublist, pWin->pOwner->x.pList, 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);
................................................................................
  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 = 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);
................................................................................
        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;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
|
>
>
>
|
>







 







|







 







>
>
>
>
>










>
>
>







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
...
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
....
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
....
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
  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 */
................................................................................
    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);
................................................................................
  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);
................................................................................
        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