Import SQLite 3.8.7.4.

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.
 #
 #***********************************************************************
 #
 # This file implements tests to verify that the "testable statements" in 
 # the lang_expr.html document are correct.
 #
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 source $testdir/malloc_common.tcl
 
+ifcapable !compound {
+  finish_test
+  return
+}
 
 proc do_expr_test {tn expr type value} {
   uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
     list [list $type $value]
   ]
 }
 
 proc do_qexpr_test {tn expr value} {
   uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
 }
@@ skipping 325 matching lines @@
   set out
 }
 proc reverse_collate {zLeft zRight} {
   string compare [reverse_str $zLeft] [reverse_str $zRight]
 }
 db collate reverse reverse_collate
 
 # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
 # operator that assigns a collating sequence to an expression.
 #
-# EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher
-# precedence (binds more tightly) than any prefix unary operator or any
-# binary operator.
+# EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher
+# precedence (binds more tightly) than any binary operator and any unary
+# prefix operator except "~".
 #
 do_execsql_test e_expr-9.1 { SELECT  'abcd' < 'bbbb'    COLLATE reverse } 0
 do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb')   COLLATE reverse } 1
 do_execsql_test e_expr-9.3 { SELECT  'abcd' <= 'bbbb'   COLLATE reverse } 0
 do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb')  COLLATE reverse } 1
 
 do_execsql_test e_expr-9.5 { SELECT  'abcd' > 'bbbb'    COLLATE reverse } 1
 do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb')   COLLATE reverse } 0
 do_execsql_test e_expr-9.7 { SELECT  'abcd' >= 'bbbb'   COLLATE reverse } 1
 do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb')  COLLATE reverse } 0
@@ skipping 61 matching lines @@
 #
 do_execsql_test e_expr-10.3.1 { SELECT 'is not' }         {{is not}}
 do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
 do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' }         {isn't}
 do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
 
 # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
 # containing hexadecimal data and preceded by a single "x" or "X"
 # character.
 #
-# EVIDENCE-OF: R-39344-59787 For example: X'53514C697465'
+# EVIDENCE-OF: R-19836-11244 Example: X'53514C697465'
 #
 do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
 do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
 do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
 do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
 do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465')     } blob
 
 # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
 # "NULL".
 #
@@ skipping 160 matching lines @@
   list [sqlite3_column_type $stmt 0] \
        [sqlite3_column_type $stmt 1] \
        [sqlite3_column_type $stmt 2] \
        [sqlite3_column_type $stmt 3] 
 } {NULL NULL NULL NULL}
 do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
 
 #-------------------------------------------------------------------------
 # "Test" the syntax diagrams in lang_expr.html.
 #
-# EVIDENCE-OF: R-62067-43884 -- syntax diagram signed-number
+# -- syntax diagram signed-number
 #
 do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
 do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
 do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
 do_execsql_test e_expr-12.1.4 { 
   SELECT 1.4, +1.4, -1.4 
 } {1.4 1.4 -1.4}
 do_execsql_test e_expr-12.1.5 { 
   SELECT 1.5e+5, +1.5e+5, -1.5e+5 
 } {150000.0 150000.0 -150000.0}
 do_execsql_test e_expr-12.1.6 { 
   SELECT 0.0001, +0.0001, -0.0001 
 } {0.0001 0.0001 -0.0001}
 
-# EVIDENCE-OF: R-21258-25489 -- syntax diagram literal-value
+# -- syntax diagram literal-value
 #
 set sqlite_current_time 1
 do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
 do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
 do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
 do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
 do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
 do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
 do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
 do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
 set sqlite_current_time 0
 
-# EVIDENCE-OF: R-57598-59332 -- syntax diagram expr
+# -- syntax diagram expr
 #
-file delete -force test.db2
+forcedelete test.db2
 execsql {
   ATTACH 'test.db2' AS dbname;
   CREATE TABLE dbname.tblname(cname);
 }
 
 proc glob {args} {return 1}
 db function glob glob
 db function match glob
 db function regexp glob
 
@@ skipping 134 matching lines @@
 
   set x 0
   foreach e $elist {
     incr x
     do_test e_expr-12.3.$tn.$x { 
       set rc [catch { execsql "SELECT $e FROM tblname" } msg]
     } {0}
   }
 }
 
-# EVIDENCE-OF: R-49462-56079 -- syntax diagram raise-function
+# -- syntax diagram raise-function
 #
 foreach {tn raiseexpr} {
   1 "RAISE(IGNORE)"
   2 "RAISE(ROLLBACK, 'error message')"
   3 "RAISE(ABORT, 'error message')"
   4 "RAISE(FAIL, 'error message')"
 } {
   do_execsql_test e_expr-12.4.$tn "
     CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
       SELECT $raiseexpr ;
@@ skipping 244 matching lines @@
     SELECT regexp('abc', 'def') 
   } {1 {no such function: regexp}}
   do_catchsql_test e_expr-18.1.2 { 
     SELECT 'abc' REGEXP 'def'
   } {1 {no such function: REGEXP}}
 }
 
 # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
 # the regexp() user function.
 #
-# EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
-# "regexp" is added at run-time, that function will be called in order
-# to implement the REGEXP operator.
+# EVIDENCE-OF: R-65524-61849 If an application-defined SQL function
+# named "regexp" is added at run-time, then the "X REGEXP Y" operator
+# will be implemented as a call to "regexp(Y,X)".
 #
 proc regexpfunc {args} {
   eval lappend ::regexpargs $args
   return 1
 }
 db func regexp -argcount 2 regexpfunc
 set ::regexpargs [list]
 do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
 do_test         e_expr-18.2.2 { set regexpargs } {def abc}
 set ::regexpargs [list]
@@ skipping 190 matching lines @@
 #
 proc rev {str} {
   set ret ""
   set chars [split $str]
   for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
     append ret [lindex $chars $i]
   }
   set ret
 }
 proc reverse {lhs rhs} {
-  string compare [rev $lhs] [ref $rhs]
+  string compare [rev $lhs] [rev $rhs]
 }
 db collate reverse reverse
 do_execsql_test e_expr-23.1.1 {
   CREATE TABLE t1(
     a TEXT     COLLATE NOCASE,
     b          COLLATE REVERSE,
     c INTEGER,
     d BLOB
   );
   INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
 } {}
 do_execsql_test e_expr-23.1.2 {
   SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
 } {B}
 do_execsql_test e_expr-23.1.3 {
   SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
 } {B}
 do_execsql_test e_expr-23.1.4 {
   SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
 } {B}
 do_execsql_test e_expr-23.1.5 {
   SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
-} {A}
+} {B}
 do_execsql_test e_expr-23.1.6 {
   SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
 } {B}
 do_execsql_test e_expr-23.1.7 {
   SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
 } {A}
 do_execsql_test e_expr-23.1.8 {
   SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
 } {B}
 do_execsql_test e_expr-23.1.9 {
@@ skipping 69 matching lines @@
     WHEN ceval(x)=w2 THEN r2 
     ELSE r3 END 
   FROM t2
 } {R1 R2 R3}
 do_test e_expr-26.1.6 { set ::evalcount } {5}
 
 
 #-------------------------------------------------------------------------
 # Test statements related to CAST expressions.
 #
-# EVIDENCE-OF: R-65079-31758 Application of a CAST expression is
-# different to application of a column affinity, as with a CAST
-# expression the storage class conversion is forced even if it is lossy
-# and irrreversible.
+# EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
+# conversion that takes place when a column affinity is applied to a
+# value except that with the CAST operator the conversion always takes
+# place even if the conversion lossy and irreversible, whereas column
+# affinity only changes the data type of a value if the change is
+# lossless and reversible.
 #
 do_execsql_test e_expr-27.1.1 {
   CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
   INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
   SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
 } {blob UVU text 1.23abc real 4.5}
 do_execsql_test e_expr-27.1.2 {
   SELECT 
     typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
     typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
@@ skipping 163 matching lines @@
 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
 do_expr_test e_expr-30.3.4 { CAST('     1 2 3 4' AS INT) } integer 1
 
 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
 # interpreted as an integer number, the result of the conversion is 0.
 #
 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
 
-# EVIDENCE-OF: R-00741-38776 A cast of a REAL value into an INTEGER will
-# truncate the fractional part of the REAL.
+# EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
+# integers only &mdash; conversion of hexadecimal integers stops at
+# the "x" in the "0x" prefix of the hexadecimal integer string and thus
+# result of the CAST is always zero.
+do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
+do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
+
+# EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
+# results in the integer between the REAL value and zero that is closest
+# to the REAL value.
 #
 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
 
-# EVIDENCE-OF: R-06126-36021 If an REAL is too large to be represented
-# as an INTEGER then the result of the cast is the largest negative
-# integer: -9223372036854775808.
+# EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
+# possible signed integer (+9223372036854775807) then the result is the
+# greatest possible signed integer and if the REAL is less than the
+# least possible signed integer (-9223372036854775808) then the result
+# is the least possible signed integer.
 #
-do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
+do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
 do_expr_test e_expr-31.2.3 { 
   CAST(-9223372036854775809.0 AS INT)
 } integer -9223372036854775808
 do_expr_test e_expr-31.2.4 { 
   CAST(9223372036854775809.0 AS INT)
-} integer -9223372036854775808
+} integer 9223372036854775807
 
 
 # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
 # first does a forced conversion into REAL but then further converts the
 # result into INTEGER if and only if the conversion from REAL to INTEGER
 # is lossless and reversible.
 #
 do_expr_test e_expr-32.1.1 { CAST('45'   AS NUMERIC)  } integer 45
 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } integer 45
 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC)  } real 45.2
@@ skipping 45 matching lines @@
 }
 db1 close
 db2 close
 db3 close
 }
 
 #-------------------------------------------------------------------------
 # Test statements related to the EXISTS and NOT EXISTS operators.
 #
 catch { db close }
-file delete -force test.db
+forcedelete test.db
 sqlite3 db test.db
 
 do_execsql_test e_expr-34.1 {
   CREATE TABLE t1(a, b);
   INSERT INTO t1 VALUES(1, 2);
   INSERT INTO t1 VALUES(NULL, 2);
   INSERT INTO t1 VALUES(1, NULL);
   INSERT INTO t1 VALUES(NULL, NULL);
 } {}
 
@@ skipping 60 matching lines @@
   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
+forcedelete 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);
   }
 } {}
 
@@ skipping 66 matching lines @@
 # 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-62477-06476 For example, the values NULL, 0.0, 0,
+# 'english' and '0' are all considered to be false.
+#
+do_execsql_test e_expr-37.1 {
+   SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END;
+} {false}
+do_execsql_test e_expr-37.2 {
+   SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END;
+} {false}
+do_execsql_test e_expr-37.3 {
+   SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END;
+} {false}
+do_execsql_test e_expr-37.4 {
+   SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END;
+} {false}
+do_execsql_test e_expr-37.5 {
+   SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END;
+} {false}
+
+# EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
+# considered to be true.
+#
+do_execsql_test e_expr-37.6 {
+   SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END;
+} {true}
+do_execsql_test e_expr-37.7 {
+   SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END;
+} {true}
+do_execsql_test e_expr-37.8 {
+   SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END;
+} {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
-