SQLite

**
**        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).
**
**        Also, each component of the sub-query must return the same number
**        of result columns. This is actually a requirement for any compound
**        SELECT statement, but all the code here does is make sure that no
**        such (illegal) sub-query is flattened. The caller will detect the
**        syntax error and return a detailed message.
**
**  (18)  If the sub-query is a compound select, then all terms of the
**        ORDER by clause of the parent must be simple references to 
**        columns of the sub-query.
**
**  (19)  The subquery does not use LIMIT or the outer query does not
**        have a WHERE clause.

    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
      assert( pSub->pSrc!=0 );
      if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
       || (pSub1->pPrior && pSub1->op!=TK_ALL) 
       || pSub1->pSrc->nSrc<1
       || pSub->pEList->nExpr!=pSub1->pEList->nExpr
      ){
        return 0;
      }
      testcase( pSub1->pSrc->nSrc>1 );
    }

    /* Restriction 18. */

source $testdir/tester.tcl

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
  return
}
set ::testprefix select6

do_test select6-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);

} {2 12 3 13 4 14}
do_test select6-9.11 {
  execsql {
    SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
  }
} {2 12 3 13 4 14}


#-------------------------------------------------------------------------
# Test that if a UNION ALL sub-query that would otherwise be eligible for
# flattening consists of two or more SELECT statements that do not all 
# return the same number of result columns, the error is detected.
#
do_execsql_test 10.1 {
  CREATE TABLE t(i,j,k);
  CREATE TABLE j(l,m);
  CREATE TABLE k(o);
}

set err [list 1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}]

do_execsql_test 10.2 {
  SELECT * FROM (SELECT * FROM t), j;
}
do_catchsql_test 10.3 {
  SELECT * FROM t UNION ALL SELECT * FROM j
} $err
do_catchsql_test 10.4 {
  SELECT * FROM (SELECT i FROM t UNION ALL SELECT l, m FROM j)
} $err
do_catchsql_test 10.5 {
  SELECT * FROM (SELECT j FROM t UNION ALL SELECT * FROM j)
} $err
do_catchsql_test 10.6 {
  SELECT * FROM (SELECT * FROM t UNION ALL SELECT * FROM j)
} $err
do_catchsql_test 10.7 {
  SELECT * FROM (
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j UNION ALL
    SELECT * FROM k
  )
} $err
do_catchsql_test 10.8 {
  SELECT * FROM (
    SELECT * FROM k UNION ALL
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j 
  )
} $err


finish_test