Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with window functions min() and max() when used with a FILTER clause. Forum post e9126d554a. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
d15fb0f75e64bbfdb8df0c0d0358aafb |
| User & Date: | dan 2024-11-14 14:38:16 |
Context
|
2024-11-14
| ||
| 15:55 | Remove some obsolete macros from the CLI. (check-in: 5c4eb625 user: drh tags: trunk) | |
| 15:25 | Remove unused sqlite_cfg.h.in (sqlite_cfg.h gets generated without an input template). (check-in: 6148f2d3 user: stephan tags: makefile-simplification) | |
| 14:38 | Fix a problem with window functions min() and max() when used with a FILTER clause. Forum post e9126d554a. (check-in: d15fb0f7 user: dan tags: trunk) | |
| 12:23 | Document the if block at the end of sqlite-check-tcl. (check-in: 6bfd0940 user: stephan tags: trunk) | |
Changes
Changes to src/window.c.
| ︙ | ︙ | |||
1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 |
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
FuncDef *pFunc = pWin->pWFunc;
int regArg;
int nArg = pWin->bExprArgs ? 0 : windowArgCount(pWin);
int i;
assert( bInverse==0 || pWin->eStart!=TK_UNBOUNDED );
/* All OVER clauses in the same window function aggregate step must
** be the same. */
assert( pWin==pMWin || sqlite3WindowCompare(pParse,pWin,pMWin,0)!=1 );
for(i=0; i<nArg; i++){
if( i!=1 || pFunc->zName!=nth_valueName ){
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
}else{
sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+i, reg+i);
}
}
regArg = reg;
if( pMWin->regStartRowid==0
&& (pFunc->funcFlags & SQLITE_FUNC_MINMAX)
&& (pWin->eStart!=TK_UNBOUNDED)
){
int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
VdbeCoverage(v);
if( bInverse==0 ){
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
}else{
sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
VdbeCoverageNeverTaken(v);
sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}
sqlite3VdbeJumpHere(v, addrIsNull);
}else if( pWin->regApp ){
assert( pFunc->zName==nth_valueName
|| pFunc->zName==first_valueName
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pFunc->xSFunc!=noopStepFunc ){
| > > > > > > > > > > > > > > < < < < < < < < < < < < < | 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 |
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
FuncDef *pFunc = pWin->pWFunc;
int regArg;
int nArg = pWin->bExprArgs ? 0 : windowArgCount(pWin);
int i;
int addrIf = 0;
assert( bInverse==0 || pWin->eStart!=TK_UNBOUNDED );
/* All OVER clauses in the same window function aggregate step must
** be the same. */
assert( pWin==pMWin || sqlite3WindowCompare(pParse,pWin,pMWin,0)!=1 );
for(i=0; i<nArg; i++){
if( i!=1 || pFunc->zName!=nth_valueName ){
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
}else{
sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+i, reg+i);
}
}
regArg = reg;
if( pWin->pFilter ){
int regTmp;
assert( ExprUseXList(pWin->pOwner) );
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( pMWin->regStartRowid==0
&& (pFunc->funcFlags & SQLITE_FUNC_MINMAX)
&& (pWin->eStart!=TK_UNBOUNDED)
){
int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
VdbeCoverage(v);
if( bInverse==0 ){
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
}else{
sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
VdbeCoverageNeverTaken(v);
sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}
sqlite3VdbeJumpHere(v, addrIsNull);
}else if( pWin->regApp ){
assert( pWin->pFilter==0 );
assert( pFunc->zName==nth_valueName
|| pFunc->zName==first_valueName
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pFunc->xSFunc!=noopStepFunc ){
if( pWin->bExprArgs ){
int iOp = sqlite3VdbeCurrentAddr(v);
int iEnd;
assert( ExprUseXList(pWin->pOwner) );
nArg = pWin->pOwner->x.pList->nExpr;
regArg = sqlite3GetTempRange(pParse, nArg);
|
| ︙ | ︙ | |||
1750 1751 1752 1753 1754 1755 1756 |
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);
}
| < > > | 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 |
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);
}
}
/*
** Values that may be passed as the second argument to windowCodeOp().
*/
#define WINDOW_RETURN_ROW 1
|
| ︙ | ︙ |
Changes to test/window8.tcl.
| ︙ | ︙ | |||
485 486 487 488 489 490 491 492 493 494 495 |
SELECT (
SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
+ min(a) OVER()
)
FROM t1
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 |
SELECT (
SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
+ min(a) OVER()
)
FROM t1
}
==========
execsql_test 10.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES (10, 1),
(20, -1),
(5, 2),
(15, 0),
(25, 3);
}
execsql_test 10.1 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
}
execsql_test 10.2 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS ();
}
execsql_test 10.3 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS (ORDER BY a);
}
execsql_test 10.4 {
SELECT
a, b, MIN(a) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
}
==========
execsql_test 11.0 {
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER, b INTEGER);
INSERT INTO t2 VALUES(1, 12);
INSERT INTO t2 VALUES(2, 10);
INSERT INTO t2 VALUES(3, 15);
INSERT INTO t2 VALUES(4, 22);
INSERT INTO t2 VALUES(5, 1);
INSERT INTO t2 VALUES(6, 4);
INSERT INTO t2 VALUES(7, 7);
INSERT INTO t2 VALUES(8, 6);
INSERT INTO t2 VALUES(9, 22);
INSERT INTO t2 VALUES(10, 2);
}
execsql_test 11.1 {
SELECT a, min(b) FILTER (WHERE a%2 != 0) OVER win
FROM t2
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
}
finish_test
|
Changes to test/window8.test.
| ︙ | ︙ | |||
6535 6536 6537 6538 6539 6540 6541 6542 6543 |
do_execsql_test 9.2 {
SELECT (
SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
+ min(a) OVER()
)
FROM t1
} {}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 6535 6536 6537 6538 6539 6540 6541 6542 6543 6544 6545 6546 6547 6548 6549 6550 6551 6552 6553 6554 6555 6556 6557 6558 6559 6560 6561 6562 6563 6564 6565 6566 6567 6568 6569 6570 6571 6572 6573 6574 6575 6576 6577 6578 6579 6580 6581 6582 6583 6584 6585 6586 6587 6588 6589 6590 6591 6592 6593 6594 6595 6596 6597 6598 6599 6600 6601 6602 6603 6604 6605 6606 |
do_execsql_test 9.2 {
SELECT (
SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
+ min(a) OVER()
)
FROM t1
} {}
#==========================================================================
do_execsql_test 10.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES (10, 1),
(20, -1),
(5, 2),
(15, 0),
(25, 3);
} {}
do_execsql_test 10.1 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
} {5 2 5 10 1 5 15 0 10 20 -1 25 25 3 25}
do_execsql_test 10.2 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS ();
} {10 1 5 20 -1 5 5 2 5 15 0 5 25 3 5}
do_execsql_test 10.3 {
SELECT
a, b, MIN(a) FILTER(WHERE b > 0) OVER win
FROM t1
WINDOW win AS (ORDER BY a);
} {5 2 5 10 1 5 15 0 5 20 -1 5 25 3 5}
do_execsql_test 10.4 {
SELECT
a, b, MIN(a) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
} {5 2 5 10 1 5 15 0 10 20 -1 15 25 3 20}
#==========================================================================
do_execsql_test 11.0 {
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER, b INTEGER);
INSERT INTO t2 VALUES(1, 12);
INSERT INTO t2 VALUES(2, 10);
INSERT INTO t2 VALUES(3, 15);
INSERT INTO t2 VALUES(4, 22);
INSERT INTO t2 VALUES(5, 1);
INSERT INTO t2 VALUES(6, 4);
INSERT INTO t2 VALUES(7, 7);
INSERT INTO t2 VALUES(8, 6);
INSERT INTO t2 VALUES(9, 22);
INSERT INTO t2 VALUES(10, 2);
} {}
do_execsql_test 11.1 {
SELECT a, min(b) FILTER (WHERE a%2 != 0) OVER win
FROM t2
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
} {1 12 2 12 3 1 4 1 5 1 6 1 7 1 8 7 9 7 10 22}
finish_test
|
Changes to test/window9.test.
| ︙ | ︙ | |||
277 278 279 280 281 282 283 284 285 |
do_catchsql_test 9.1 {
SELECT sum(c) OVER (
ORDER BY c RANGE BETWEEN 0 PRECEDING AND '-700' PRECEDING
)
FROM t1
} {1 {frame ending offset must be a non-negative number}}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 |
do_catchsql_test 9.1 {
SELECT sum(c) OVER (
ORDER BY c RANGE BETWEEN 0 PRECEDING AND '-700' PRECEDING
)
FROM t1
} {1 {frame ending offset must be a non-negative number}}
#--------------------------------------------------------------------------
reset_db
do_execsql_test 10.0 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 'a');
INSERT INTO t1 VALUES(2, 'b');
INSERT INTO t1 VALUES(3, 'c');
INSERT INTO t1 VALUES(4, 'd');
INSERT INTO t1 VALUES(5, 'e');
INSERT INTO t1 VALUES(6, 'f');
}
do_execsql_test 10.1 {
SELECT a, min(b) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
} {
1 a
2 a
3 a
4 b
5 c
6 d
}
do_execsql_test 10.2 {
SELECT a, min(b) FILTER (WHERE a%2) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
} {
1 a
2 a
3 a
4 c
5 c
6 e
}
do_execsql_test 10.3 {
SELECT a, min(b) FILTER (WHERE (a%2)=0) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
} {
1 b
2 b
3 b
4 b
5 d
6 d
}
do_catchsql_test 10.4 {
SELECT a, nth_value(b, 1) FILTER (WHERE (a%2)=0) OVER win
FROM t1
WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
} {1 {FILTER clause may only be used with aggregate window functions}}
finish_test
|