SQLite

  /* Check that the CTE name is unique within this WITH clause. If
  ** not, store an error in the Parse structure. */
  zName = sqlite3NameFromToken(pParse->db, pName);
  if( zName && pWith ){
    int i;
    for(i=0; i<pWith->nCte; i++){
      if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
        sqlite3ErrorMsg(pParse, "duplicate cte name: %s", zName);
      }
    }
  }

  if( pWith ){
    int nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte);
    pNew = sqlite3DbRealloc(db, pWith, nByte);

        }
      }
    }

    /* Only one recursive reference is permitted. */ 
    if( pTab->nRef>2 ){
      sqlite3ErrorMsg(
          pParse, "multiple recursive references in cte: %s", pCte->zName
      );
      return WRC_Abort;
    }
    assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));

    pCte->zErr = "circular reference to cte: %s";
    sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel);

    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
    pEList = pLeft->pEList;
    if( pCte->pCols ){
      if( pEList->nExpr!=pCte->pCols->nExpr ){
        sqlite3ErrorMsg(pParse, "cte \"%s\" returns %d values for %d columns",
            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
        );
        return WRC_Abort;
      }
      pEList = pCte->pCols;
    }
    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);

    if( bMayRecursive ){
      if( pSel->selFlags & SF_Recursive ){
        pCte->zErr = "multiple recursive references in cte: %s";
      }else{
        pCte->zErr = "recursive reference may not appear in sub-query: %s";
      }
      sqlite3WalkSelect(pWalker, pSel);
    }
    pCte->zErr = 0;
  }

  return SQLITE_OK;

} {1 2}

#-------------------------------------------------------------------------
do_catchsql_test 3.1 {
  WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
       tmp1(a) AS ( SELECT * FROM tmp2 )
  SELECT * FROM tmp1;
} {1 {circular reference to cte: tmp1}}

do_catchsql_test 3.2 {
  CREATE TABLE t2(x INTEGER);
  WITH tmp(a) AS (SELECT * FROM t1),
       tmp(a) AS (SELECT * FROM t1)
  SELECT * FROM tmp;
} {1 {duplicate cte name: tmp}}

do_execsql_test 3.3 {
  CREATE TABLE t3(x);
  CREATE TABLE t4(x);

  INSERT INTO t3 VALUES('T3');
  INSERT INTO t4 VALUES('T4');

  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
  SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}

do_catchsql_test 5.6.1 {
  WITH i(x, y) AS ( VALUES(1) )
  SELECT * FROM i;
} {1 {cte "i" returns 1 values for 2 columns}}

do_catchsql_test 5.6.2 {
  WITH i(x) AS ( VALUES(1,2) )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.3 {
  CREATE TABLE t5(a, b);
  WITH i(x) AS ( SELECT * FROM t5 )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.4 {
  WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.5 {
  WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
  SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

do_catchsql_test 5.6.6 {
  WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
  SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

do_catchsql_test 5.6.7 {
  WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
  CREATE TABLE f(
      id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
  );

do_catchsql_test 7.4 {
  WITH t(id) AS (
    VALUES(2)
    UNION ALL
    SELECT i FROM tree WHERE p IN (SELECT id FROM t)
  ) 
  SELECT id FROM t;
} {1 {recursive reference may not appear in sub-query: t}}

do_catchsql_test 7.5 {
  WITH t(id) AS (
    VALUES(2)
    UNION ALL
    SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
  ) 
  SELECT id FROM t;
} {1 {multiple recursive references in cte: t}}

do_catchsql_test 7.6 {
  WITH t(id) AS (
    SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
    UNION ALL
    SELECT i FROM tree, t WHERE p = id
  ) 
  SELECT id FROM t;
} {1 {circular reference to cte: t}}



finish_test