[sql] Import SQLite 3.17.0.

third_party/sqlite/src/test/e_expr.test

 # 2010 July 16
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
@@ skipping 829 matching lines @@
 # only evaluated once.
 #
 db func x x
 proc x {} { incr ::xcount ; return [expr $::x] }
 foreach {tn x expr res nEval} {
   1  10  "x() >= 5 AND x() <= 15"  1  2
   2  10  "x() BETWEEN 5 AND 15"    1  1
 
   3   5  "x() >= 5 AND x() <= 5"   1  2
   4   5  "x() BETWEEN 5 AND 5"     1  1
+
+  5   9  "(x(),8) >= (9,7) AND (x(),8)<=(9,10)"  1 2
+  6   9  "(x(),8) BETWEEN (9,7) AND (9,10)"      1 1
 } {
   do_test e_expr-13.1.$tn {
     set ::xcount 0
     set a [execsql "SELECT $expr"]
     list $::xcount $a
   } [list $nEval $res]
 }
 
 # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
 # the same as the precedence as operators == and != and LIKE and groups
@@ skipping 567 matching lines @@
 } {text UVU real 1.23 integer 4}
 
 # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
 # result of the CAST expression is also NULL.
 #
 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) }    null {}
 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) }    null {}
 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) }  null {}
 
+# EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
+# is determined by applying the rules for determining column affinity to
+# the type-name.
+#
+# The R-29283-15561 requirement above is demonstrated by all of the 
+# subsequent e_expr-26 tests.
+#
 # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
 # affinity causes the value to be converted into a BLOB.
 #
 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob)       } blob abc
 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
 
 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
 # the value to TEXT in the encoding of the database connection, then
 # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
@@ skipping 329 matching lines @@
 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);
   }
 } {}
 
-# EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
-# may appear as a scalar quantity.
+# EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
+# is a subquery.
 #
 # 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.
+# EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns
+# is a row value subquery and can only be used as the operand of a
+# comparison operator.
 #
 # The following block tests that errors are returned in a bunch of cases
 # where a subquery returns more than one column.
 #
-set M {only a single result allowed for a SELECT that is part of an expression}
+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 [list 1 $M]
+  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-18318-14995 The value of a subquery expression is the
+# first row of the result from the enclosed SELECT statement.
 #
-# EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
-# row, all rows after the first are ignored.
+# EVIDENCE-OF: R-15900-52156 In other words, an implied "LIMIT 1" is
+# added to the subquery, overriding an explicitly coded LIMIT.
 #
 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');
 } {}
 
 foreach {tn expr restype resval} {
     2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
     3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
     4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
     5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
     6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two
 
     7  { ( SELECT sum(x) FROM t4 )           }         integer 6
     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.
+# EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
+# if the enclosed SELECT statement returns no rows.
 #
 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-62477-06476 For example, the values NULL, 0.0, 0,
 # 'english' and '0' are all considered to be false.
@@ skipping 28 matching lines @@
 } {true}
 do_execsql_test e_expr-37.9 {
    SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END;
 } {true}
 do_execsql_test e_expr-37.10 {
    SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END;
 } {true}
 
 
 finish_test