/ Check-in [7fc29944]
Login

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: 7fc2994434c7d9ed29c96a69c07e8eb4e97be776473c170c63f9a1bbaa09fa68
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  2313   2313     int eDest = SRT_Fifo;         /* How to write to Queue */
  2314   2314     SelectDest destQueue;         /* SelectDest targetting the Queue table */
  2315   2315     int i;                        /* Loop counter */
  2316   2316     int rc;                       /* Result code */
  2317   2317     ExprList *pOrderBy;           /* The ORDER BY clause */
  2318   2318     Expr *pLimit;                 /* Saved LIMIT and OFFSET */
  2319   2319     int regLimit, regOffset;      /* Registers used by LIMIT and OFFSET */
         2320  +
         2321  +#ifndef SQLITE_OMIT_WINDOWFUNC
         2322  +  if( p->pWin ){
         2323  +    sqlite3ErrorMsg(pParse, "cannot use window functions in recursive queries");
         2324  +    return;
         2325  +  }
         2326  +#endif
  2320   2327   
  2321   2328     /* Obtain authorization to do a recursive query */
  2322   2329     if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return;
  2323   2330   
  2324   2331     /* Process the LIMIT and OFFSET clauses, if they exist */
  2325   2332     addrBreak = sqlite3VdbeMakeLabel(v);
  2326   2333     p->nSelectRow = 320;  /* 4 billion rows */

Changes to test/with1.test.

   860    860   # 2015-07-05:  Do not allow aggregate recursive queries
   861    861   #
   862    862   do_catchsql_test 16.1 {
   863    863     WITH RECURSIVE
   864    864       i(x) AS (VALUES(1) UNION SELECT count(*) FROM i)
   865    865     SELECT * FROM i;
   866    866   } {1 {recursive aggregate queries not supported}}
          867  +
          868  +# Or window-function recursive queries. Ticket e8275b41.
          869  +#
          870  +ifcapable windowfunc {
          871  +  do_catchsql_test 16.2 {
          872  +    WITH RECURSIVE
          873  +      i(x) AS (VALUES(1) UNION SELECT count(*) OVER () FROM i)
          874  +      SELECT * FROM i;
          875  +  } {1 {cannot use window functions in recursive queries}}
          876  +  do_catchsql_test 16.3 {
          877  +    WITH RECURSIVE
          878  +      t(id, parent) AS (VALUES(1,2)),
          879  +      q(id, parent, rn) AS (
          880  +          VALUES(1,2,3)
          881  +          UNION ALL
          882  +          SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
          883  +          FROM q JOIN t ON t.parent = q.id
          884  +          )
          885  +        SELECT * FROM q;
          886  +  } {1 {cannot use window functions in recursive queries}}
          887  +}
   867    888   
   868    889   #-------------------------------------------------------------------------
   869    890   do_execsql_test 17.1 {
   870    891     WITH x(a) AS (
   871    892       WITH y(b) AS (SELECT 10)
   872    893       SELECT 9 UNION ALL SELECT * FROM y
   873    894     )