Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Disable the ORDER BY LIMIT optimization in queries using window functions. This fixes a problem that was introduced by check-in [206720129ed2fa8875a286] which attempted to fix ticket [9936b2fa443fec03ff25f9]. This changes is a fix for the follow-in tocket [510cde277783b5fb5de628]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
c6c9585f294710829ca24b64d70a36fd |
| User & Date: | drh 2018-09-17 15:19:13 |
Context
|
2018-09-17
| ||
| 20:47 | Further optimizations to the UPDATE logic to avoid making changes to partial indexes if none of the columns mentioned in the WHERE clause are modified by the UPDATE. check-in: d1365a5bf0 user: drh tags: trunk | |
| 15:25 | Disable the ORDER BY LIMIT optimization in queries using window functions. This fixes a problem that was introduced by check-in [206720129ed2fa8875a286] which attempted to fix ticket [9936b2fa443fec03ff25f9]. This changes is a fix for the follow-in tocket [510cde277783b5fb5de628]. check-in: 36c75fd5b7 user: drh tags: branch-3.25 | |
| 15:19 | Disable the ORDER BY LIMIT optimization in queries using window functions. This fixes a problem that was introduced by check-in [206720129ed2fa8875a286] which attempted to fix ticket [9936b2fa443fec03ff25f9]. This changes is a fix for the follow-in tocket [510cde277783b5fb5de628]. check-in: c6c9585f29 user: drh tags: trunk | |
| 14:13 | Fix a false-positive in the post-ALTER-TABLE schema verification logic that prevents ALTER TABLE from working if the table being altered has a trigger that references any column of a virtual table. Ticket [b41031ea2b537237]. check-in: 84105ea4af user: drh tags: trunk | |
Changes
Changes to src/select.c.
6082 6083 6084 6085 6086 6087 6088 6089 6090 6091 6092 6093 6094 6095 |
int regGosub = ++pParse->nMem;
sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
sqlite3VdbeResolveLabel(v, addrGosub);
VdbeNoopComment((v, "inner-loop subroutine"));
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, iBreak);
sqlite3VdbeResolveLabel(v, iCont);
sqlite3VdbeAddOp1(v, OP_Return, regGosub);
VdbeComment((v, "end inner-loop subroutine"));
sqlite3VdbeResolveLabel(v, iBreak);
}else
#endif /* SQLITE_OMIT_WINDOWFUNC */
|
> |
6082 6083 6084 6085 6086 6087 6088 6089 6090 6091 6092 6093 6094 6095 6096 |
int regGosub = ++pParse->nMem;
sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
sqlite3VdbeResolveLabel(v, addrGosub);
VdbeNoopComment((v, "inner-loop subroutine"));
sSort.labelOBLopt = 0;
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, iCont, iBreak);
sqlite3VdbeResolveLabel(v, iCont);
sqlite3VdbeAddOp1(v, OP_Return, regGosub);
VdbeComment((v, "end inner-loop subroutine"));
sqlite3VdbeResolveLabel(v, iBreak);
}else
#endif /* SQLITE_OMIT_WINDOWFUNC */
|
Changes to test/window1.test.
522 523 524 525 526 527 528 529 530 |
do_catchsql_test 11.3 {
CREATE INDEX t6i ON t6(sum(b) OVER ());
} {1 {misuse of window function sum()}}
do_catchsql_test 11.4 {
CREATE INDEX t6i ON t6(lead(b) OVER ());
} {1 {misuse of window function lead()}}
finish_test
|
| > | > > > > > > > > > > > > > > > > > > > > > |
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 |
do_catchsql_test 11.3 {
CREATE INDEX t6i ON t6(sum(b) OVER ());
} {1 {misuse of window function sum()}}
do_catchsql_test 11.4 {
CREATE INDEX t6i ON t6(lead(b) OVER ());
} {1 {misuse of window function lead()}}
# 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3
# Endless loop on a query with window functions and a limit
#
do_execsql_test 12.100 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR);
INSERT INTO t1 VALUES(1, 'A', 'one');
INSERT INTO t1 VALUES(2, 'B', 'two');
INSERT INTO t1 VALUES(3, 'C', 'three');
INSERT INTO t1 VALUES(4, 'D', 'one');
INSERT INTO t1 VALUES(5, 'E', 'two');
SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
FROM t1 WHERE id>1
ORDER BY b LIMIT 1;
} {2 B two}
do_execsql_test 12.110 {
INSERT INTO t1 VALUES(6, 'F', 'three');
INSERT INTO t1 VALUES(7, 'G', 'one');
SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
FROM t1 WHERE id>1
ORDER BY b LIMIT 2;
} {2 B two 3 C three}
finish_test
|