SQLite

  isAgg = (p->selFlags & SF_Aggregate)!=0;
  assert( pEList!=0 );

  /* Begin generating code.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    goto select_end;
  }
#endif

  /* Generate code for all sub-queries in the FROM clause
  */
#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
    struct SrcList_item *pItem = &pTabList->a[i];
    SelectDest dest;

      if( mxSelect && cnt>mxSelect ){
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, pDest);
  }
#endif

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    goto select_end;
  }
#endif

  /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  ** GROUP BY might use an index, DISTINCT never does.
  */
  assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 );
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){

  2 { EXISTS (SELECT NULL FROM t1) }  { EXISTS (SELECT 'bread' FROM t1) }
} {
  set res [db one "SELECT $e1"]
  do_expr_test e_expr-34.5.${tn}a $e1 integer $res
  do_expr_test e_expr-34.5.${tn}b $e2 integer $res
}

#-------------------------------------------------------------------------
# Test statements related to scalar sub-queries.

#

catch { db close }
file delete -force test.db
sqlite3 db test.db
do_test e_expr-35.0 {
  execsql {
    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES('one', 'two');
    INSERT INTO t2 VALUES('three', NULL);
    INSERT INTO t2 VALUES(4, 5.0);
  }
} {}
#-------------------------------------------------------------------------
# Test statements related to the IN and NOT IN operators.
#

# EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
# may appear as a scalar quantity.
#
# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
# aggregate and compound SELECT queries (queries with keywords like
# UNION or EXCEPT) are allowed as scalar subqueries.
#
do_expr_test e_expr-35.1.1 { (SELECT 35)   } integer 35
do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}

do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4

do_expr_test e_expr-35.1.5 { 
  (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
} null {}
do_expr_test e_expr-35.1.6 { 
  (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
} integer 4

# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
# return a result set with a single column.
#
set M {only a single result allowed for a SELECT that is part of an expression}
foreach {tn sql} {
  1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
  2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
  3     { SELECT (SELECT 1, 2) }
  4     { SELECT (SELECT NULL, NULL, NULL) }
  5     { SELECT (SELECT * FROM t2) }
  6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
} {
  do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
}

finish_test

      SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.6 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
      SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_test in-12.7 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION SELECT a, b FROM t2
      SELECT a, b FROM t3 UNION SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
do_test in-12.8 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
      SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
do_test in-12.9 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
      SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
}

do_test in-12.10 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.11 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.12 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.13 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#
# When it appears in such a set, NULL is handled as an "unknown value".