SQLite

    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES('one', 'two');
    INSERT INTO t2 VALUES('three', NULL);
    INSERT INTO t2 VALUES(4, 5.0);
  }
} {}

# 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.

#
# The following block tests that errors are returned in a bunch of cases
# where a subquery returns more than one column.
#
set M {/1 {sub-select returns [23] columns - expected 1}/}
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 $M
}

# EVIDENCE-OF: R-35764-28041 The result of the expression is the value
# of the only column in the first row returned by the SELECT statement.
#
# EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
# row, all rows after the first are ignored.
#
do_execsql_test e_expr-36.3.1 {
  CREATE TABLE t4(x, y);
  INSERT INTO t4 VALUES(1, 'one');
  INSERT INTO t4 VALUES(2, 'two');
  INSERT INTO t4 VALUES(3, 'three');
} {}

    8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
    9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 

} {
  do_expr_test e_expr-36.3.$tn $expr $restype $resval
}

# EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
# value of the expression is NULL.
#
foreach {tn expr} {
    1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
    2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
} {
  do_expr_test e_expr-36.4.$tn $expr null {}
}

# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
# rows, two NULL values are considered to be equal.
#
do_select_tests e_select-5.5 {
  1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
}

# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
# sequence to compare text values with apply.
#
do_select_tests e_select-5.6 {
  1  "SELECT DISTINCT b FROM h1"                  {one I i four IV iv}
  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {one I four IV}
  3  "SELECT DISTINCT x FROM h2"                  {One Two Three Four}
  4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
    One Two Three Four one two three four

#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl

# EVIDENCE-OF: R-58875-56087 The IN and NOT IN operators take a single
# scalar operand on the left and a vector operand on the right formed by
# an explicit list of zero or more scalars or by a single subquery.
#
# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
# result of IN is false and the result of NOT IN is true, regardless of
# the left operand and even if the left operand is NULL.
#
# EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized
# list of scalar values on the right-hand side of an IN or NOT IN
# operator to be an empty list but most other SQL database database