SQLite

    if( pFrom->fg.isIndexedBy ){
      sqlite3ErrorMsg(pParse, "no such index: \"%s\"", pFrom->u1.zIndexedBy);
      return 2;
    }
    pFrom->fg.isCte = 1;
    pFrom->u2.pCteUse = pCteUse;
    pCteUse->nUse++;




    /* Check if this is a recursive CTE. */
    pRecTerm = pSel = pFrom->pSelect;
    bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION );
    while( bMayRecursive && pRecTerm->op==pSel->op ){
      int i;
      SrcList *pSrc = pRecTerm->pSrc;

**              requires it to be the outer loop
**         (c)  All of the following are true:
**                (i) The subquery is the left-most subquery in the FROM clause
**               (ii) There is nothing that would prevent the subquery from
**                    being used as the outer loop if the sqlite3WhereBegin()
**                    routine nominates it to that position.
**              (iii) The query is not a UPDATE ... FROM
**    (2)  The subquery is not a CTE that should be materialized because
**         (a) the AS MATERIALIZED keyword is used, or
**         (b) the CTE is used multiple times and does not have the
**             NOT MATERIALIZED keyword
**    (3)  The subquery is not part of a left operand for a RIGHT JOIN
**    (4)  The SQLITE_Coroutine optimization disable flag is not set
**    (5)  The subquery is not self-joined
*/
static int fromClauseTermCanBeCoroutine(
  Parse *pParse,          /* Parsing context */
  SrcList *pTabList,      /* FROM clause */
  int i,                  /* Which term of the FROM clause holds the subquery */
  int selFlags            /* Flags on the SELECT statement */
){
  SrcItem *pItem = &pTabList->a[i];
  if( pItem->fg.isCte ){
    const CteUse *pCteUse = pItem->u2.pCteUse;
    if( pCteUse->eM10d==M10d_Yes ) return 0;                          /* (2a) */
    if( pCteUse->nUse>=2 && pCteUse->eM10d!=M10d_No ) return 0;       /* (2b) */
  }
  if( pTabList->a[0].fg.jointype & JT_LTORJ ) return 0;               /* (3)  */
  if( OptimizationDisabled(pParse->db, SQLITE_Coroutines) ) return 0; /* (4)  */
  if( isSelfJoinView(pTabList, pItem, i+1, pTabList->nSrc)!=0 ){
    return 0;                                                          /* (5) */
  }
  if( i==0 ){
    if( pTabList->nSrc==1 ) return 1;                             /* (1a) */
    if( pTabList->a[1].fg.jointype & JT_CROSS ) return 1;         /* (1b) */
    if( selFlags & SF_UpdateFrom )              return 0;         /* (1c-iii) */

} {
  QUERY PLAN
  |--SCAN t2
  |--SCAN t3 LEFT-JOIN
  `--RIGHT-JOIN t3
     `--SCAN t3
}

# 2023-02-01
# https://sqlite.org/forum/forumpost/1d571c02963355ed
#
# Just because a CTE is used more than once, does not mean it should be
# marked with M10d_Yes and hence prohibited from participating in the
# query flattening optimization.
#
reset_db
db eval {
  CREATE TABLE raw(country,date,total,delta, UNIQUE(country,date));
}
do_eqp_test 400 {
  with recursive
    init(country, date, fin) AS (SELECT country, min(date), max(date) FROM raw WHERE total > 0 GROUP BY country),
    src(country, date) AS (SELECT raw.country, raw.date
        FROM raw JOIN init i on raw.country = i.country AND raw.date > i.date
        ORDER BY raw.country, raw.date),
    vals(country, date, x, y) AS (SELECT src.country, src.date, julianday(raw.date) - julianday(src.date), log(delta+1)
        FROM src JOIN raw on raw.country = src.country AND raw.date > date(src.date,'-7 days') AND raw.date <= src.date AND delta >= 0),
    sums(country, date, x2, x, n, xy, y) AS (SELECT country, date, sum(x*x*1.0), sum(x*1.0), sum(1.0), sum(x*y*1.0), sum(y*1.0) FROM vals GROUP BY 1, 2),
    mult(country, date, m) AS (SELECT country, date, 1.0/(x2 * n - x * x) FROM sums),
    inv(country, date, a,b,c,d) AS (SELECT mult.country, mult.date, n * m, -x * m, -x * m, x2 * m
        FROM mult JOIN sums on sums.country=mult.country AND mult.date=sums.date),
    fit(country, date, a, b) AS (SELECT inv.country, inv.date, a * xy + b * y, c * xy + d * y
        FROM inv
        JOIN mult on mult.country = inv.country AND mult.date = inv.date
        JOIN sums on sums.country = mult.country AND sums.date = mult.date
  )
  SELECT *, nFin/nPrev - 1 AS growth, log(2)/log(nFin/nPrev) AS doubling
    FROM (SELECT f.*, exp(b) - 1 AS nFin, exp(a* (-1) + b) - 1 AS nPrev
            FROM fit f JOIN init i on i.country = f.country AND f.date <= date(i.fin,'-3 days'))
   WHERE nPrev > 0 AND nFin > 0;
} {
  QUERY PLAN
  |--MATERIALIZE sums
  |  |--MATERIALIZE src
  |  |  |--MATERIALIZE init
  |  |  |  `--SCAN raw USING INDEX sqlite_autoindex_raw_1
  |  |  |--SCAN i
  |  |  |--SEARCH raw USING COVERING INDEX sqlite_autoindex_raw_1 (country=? AND date>?)
  |  |  `--USE TEMP B-TREE FOR ORDER BY
  |  |--SCAN src
  |  |--SEARCH raw USING INDEX sqlite_autoindex_raw_1 (country=? AND date>? AND date<?)
  |  `--USE TEMP B-TREE FOR GROUP BY
  |--SCAN sums
  |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
  |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
  |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
  `--SEARCH i USING AUTOMATIC COVERING INDEX (country=?)
}


finish_test