Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with using json1 window functions with an EXCLUDE clause. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
4a1978814da41608a16f6953bd575c97 |
| User & Date: | dan 2019-09-13 20:42:46 |
Context
|
2019-09-14
| ||
| 00:21 | Fix the windows inverse function on the JSON aggregates. (check-in: f464d847af user: drh tags: trunk) | |
|
2019-09-13
| ||
| 20:42 | Fix a problem with using json1 window functions with an EXCLUDE clause. (check-in: 4a1978814d user: dan tags: trunk) | |
| 18:59 | Fix the windowB test module so that it works even if SQLite is built without JSON support. (check-in: 807975c76b user: drh tags: trunk) | |
Changes
Changes to src/window.c.
1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 |
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pFunc->xSFunc!=noopStepFunc ){
int addrIf = 0;
if( pWin->pFilter ){
int regTmp;
assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr );
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);
}
|
| | > > > > > > > > > > |
1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 |
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pFunc->xSFunc!=noopStepFunc ){
int addrIf = 0;
if( pWin->pFilter ){
int regTmp;
assert( pWin->bExprArgs || !nArg ||nArg==pWin->pOwner->x.pList->nExpr );
assert( pWin->bExprArgs || 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 ){
int iStart = sqlite3VdbeCurrentAddr(v);
VdbeOp *pOp, *pEnd;
nArg = pWin->pOwner->x.pList->nExpr;
regArg = sqlite3GetTempRange(pParse, nArg);
sqlite3ExprCodeExprList(pParse, pWin->pOwner->x.pList, regArg, 0, 0);
pEnd = sqlite3VdbeGetOp(v, -1);
for(pOp=sqlite3VdbeGetOp(v, iStart); pOp<=pEnd; pOp++){
if( pOp->opcode==OP_Column && pOp->p1==pWin->iEphCsr ){
pOp->p1 = csr;
}
}
}
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);
}
|
Changes to test/windowB.test.
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 |
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}');
}
ifcapable json1 {
do_execsql_test 3.1 {
SELECT json_group_array(json(j)) FROM testjson;
} {
{[{"a":1},{"b":2}]}
}
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
|
> | | | | | > > | | < | | > > | > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
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}
}
#-------------------------------------------------------------------------
ifcapable json1 {
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}');
INSERT INTO testjson VALUES(3, '{"c":3}');
INSERT INTO testjson VALUES(4, '{"d":4}');
}
do_execsql_test 3.1 {
SELECT json_group_array(json(j)) FROM testjson;
} {
{[{"a":1},{"b":2},{"c":3},{"d":4}]}
}
do_execsql_test 3.2 {
SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
} {
{[{"a":1}]}
{[{"a":1},{"b":2}]}
{[{"a":1},{"b":2},{"c":3}]}
{[{"a":1},{"b":2},{"c":3},{"d":4}]}
}
do_execsql_test 3.3 {
SELECT json_group_array(json(j)) OVER (
ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
) FROM testjson;
} {
{[{"a":1}]}
{[{"a":1},{"b":2}]}
{[{"a":1},{"b":2},{"c":3}]}
{[{"a":1},{"b":2},{"c":3},{"d":4}]}
}
do_execsql_test 3.4 {
SELECT json_group_array(json(j)) OVER (
ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM testjson;
} {
{[{"a":1},{"b":2}]}
{[{"a":1},{"b":2},{"c":3}]}
{[{"b":2},{"c":3},{"d":4}]}
{[{"c":3},{"d":4}]}
}
do_execsql_test 3.5 {
SELECT json_group_array(json(j)) OVER (
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
) FROM testjson;
} {
{[]}
{[{"a":1}]}
{[{"a":1},{"b":2}]}
{[{"b":2},{"c":3}]}
}
if 0 {
do_execsql_test 3.5 {
SELECT json_group_array(json(j)) OVER (
ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
) FROM testjson;
} {
{[]}
{[{"a":1}]}
{[{"a":1},{"b":2}]}
{[{"b":2},{"c":3}]}
}
explain_i {
SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
) FROM testjson;
}
do_execsql_test 3.7 {
PRAGMA vdbe_trace = 1;
SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
) FROM testjson;
} {
{[]}
{[{"a":1}]}
{[{"a":1}]}
{[{"c":3}]}
}
}
}
finish_test
|