SQLite

    if( pRet ){
      int i;
      pRet->nCte = p->nCte;
      for(i=0; i<p->nCte; i++){
        pRet->a[i].pSelect = sqlite3SelectDup(db, p->a[i].pSelect, 0);
        pRet->a[i].pCols = sqlite3ExprListDup(db, p->a[i].pCols, 0);
        pRet->a[i].zName = sqlite3DbStrDup(db, p->a[i].zName);
        pRet->a[i].eM10d = p->a[i].eM10d;
      }
    }
  }
  return pRet;
}
#else
# define sqlite3WithDup(x,y) 0

**
**  (26)  The subquery may not be the right operand of a RIGHT JOIN.
**        See also (3) for restrictions on LEFT JOIN.
**
**  (27)  The subquery may not contain a FULL or RIGHT JOIN unless it
**        is the first element of the parent query.
**
**  (28)  The subquery is not a MATERIALIZED CTE.
**
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
** uses aggregates.
**
** If flattening is not attempted, this routine is a no-op and returns 0.
** If flattening is attempted this routine returns 1.

    isOuterJoin = -1;
  }
#endif

  assert( pSubSrc->nSrc>0 );  /* True by restriction (7) */
  if( iFrom>0 && (pSubSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
    return 0;   /* Restriction (27) */
  }
  if( pSubitem->fg.isCte && pSubitem->u2.pCteUse->eM10d==M10d_Yes ){
    return 0;       /* (28) */
  }

  /* Restriction (17): If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct
  ** queries.
  */

        int j;
        for(j=0; j<pCte->pCols->nExpr; j++){
          sqlite3_str_appendf(&x, "%c%s", cSep, pCte->pCols->a[j].zEName);
          cSep = ',';
        }
        sqlite3_str_appendf(&x, ")");
      }
      if( pCte->eM10d!=M10d_Any ){
        sqlite3_str_appendf(&x, " %sMATERIALIZED", 
           pCte->eM10d==M10d_No ? "NOT " : "");
      }
      if( pCte->pUse ){
        sqlite3_str_appendf(&x, " (pUse=0x%p, nUse=%d)", pCte->pUse,
                 pCte->pUse->nUse);
      }
      sqlite3StrAccumFinish(&x);
      sqlite3TreeViewItem(pView, zLine, i<pWith->nCte-1);
      sqlite3TreeViewSelect(pView, pCte->pSelect, 0);

             SELECT * FROM Table0  NATURAL JOIN  Table0));
} {{}}

# 2019-01-17
# Make sure crazy nexted CTE joins terminate with an error quickly.
#
do_catchsql_test 22.1 {
  WITH RECURSIVE c AS NOT MATERIALIZED (
     WITH RECURSIVE c AS NOT MATERIALIZED (
        WITH RECURSIVE c AS NOT MATERIALIZED (
           WITH RECURSIVE c AS NOT MATERIALIZED (
               WITH  c AS (VALUES(0))
               SELECT 1 FROM c LEFT JOIN c ON ltrim(1)
           )
           SELECT 1 FROM c,c,c,c,c,c,c,c,c
        )
        SELECT  2 FROM c,c,c,c,c,c,c,c,c
     )

  SELECT y FROM t2 ORDER BY y;
} {40404 40405 40406 40504 40505 40506 40604 40605 40606}
do_eqp_test 211 {
  SELECT y FROM t2 ORDER BY y;
} {
  QUERY PLAN
  |--MATERIALIZE c1
  |  |--CO-ROUTINE c
  |  |  `--SCAN 3 CONSTANT ROWS
  |  `--SCAN c
  |--MATERIALIZE c2
  |  |--CO-ROUTINE c
  |  |  `--SCAN 3 CONSTANT ROWS
  |  `--SCAN c
  |--SCAN c1
  |--SCAN c2
  |--SCAN t1
  `--USE TEMP B-TREE FOR ORDER BY
}
do_execsql_test 220 {
  DROP VIEW t2;
  CREATE VIEW t2(y) AS
    WITH c(z) AS MATERIALIZED (VALUES(4),(5),(6))
    SELECT c1.z+c2.z*100+t1.x*10000
      FROM t1,
           (SELECT z FROM c LIMIT 5) AS c1,
           (SELECT z FROM c LIMIT 5) AS c2;
  SELECT y FROM t2 ORDER BY y;
} {40404 40405 40406 40504 40505 40506 40604 40605 40606}

# 2022-04-22: Do not allow flattening of a MATERIALIZED CTE into
# an outer query.
#
reset_db
db null -
do_execsql_test 300 {
  CREATE TABLE t2(a INT,b INT,d INT); INSERT INTO t2 VALUES(4,5,6),(7,8,9);
  CREATE TABLE t3(a INT,b INT,e INT); INSERT INTO t3 VALUES(3,3,3),(8,8,8);
} {}
do_execsql_test 310 {
  WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  4 5 6 - -
  7 8 9 8 8
  - 3 - 3 3
}
do_eqp_test 311 {
  WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  QUERY PLAN
  |--MATERIALIZE t23
  |  |--SCAN t2
  |  |--SCAN t3 LEFT-JOIN
  |  `--RIGHT-JOIN t3
  |     `--SCAN t3
  `--SCAN t23
}
do_execsql_test 320 {
  WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  4 5 6 - -
  7 8 9 8 8
  - 3 - 3 3
}
do_eqp_test 321 {
  WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  QUERY PLAN
  |--SCAN t2
  |--SCAN t3 LEFT-JOIN
  `--RIGHT-JOIN t3
     `--SCAN t3
}
do_execsql_test 330 {
  WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  4 5 6 - -
  7 8 9 8 8
  - 3 - 3 3
}
do_eqp_test 331 {
  WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b))
  SELECT * FROM t23;
} {
  QUERY PLAN
  |--SCAN t2
  |--SCAN t3 LEFT-JOIN
  `--RIGHT-JOIN t3
     `--SCAN t3
}


finish_test