Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
7fc2994434c7d9ed29c96a69c07e8eb4 |
| User & Date: | dan 2018-09-27 12:14:15 |
| Original Comment: | Disallow the use of window functions in the recursive part of a recursive CTE. |
Context
|
2018-09-27
| ||
| 17:03 | Add the sqlite_memstat extension - an eponymous virtual table that shows memory usages statistics for SQLite. (check-in: 954ef61f user: drh tags: trunk) | |
| 15:21 | Initial prototype of a eponymous virtual table that accesses sqlite3_status64() and sqlite3_db_status(). (check-in: 0b44e1f6 user: drh tags: memstat-vtab) | |
| 14:24 | Minor enhancement to the pager so that it remembers if the underlying database files is immutable. (Leaf check-in: 64db614e user: drh tags: immutable-pager) | |
| 13:10 | Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. (check-in: b2849570 user: drh tags: branch-3.25) | |
| 12:14 | Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. (check-in: 7fc29944 user: dan tags: trunk) | |
| 00:04 | Minor simplification: In OP_ParseSchema, read the p1 register once. (check-in: bd250533 user: mistachkin tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 | int eDest = SRT_Fifo; /* How to write to Queue */ SelectDest destQueue; /* SelectDest targetting the Queue table */ int i; /* Loop counter */ int rc; /* Result code */ ExprList *pOrderBy; /* The ORDER BY clause */ Expr *pLimit; /* Saved LIMIT and OFFSET */ int regLimit, regOffset; /* Registers used by LIMIT and OFFSET */ /* Obtain authorization to do a recursive query */ if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return; /* Process the LIMIT and OFFSET clauses, if they exist */ addrBreak = sqlite3VdbeMakeLabel(v); p->nSelectRow = 320; /* 4 billion rows */ | > > > > > > > | 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 |
int eDest = SRT_Fifo; /* How to write to Queue */
SelectDest destQueue; /* SelectDest targetting the Queue table */
int i; /* Loop counter */
int rc; /* Result code */
ExprList *pOrderBy; /* The ORDER BY clause */
Expr *pLimit; /* Saved LIMIT and OFFSET */
int regLimit, regOffset; /* Registers used by LIMIT and OFFSET */
#ifndef SQLITE_OMIT_WINDOWFUNC
if( p->pWin ){
sqlite3ErrorMsg(pParse, "cannot use window functions in recursive queries");
return;
}
#endif
/* Obtain authorization to do a recursive query */
if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return;
/* Process the LIMIT and OFFSET clauses, if they exist */
addrBreak = sqlite3VdbeMakeLabel(v);
p->nSelectRow = 320; /* 4 billion rows */
|
| ︙ | ︙ |
Changes to test/with1.test.
| ︙ | ︙ | |||
860 861 862 863 864 865 866 867 868 869 870 871 872 873 |
# 2015-07-05: Do not allow aggregate recursive queries
#
do_catchsql_test 16.1 {
WITH RECURSIVE
i(x) AS (VALUES(1) UNION SELECT count(*) FROM i)
SELECT * FROM i;
} {1 {recursive aggregate queries not supported}}
#-------------------------------------------------------------------------
do_execsql_test 17.1 {
WITH x(a) AS (
WITH y(b) AS (SELECT 10)
SELECT 9 UNION ALL SELECT * FROM y
)
| > > > > > > > > > > > > > > > > > > > > > | 860 861 862 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 |
# 2015-07-05: Do not allow aggregate recursive queries
#
do_catchsql_test 16.1 {
WITH RECURSIVE
i(x) AS (VALUES(1) UNION SELECT count(*) FROM i)
SELECT * FROM i;
} {1 {recursive aggregate queries not supported}}
# Or window-function recursive queries. Ticket e8275b41.
#
ifcapable windowfunc {
do_catchsql_test 16.2 {
WITH RECURSIVE
i(x) AS (VALUES(1) UNION SELECT count(*) OVER () FROM i)
SELECT * FROM i;
} {1 {cannot use window functions in recursive queries}}
do_catchsql_test 16.3 {
WITH RECURSIVE
t(id, parent) AS (VALUES(1,2)),
q(id, parent, rn) AS (
VALUES(1,2,3)
UNION ALL
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
FROM q JOIN t ON t.parent = q.id
)
SELECT * FROM q;
} {1 {cannot use window functions in recursive queries}}
}
#-------------------------------------------------------------------------
do_execsql_test 17.1 {
WITH x(a) AS (
WITH y(b) AS (SELECT 10)
SELECT 9 UNION ALL SELECT * FROM y
)
|
| ︙ | ︙ |