SQLite

**              or DISTINCT, and
**        (17c) every term within the subquery compound must have a FROM clause
**        (17d) the outer query may not be
**              (17d1) aggregate, or
**              (17d2) DISTINCT
**        (17e) the subquery may not contain window functions, and
**        (17f) the subquery must not be the RHS of a LEFT JOIN.
**        (17g) either the subquery is the first element of the outer
**              query or there are no RIGHT or FULL JOINs in any arm
**              of the subquery.  (This is a duplicate of condition (27b).)
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
**        operator other than UNION ALL because all the other compound
**        operators have an implied DISTINCT which is disallowed by
**        restriction (4).

**        function in the select list or ORDER BY clause, flattening
**        is not attempted.
**
**  (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.  This must be the
**        the case if:
**        (27a) the subquery is not compound query, and
**        (27b) the subquery is a compound query and the RIGHT JOIN occurs
**              in any arm of the compound query.  (See also (17g).)
**
**  (28)  The subquery is not a MATERIALIZED CTE.
**
**  (29)  Either the subquery is not the right-hand operand of a join with an
**        ON or USING clause nor the right-hand operand of a NATURAL JOIN, or
**        the right-most table within the FROM clause of the subquery
**        is not part of an outer join.

    ** opcode. */
    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 (27a) */
  }
  if( pSubitem->fg.isCte && pSubitem->u2.pCteUse->eM10d==M10d_Yes ){
    return 0;       /* (28) */
  }

  /* Restriction (29): 
  **
  ** We do not want two constraints on the same term of the flattened
  ** query where one constraint has EP_InnerON and the other is EP_OuterON.
  ** To prevent this, one or the other of the following conditions must be
  ** false:
  **
  **   (29a)  The right-most entry in the FROM clause of the subquery
  **          must not be part of an outer join.
  **
  **   (29b)  The subquery itself must not be the right operand of a 
  **          NATURAL join or a join that as an ON or USING clause.
  **
  ** These conditions are sufficient to keep an EP_OuterON from being
  ** flattened into an EP_InnerON.  Restrictions (3a) and (27a) prevent
  ** an EP_InnerON from being flattened into an EP_OuterON.
  */
  if( pSubSrc->nSrc>=2
   && (pSubSrc->a[pSubSrc->nSrc-1].fg.jointype & JT_OUTER)!=0
  ){
    if( (pSubitem->fg.jointype & JT_NATURAL)!=0
     || pSubitem->fg.isUsing

       || (pSub1->pPrior && pSub1->op!=TK_ALL)                 /* (17a) */
       || pSub1->pSrc->nSrc<1                                  /* (17c) */
#ifndef SQLITE_OMIT_WINDOWFUNC
       || pSub1->pWin                                          /* (17e) */
#endif
      ){
        return 0;
      }
      if( iFrom>0 && (pSub1->pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
        /* Without this restriction, the JT_LTORJ flag would end up being
        ** omitted on left-hand tables of the right join that is being
        ** flattened. */
        return 0;   /* Restrictions (17g), (27b) */
      }
      testcase( pSub1->pSrc->nSrc>1 );
    }

    /* Restriction (18). */
    if( p->pOrderBy ){
      int ii;

do_execsql_test join8-25010 {
  SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
} {- - - - - x}
do_execsql_test join8-25020 {
  SELECT 1 FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
} {1}

# 2022-07-13
# forum/forumpost/174afeae57
#
reset_db
db null -
do_execsql_test join8-26000 {
  CREATE TABLE t1(a INT);
  CREATE TABLE t2(b INT, c INT);
  CREATE VIEW t3(d) AS SELECT NULL FROM t2 FULL OUTER JOIN t1 ON c=a UNION ALL SELECT b FROM t2;
  INSERT INTO t1(a) VALUES (NULL);
  INSERT INTO t2(b, c) VALUES (99, NULL);
  SELECT DISTINCT b, c, d FROM t2, t3 WHERE b<>0
   UNION SELECT DISTINCT b, c, d FROM t2, t3 WHERE b ISNULL;
} {99 - - 99 - 99}

finish_test