Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
e7bced731aa071c95bc398cdecd53c93 |
| User & Date: | dan 2019-03-11 11:12:34 |
| Wiki: | window-functions |
Context
|
2019-03-11
| ||
| 18:17 | Simplify the windows frame code some. Add a comment explaining some of the VM code generated by sqlite3WindowCodeStep(). (check-in: 6bd1a079 user: dan tags: window-functions) | |
| 11:12 | Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. (check-in: e7bced73 user: dan tags: window-functions) | |
|
2019-03-09
| ||
| 20:49 | Add support for RANGE window frames. Some cases still do not work. (check-in: ffc32b24 user: dan tags: window-functions) | |
Changes
Changes to src/window.c.
1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 |
int csr2,
int lbl
){
Parse *pParse = p->pParse;
Vdbe *v = sqlite3GetVdbe(pParse);
int reg1 = sqlite3GetTempReg(pParse);
int reg2 = sqlite3GetTempReg(pParse);
windowReadPeerValues(p, csr1, reg1);
windowReadPeerValues(p, csr2, reg2);
sqlite3VdbeAddOp3(v, OP_Add, reg1, regVal, reg1);
sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
sqlite3ReleaseTempReg(pParse, reg1);
sqlite3ReleaseTempReg(pParse, reg2);
assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
}
static int windowCodeOp(
WindowCodeArg *p,
int op,
int regCountdown,
int jumpOnEof
|
> > > > > > > > > > > > > | < < |
1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 |
int csr2,
int lbl
){
Parse *pParse = p->pParse;
Vdbe *v = sqlite3GetVdbe(pParse);
int reg1 = sqlite3GetTempReg(pParse);
int reg2 = sqlite3GetTempReg(pParse);
int arith = OP_Add;
assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
if( p->pMWin->pOrderBy->a[0].sortOrder ){
switch( op ){
case OP_Ge: op = OP_Le; break;
case OP_Gt: op = OP_Lt; break;
default: assert( op==OP_Le ); op = OP_Ge; break;
}
arith = OP_Subtract;
}
windowReadPeerValues(p, csr1, reg1);
windowReadPeerValues(p, csr2, reg2);
sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
sqlite3ReleaseTempReg(pParse, reg1);
sqlite3ReleaseTempReg(pParse, reg2);
}
static int windowCodeOp(
WindowCodeArg *p,
int op,
int regCountdown,
int jumpOnEof
|
Changes to test/window8.tcl.
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
==========
execsql_test 2.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(13, 26), (15, 30);
}
foreach {tn frame} {
1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
} {
execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
}
finish_test
|
| > > > > |
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 |
==========
execsql_test 2.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(5, 10), (10, 20), (13, 26), (13, 26),
(15, 30), (20, 40), (22,80), (30, 90);
}
foreach {tn frame} {
1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
} {
execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
}
finish_test
|
Changes to test/window8.test.
435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 |
#==========================================================================
do_execsql_test 2.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(13, 26), (15, 30);
} {}
do_execsql_test 2.1 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {13 56 15 56}
do_execsql_test 2.2 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {13 {} 15 {}}
do_execsql_test 2.3 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {13 30 15 {}}
finish_test
|
| > | | | > > > > > > > > > > > > |
435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 |
#==========================================================================
do_execsql_test 2.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(5, 10), (10, 20), (13, 26), (13, 26),
(15, 30), (20, 40), (22,80), (30, 90);
} {}
do_execsql_test 2.1 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {5 30 10 112 13 102 13 102 15 142 20 150 22 120 30 90}
do_execsql_test 2.2 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {5 {} 10 10 13 10 13 10 15 30 20 102 22 82 30 120}
do_execsql_test 2.3 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {5 {} 10 52 13 30 13 30 15 {} 20 80 22 {} 30 {}}
do_execsql_test 2.4 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {30 90 22 120 20 150 15 142 13 102 13 102 10 112 5 30}
do_execsql_test 2.5 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {30 {} 22 90 20 90 15 120 13 120 13 120 10 70 5 102}
do_execsql_test 2.6 {
SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {30 {} 22 40 20 {} 15 52 13 20 13 20 10 {} 5 {}}
finish_test
|