/ Check-in [b2849570]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | branch-3.25
Files: files | file ages | folders
SHA3-256: b2849570967555d486e797cb1807e45706cb55036a4b9074be267b5e4940ec91
User & Date: drh 2018-09-27 13:10:37
Context
2018-10-01
11:00
Ensure that the OP_VColumn opcode does set sqlite3_vtab_nochange() unless the OPFLAG_NOCHNG bit is set in P5. Fix for ticket [69d642332d25aa3b7315a6d385] check-in: 31ac8dba user: drh tags: branch-3.25
2018-09-27
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
2018-09-25
19:08
Version 3.25.2 check-in: fb90e718 user: drh tags: release, version-3.25.2, branch-3.25
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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
  )