diff options
Diffstat (limited to '')
| -rw-r--r-- | test/e_expr.test | 1988 |
1 files changed, 1988 insertions, 0 deletions
diff --git a/test/e_expr.test b/test/e_expr.test new file mode 100644 index 0000000..3f8d5ad --- /dev/null +++ b/test/e_expr.test @@ -0,0 +1,1988 @@ +# 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] +} + +# Set up three global variables: +# +# ::opname An array mapping from SQL operator to an easy to parse +# name. The names are used as part of test case names. +# +# ::opprec An array mapping from SQL operator to a numeric +# precedence value. Operators that group more tightly +# have lower numeric precedences. +# +# ::oplist A list of all SQL operators supported by SQLite. +# +foreach {op opn} { + || cat * mul / div % mod + add + - sub << lshift >> rshift & bitand | bitor + < less <= lesseq > more >= moreeq = eq1 + == eq2 <> ne1 != ne2 IS is LIKE like + GLOB glob AND and OR or MATCH match REGEXP regexp + {IS NOT} isnt +} { + set ::opname($op) $opn +} +set oplist [list] +foreach {prec opl} { + 1 || + 2 {* / %} + 3 {+ -} + 4 {<< >> & |} + 5 {< <= > >=} + 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} + 7 AND + 8 OR +} { + foreach op $opl { + set ::opprec($op) $prec + lappend oplist $op + } +} + + +# Hook in definitions of MATCH and REGEX. The following implementations +# cause MATCH and REGEX to behave similarly to the == operator. +# +proc matchfunc {a b} { return [expr {$a==$b}] } +proc regexfunc {a b} { return [expr {$a==$b}] } +db func match -argcount 2 matchfunc +db func regexp -argcount 2 regexfunc + +#------------------------------------------------------------------------- +# Test cases e_expr-1.* attempt to verify that all binary operators listed +# in the documentation exist and that the relative precedences of the +# operators are also as the documentation suggests. +# +# X-EVIDENCE-OF: R-15514-65163 SQLite understands the following binary +# operators, in order from highest to lowest precedence: || * / % + - +# << >> & | < <= > >= = == != <> IS IS +# NOT IN LIKE GLOB MATCH REGEXP AND OR +# +# X-EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same +# precedence as =. +# + +unset -nocomplain untested +foreach op1 $oplist { + foreach op2 $oplist { + set untested($op1,$op2) 1 + foreach {tn A B C} { + 1 22 45 66 + 2 0 0 0 + 3 0 0 1 + 4 0 1 0 + 5 0 1 1 + 6 1 0 0 + 7 1 0 1 + 8 1 1 0 + 9 1 1 1 + 10 5 6 1 + 11 1 5 6 + 12 1 5 5 + 13 5 5 1 + + 14 5 2 1 + 15 1 4 1 + 16 -1 0 1 + 17 0 1 -1 + + } { + set testname "e_expr-1.$opname($op1).$opname($op2).$tn" + + # If $op2 groups more tightly than $op1, then the result + # of executing $sql1 whould be the same as executing $sql3. + # If $op1 groups more tightly, or if $op1 and $op2 have + # the same precedence, then executing $sql1 should return + # the same value as $sql2. + # + set sql1 "SELECT $A $op1 $B $op2 $C" + set sql2 "SELECT ($A $op1 $B) $op2 $C" + set sql3 "SELECT $A $op1 ($B $op2 $C)" + + set a2 [db one $sql2] + set a3 [db one $sql3] + + do_execsql_test $testname $sql1 [list [ + if {$opprec($op2) < $opprec($op1)} {set a3} {set a2} + ]] + if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) } + } + } +} + +foreach op {* AND OR + || & |} { unset untested($op,$op) } +unset untested(+,-) ;# Since (a+b)-c == a+(b-c) +unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c) + +do_test e_expr-1.1 { array names untested } {} + +# At one point, test 1.2.2 was failing. Instead of the correct result, it +# was returning {1 1 0}. This would seem to indicate that LIKE has the +# same precedence as '<'. Which is incorrect. It has lower precedence. +# +do_execsql_test e_expr-1.2.1 { + SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1) +} {1 1 0} +do_execsql_test e_expr-1.2.2 { + SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2) +} {0 1 0} + +# Showing that LIKE and == have the same precedence +# +do_execsql_test e_expr-1.2.3 { + SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1) +} {1 1 0} +do_execsql_test e_expr-1.2.4 { + SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1) +} {1 1 0} + +# Showing that < groups more tightly than == (< has higher precedence). +# +do_execsql_test e_expr-1.2.5 { + SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1) +} {1 1 0} +do_execsql_test e_expr-1.6 { + SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2) +} {0 1 0} + +#------------------------------------------------------------------------- +# Check that the four unary prefix operators mentioned in the +# documentation exist. +# +# X-EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these: +# - + ~ NOT +# +do_execsql_test e_expr-2.1 { SELECT - 10 } {-10} +do_execsql_test e_expr-2.2 { SELECT + 10 } {10} +do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11} +do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0} + +#------------------------------------------------------------------------- +# Tests for the two statements made regarding the unary + operator. +# +# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op. +# +# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers, +# blobs or NULL and it always returns a result with the same value as +# the operand. +# +foreach {tn literal type} { + 1 'helloworld' text + 2 45 integer + 3 45.2 real + 4 45.0 real + 5 X'ABCDEF' blob + 6 NULL null +} { + set sql " SELECT quote( + $literal ), typeof( + $literal) " + do_execsql_test e_expr-3.$tn $sql [list $literal $type] +} + +#------------------------------------------------------------------------- +# Check that both = and == are both acceptable as the "equals" operator. +# Similarly, either != or <> work as the not-equals operator. +# +# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==. +# +# EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or +# <>. +# +foreach {tn literal different} { + 1 'helloworld' '12345' + 2 22 23 + 3 'xyz' X'78797A' + 4 X'78797A00' 'xyz' +} { + do_execsql_test e_expr-4.$tn " + SELECT $literal = $literal, $literal == $literal, + $literal = $different, $literal == $different, + $literal = NULL, $literal == NULL, + $literal != $literal, $literal <> $literal, + $literal != $different, $literal <> $different, + $literal != NULL, $literal != NULL + + " {1 1 0 0 {} {} 0 0 1 1 {} {}} +} + +#------------------------------------------------------------------------- +# Test the || operator. +# +# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins +# together the two strings of its operands. +# +foreach {tn a b} { + 1 'helloworld' '12345' + 2 22 23 +} { + set as [db one "SELECT $a"] + set bs [db one "SELECT $b"] + + do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"] +} + +#------------------------------------------------------------------------- +# Test the % operator. +# +# EVIDENCE-OF: R-53431-59159 The % operator casts both of its operands +# to type INTEGER and then computes the remainder after dividing the +# left integer by the right integer. +# +do_execsql_test e_expr-6.1 {SELECT 72%5} {2} +do_execsql_test e_expr-6.2 {SELECT 72%-5} {2} +do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2} +do_execsql_test e_expr-6.4 {SELECT -72%5} {-2} +do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0} + +#------------------------------------------------------------------------- +# EVIDENCE-OF: R-15904-00746 The result of any binary operator is either +# a numeric value or NULL, except for the || concatenation operator, and +# the -> and ->> extract operators which evaluate to either +# NULL or a text value. +# +set literals { + 1 'abc' 2 'hexadecimal' 3 '' + 4 123 5 -123 6 0 + 7 123.4 8 0.0 9 -123.4 + 10 X'ABCDEF' 11 X'' 12 X'0000' + 13 NULL +} +foreach op $oplist { + foreach {n1 rhs} $literals { + foreach {n2 lhs} $literals { + + set t [db one " SELECT typeof($lhs $op $rhs) "] + do_test e_expr-7.$opname($op).$n1.$n2 { + expr { + ($op=="||" && ($t == "text" || $t == "null")) + || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null")) + } + } 1 + + }} +} + +#------------------------------------------------------------------------- +# Test the IS and IS NOT operators. +# +# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and +# != except when one or both of the operands are NULL. +# +# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL, +# then the IS operator evaluates to 1 (true) and the IS NOT operator +# evaluates to 0 (false). +# +# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is +# not, then the IS operator evaluates to 0 (false) and the IS NOT +# operator is 1 (true). +# +# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT +# expression to evaluate to NULL. +# +do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1} +do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0} +do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0} +do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1} +do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}} +do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}} +do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}} +do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1} +do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0} +do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1} +do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1} +do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0} +do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}} +do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}} +do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}} +do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0} + +foreach {n1 rhs} $literals { + foreach {n2 lhs} $literals { + if {$rhs!="NULL" && $lhs!="NULL"} { + set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"] + } else { + set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \ + [expr {$lhs!="NULL" || $rhs!="NULL"}] + ] + } + set test e_expr-8.2.$n1.$n2 + do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq + do_execsql_test $test.2 " + SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL + " {0 0} + } +} + +#------------------------------------------------------------------------- +# Run some tests on the COLLATE "unary postfix operator". +# +# This collation sequence reverses both arguments before using +# [string compare] to compare them. For example, when comparing the +# strings 'one' and 'four', return the result of: +# +# string compare eno ruof +# +proc reverse_str {zStr} { + set out "" + foreach c [split $zStr {}] { set out "${c}${out}" } + 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. +# +# X-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 + +do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1 +do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0 +do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1 +do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0 +do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1 +do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0 + +do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0 +do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1 +do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0 +do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1 +do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0 +do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1 + +do_execsql_test e_expr-9.22 { + SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase +} 1 +do_execsql_test e_expr-9.23 { + SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase +} 0 + +# EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE +# operator overrides the collating sequence determined by the COLLATE +# clause in a table column definition. +# +do_execsql_test e_expr-9.24 { + CREATE TABLE t24(a COLLATE NOCASE, b); + INSERT INTO t24 VALUES('aaa', 1); + INSERT INTO t24 VALUES('bbb', 2); + INSERT INTO t24 VALUES('ccc', 3); +} {} +do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0} +do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0} +do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0} +do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0} + +#------------------------------------------------------------------------- +# Test statements related to literal values. +# +# EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating +# point numbers, strings, BLOBs, or NULLs. +# +do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer} +do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real} +do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text} +do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob} +do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null} + +# "Scientific notation is supported for point literal values." +# +do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real} +do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real} +do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034} +do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0} + +# EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing +# the string in single quotes ('). +# +# EVIDENCE-OF: R-07100-06606 A single quote within the string can be +# encoded by putting two single quotes in a row - as in Pascal. +# +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-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". +# +do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}} +do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null} + +#------------------------------------------------------------------------- +# Test statements related to bound parameters +# + +proc parameter_test {tn sql params result} { + set stmt [sqlite3_prepare_v2 db $sql -1] + + foreach {number name} $params { + set nm [sqlite3_bind_parameter_name $stmt $number] + do_test $tn.name.$number [list set {} $nm] $name + sqlite3_bind_int $stmt $number [expr -1 * $number] + } + + sqlite3_step $stmt + + set res [list] + for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} { + lappend res [sqlite3_column_text $stmt $i] + } + + set rc [sqlite3_finalize $stmt] + do_test $tn.rc [list set {} $rc] SQLITE_OK + do_test $tn.res [list set {} $res] $result +} + +# EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN +# holds a spot for the NNN-th parameter. NNN must be between 1 and +# SQLITE_MAX_VARIABLE_NUMBER. +# +set mvn $SQLITE_MAX_VARIABLE_NUMBER +parameter_test e_expr-11.1 " + SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4 +" "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4" + +set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER" +foreach {tn param_number} [list \ + 2 0 \ + 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \ + 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \ + 5 12345678903456789034567890234567890 \ + 6 2147483648 \ + 7 2147483649 \ + 8 4294967296 \ + 9 4294967297 \ + 10 9223372036854775808 \ + 11 9223372036854775809 \ + 12 18446744073709551616 \ + 13 18446744073709551617 \ +] { + do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg] +} + +# EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a +# number creates a parameter with a number one greater than the largest +# parameter number already assigned. +# +# EVIDENCE-OF: R-42938-07030 If this means the parameter number is +# greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error. +# +parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1 +parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2} +parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6} +parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5} +parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" { + 1 {} 456 ?456 457 {} +} {-1 -456 -457} +parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" { + 1 {} 456 ?456 4 ?4 457 {} +} {-1 -456 -4 -457} +foreach {tn sql} [list \ + 1 "SELECT ?$mvn, ?" \ + 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \ + 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \ +] { + do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}] +} + +# EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name +# holds a spot for a named parameter with the name :AAAA. +# +# Identifiers in SQLite consist of alphanumeric, '_' and '$' characters, +# and any UTF characters with codepoints larger than 127 (non-ASCII +# characters). +# +parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1 +parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1 +parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1 +parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1 +parameter_test e_expr-11.2.5 " + SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 +" "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 +parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1 + +# EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon, +# except that the name of the parameter created is @AAAA. +# +parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1 +parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1 +parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1 +parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1 +parameter_test e_expr-11.3.5 " + SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 +" "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 +parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1 + +# EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier +# name also holds a spot for a named parameter with the name $AAAA. +# +# EVIDENCE-OF: R-55025-21042 The identifier name in this case can +# include one or more occurrences of "::" and a suffix enclosed in +# "(...)" containing any text at all. +# +# Note: Looks like an identifier cannot consist entirely of "::" +# characters or just a suffix. Also, the other named variable characters +# (: and @) work the same way internally. Why not just document it that way? +# +parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1 +parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1 +parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1 +parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1 +parameter_test e_expr-11.4.5 " + SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 +" "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 +parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1 + +parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1 +parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1 +parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1 + +# EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The +# number assigned is one greater than the largest parameter number +# already assigned. +# +# EVIDENCE-OF: R-42620-22184 If this means the parameter would be +# assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an +# error. +# +parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2} +parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124} +parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} { + 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c +} {-1 -8 -9 -10 -2 -11} +foreach {tn sql} [list \ + 1 "SELECT ?$mvn, \$::a" \ + 2 "SELECT ?$mvn, ?4, @a1" \ + 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \ +] { + do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}] +} + +# EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values +# using sqlite3_bind() are treated as NULL. +# +do_test e_expr-11.7.1 { + set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1] + sqlite3_step $stmt + + 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. +# +# -- 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} + +# -- 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 + +# -- syntax diagram expr +# +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 + +foreach {tn expr} { + 1 123 + 2 123.4e05 + 3 'abcde' + 4 X'414243' + 5 NULL + 6 CURRENT_TIME + 7 CURRENT_DATE + 8 CURRENT_TIMESTAMP + + 9 ? + 10 ?123 + 11 @hello + 12 :world + 13 $tcl + 14 $tcl(array) + + 15 cname + 16 tblname.cname + 17 dbname.tblname.cname + + 18 "+ EXPR" + 19 "- EXPR" + 20 "NOT EXPR" + 21 "~ EXPR" + + 22 "EXPR1 || EXPR2" + 23 "EXPR1 * EXPR2" + 24 "EXPR1 / EXPR2" + 25 "EXPR1 % EXPR2" + 26 "EXPR1 + EXPR2" + 27 "EXPR1 - EXPR2" + 28 "EXPR1 << EXPR2" + 29 "EXPR1 >> EXPR2" + 30 "EXPR1 & EXPR2" + 31 "EXPR1 | EXPR2" + 32 "EXPR1 < EXPR2" + 33 "EXPR1 <= EXPR2" + 34 "EXPR1 > EXPR2" + 35 "EXPR1 >= EXPR2" + 36 "EXPR1 = EXPR2" + 37 "EXPR1 == EXPR2" + 38 "EXPR1 != EXPR2" + 39 "EXPR1 <> EXPR2" + 40 "EXPR1 IS EXPR2" + 41 "EXPR1 IS NOT EXPR2" + 42 "EXPR1 AND EXPR2" + 43 "EXPR1 OR EXPR2" + + 44 "count(*)" + 45 "count(DISTINCT EXPR)" + 46 "substr(EXPR, 10, 20)" + 47 "changes()" + + 48 "( EXPR )" + + 49 "CAST ( EXPR AS integer )" + 50 "CAST ( EXPR AS 'abcd' )" + 51 "CAST ( EXPR AS 'ab$ $cd' )" + + 52 "EXPR COLLATE nocase" + 53 "EXPR COLLATE binary" + + 54 "EXPR1 LIKE EXPR2" + 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR" + 56 "EXPR1 GLOB EXPR2" + 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR" + 58 "EXPR1 REGEXP EXPR2" + 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR" + 60 "EXPR1 MATCH EXPR2" + 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR" + 62 "EXPR1 NOT LIKE EXPR2" + 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR" + 64 "EXPR1 NOT GLOB EXPR2" + 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR" + 66 "EXPR1 NOT REGEXP EXPR2" + 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR" + 68 "EXPR1 NOT MATCH EXPR2" + 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR" + + 70 "EXPR ISNULL" + 71 "EXPR NOTNULL" + 72 "EXPR NOT NULL" + + 73 "EXPR1 IS EXPR2" + 74 "EXPR1 IS NOT EXPR2" + + 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2" + 76 "EXPR BETWEEN EXPR1 AND EXPR2" + + 77 "EXPR NOT IN (SELECT cname FROM tblname)" + 78 "EXPR NOT IN (1)" + 79 "EXPR NOT IN (1, 2, 3)" + 80 "EXPR NOT IN tblname" + 81 "EXPR NOT IN dbname.tblname" + 82 "EXPR IN (SELECT cname FROM tblname)" + 83 "EXPR IN (1)" + 84 "EXPR IN (1, 2, 3)" + 85 "EXPR IN tblname" + 86 "EXPR IN dbname.tblname" + + 87 "EXISTS (SELECT cname FROM tblname)" + 88 "NOT EXISTS (SELECT cname FROM tblname)" + + 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END" + 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END" + 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" + 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" + 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END" + 94 "CASE WHEN EXPR1 THEN EXPR2 END" + 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" + 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" +} { + + # If the expression string being parsed contains "EXPR2", then replace + # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it + # contains "EXPR", then replace EXPR with an arbitrary SQL expression. + # + set elist [list $expr] + if {[string match *EXPR2* $expr]} { + set elist [list] + foreach {e1 e2} { cname "34+22" } { + lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr] + } + } + if {[string match *EXPR* $expr]} { + set elist2 [list] + foreach el $elist { + foreach e { cname "34+22" } { + lappend elist2 [string map [list EXPR $e] $el] + } + } + set elist $elist2 + } + + 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} + } +} + +# -- 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 ; + END; + " {} +} + +#------------------------------------------------------------------------- +# Test the statements related to the BETWEEN operator. +# +# EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically +# equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent +# to "x>=y AND x<=z" except that with BETWEEN, the x expression is +# 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] +} + +# X-EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is +# the same as the precedence as operators == and != and LIKE and groups +# left to right. +# +# Therefore, BETWEEN groups more tightly than operator "AND", but less +# so than "<". +# +do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0 +do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1 +do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1 +do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0 + +do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1 +do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1 +do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0 +do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0 + +do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1 +do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0 +do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1 +do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1 +do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0 + +do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0 +do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0 +do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1 +do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0 +do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0 +do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1 + +do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1 +do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1 +do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0 +do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0 +do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0 +do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1 + +#------------------------------------------------------------------------- +# Test the statements related to the LIKE and GLOB operators. +# +# EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching +# comparison. +# +# EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE +# operator contains the pattern and the left hand operand contains the +# string to match against the pattern. +# +do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0 +do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1 + +# EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern +# matches any sequence of zero or more characters in the string. +# +do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1 +do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1 +do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1 + +# EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern +# matches any single character in the string. +# +do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0 +do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1 +do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0 + +# EVIDENCE-OF: R-59007-20454 Any other character matches itself or its +# lower/upper case equivalent (i.e. case-insensitive matching). +# +do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1 +do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1 +do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0 + +# EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case +# for ASCII characters by default. +# +# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by +# default for unicode characters that are beyond the ASCII range. +# +# EVIDENCE-OF: R-44381-11669 the expression +# 'a' LIKE 'A' is TRUE but +# 'æ' LIKE 'Æ' is FALSE. +# +# The restriction to ASCII characters does not apply if the ICU +# library is compiled in. When ICU is enabled SQLite does not act +# as it does "by default". +# +do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1 +ifcapable !icu { + do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0 +} + +# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present, +# then the expression following the ESCAPE keyword must evaluate to a +# string consisting of a single character. +# +do_catchsql_test e_expr-14.6.1 { + SELECT 'A' LIKE 'a' ESCAPE '12' +} {1 {ESCAPE expression must be a single character}} +do_catchsql_test e_expr-14.6.2 { + SELECT 'A' LIKE 'a' ESCAPE '' +} {1 {ESCAPE expression must be a single character}} +do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1} +do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1} + +# EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE +# pattern to include literal percent or underscore characters. +# +# EVIDENCE-OF: R-13345-31830 The escape character followed by a percent +# symbol (%), underscore (_), or a second instance of the escape +# character itself matches a literal percent symbol, underscore, or a +# single escape character, respectively. +# +do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1 +do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0 + +do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1 +do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0 + +do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1 +do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0 +do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0 + +# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by +# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z). +# +proc likefunc {args} { + eval lappend ::likeargs $args + return 1 +} +db func like -argcount 2 likefunc +db func like -argcount 3 likefunc +set ::likeargs [list] +do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1 +do_test e_expr-15.1.2 { set likeargs } {def abc} +set ::likeargs [list] +do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1 +do_test e_expr-15.1.4 { set likeargs } {def abc X} +db close +sqlite3 db test.db + +# EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case +# sensitive using the case_sensitive_like pragma. +# +do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1 +do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 +do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {} +do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0 +do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0 +do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 +do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 +do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {} +do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1 +do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 +do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 +do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 + +# EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but +# uses the Unix file globbing syntax for its wildcards. +# +# EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE. +# +do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0 +do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1 +do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0 +do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1 + +do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1 +do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0 +do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0 + +# EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the +# NOT keyword to invert the sense of the test. +# +do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1 +do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0 +do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0 +do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0 +do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1 + +db nullvalue null +do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null +do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null +do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null +do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null +db nullvalue {} + +# EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by +# calling the function glob(Y,X) and can be modified by overriding that +# function. +proc globfunc {args} { + eval lappend ::globargs $args + return 1 +} +db func glob -argcount 2 globfunc +set ::globargs [list] +do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1 +do_test e_expr-17.3.2 { set globargs } {def abc} +set ::globargs [list] +do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0 +do_test e_expr-17.3.4 { set globargs } {Y X} +sqlite3 db test.db + +# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by +# default and so use of the REGEXP operator will normally result in an +# error message. +# +# There is a regexp function if ICU is enabled though. +# +ifcapable !icu { + do_catchsql_test e_expr-18.1.1 { + 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-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] +do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 +do_test e_expr-18.2.4 { set regexpargs } {Y X} +sqlite3 db test.db + +# EVIDENCE-OF: R-42037-37826 The default match() function implementation +# raises an exception and is not really useful for anything. +# +do_catchsql_test e_expr-19.1.1 { + SELECT 'abc' MATCH 'def' +} {1 {unable to use function MATCH in the requested context}} +do_catchsql_test e_expr-19.1.2 { + SELECT match('abc', 'def') +} {1 {unable to use function MATCH in the requested context}} + +# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for +# the match() application-defined function. +# +# EVIDENCE-OF: R-06021-09373 But extensions can override the match() +# function with more helpful logic. +# +proc matchfunc {args} { + eval lappend ::matchargs $args + return 1 +} +db func match -argcount 2 matchfunc +set ::matchargs [list] +do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 +do_test e_expr-19.2.2 { set matchargs } {def abc} +set ::matchargs [list] +do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 +do_test e_expr-19.2.4 { set matchargs } {Y X} +sqlite3 db test.db + +#------------------------------------------------------------------------- +# Test cases for the testable statements related to the CASE expression. +# +# EVIDENCE-OF: R-57495-24088 There are two fundamental forms of the CASE +# expression: those with a base expression and those without. +# +do_execsql_test e_expr-20.1 { + SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; +} {true} +do_execsql_test e_expr-20.2 { + SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; +} {false} + +proc var {nm} { + lappend ::varlist $nm + return [set "::$nm"] +} +db func var var + +# EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each +# WHEN expression is evaluated and the result treated as a boolean, +# starting with the leftmost and continuing to the right. +# +foreach {a b c} {0 0 0} break +set varlist [list] +do_execsql_test e_expr-21.1.1 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' END +} {{}} +do_test e_expr-21.1.2 { set varlist } {a b c} +set varlist [list] +do_execsql_test e_expr-21.1.3 { + SELECT CASE WHEN var('c') THEN 'C' + WHEN var('b') THEN 'B' + WHEN var('a') THEN 'A' + ELSE 'no result' + END +} {{no result}} +do_test e_expr-21.1.4 { set varlist } {c b a} + +# EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the +# evaluation of the THEN expression that corresponds to the first WHEN +# expression that evaluates to true. +# +foreach {a b c} {0 1 0} break +do_execsql_test e_expr-21.2.1 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + ELSE 'no result' + END +} {B} +foreach {a b c} {0 1 1} break +do_execsql_test e_expr-21.2.2 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + ELSE 'no result' + END +} {B} +foreach {a b c} {0 0 1} break +do_execsql_test e_expr-21.2.3 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + ELSE 'no result' + END +} {C} + +# EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions +# evaluate to true, the result of evaluating the ELSE expression, if +# any. +# +foreach {a b c} {0 0 0} break +do_execsql_test e_expr-21.3.1 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + ELSE 'no result' + END +} {{no result}} + +# EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of +# the WHEN expressions are true, then the overall result is NULL. +# +db nullvalue null +do_execsql_test e_expr-21.3.2 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + END +} {null} +db nullvalue {} + +# EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when +# evaluating WHEN terms. +# +do_execsql_test e_expr-21.4.1 { + SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END, iif(NULL,8,99); +} {B 99} +do_execsql_test e_expr-21.4.2 { + SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, iif(0,8,99); +} {C 99} + +# EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base +# expression is evaluated just once and the result is compared against +# the evaluation of each WHEN expression from left to right. +# +# Note: This test case tests the "evaluated just once" part of the above +# statement. Tests associated with the next two statements test that the +# comparisons take place. +# +foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break +set ::varlist [list] +do_execsql_test e_expr-22.1.1 { + SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END +} {C} +do_test e_expr-22.1.2 { set ::varlist } {a} + +# EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the +# evaluation of the THEN expression that corresponds to the first WHEN +# expression for which the comparison is true. +# +do_execsql_test e_expr-22.2.1 { + SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END +} {B} +do_execsql_test e_expr-22.2.2 { + SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END +} {A} + +# EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions +# evaluate to a value equal to the base expression, the result of +# evaluating the ELSE expression, if any. +# +do_execsql_test e_expr-22.3.1 { + SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END +} {D} + +# EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of +# the WHEN expressions produce a result equal to the base expression, +# the overall result is NULL. +# +do_execsql_test e_expr-22.4.1 { + SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END +} {{}} +db nullvalue null +do_execsql_test e_expr-22.4.2 { + SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END +} {null} +db nullvalue {} + +# EVIDENCE-OF: R-11479-62774 When comparing a base expression against a +# WHEN expression, the same collating sequence, affinity, and +# NULL-handling rules apply as if the base expression and WHEN +# expression are respectively the left- and right-hand operands of an = +# operator. +# +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] [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 +} {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 { + SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END +} {B} + +# EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the +# result of the CASE is always the result of evaluating the ELSE +# expression if it exists, or NULL if it does not. +# +do_execsql_test e_expr-24.1.1 { + SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; +} {{}} +do_execsql_test e_expr-24.1.2 { + SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; +} {C} + +# EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, +# or short-circuit, evaluation. +# +set varlist [list] +foreach {a b c} {0 1 0} break +do_execsql_test e_expr-25.1.1 { + SELECT CASE WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + END +} {B} +do_test e_expr-25.1.2 { set ::varlist } {a b} +set varlist [list] +do_execsql_test e_expr-25.1.3 { + SELECT CASE '0' WHEN var('a') THEN 'A' + WHEN var('b') THEN 'B' + WHEN var('c') THEN 'C' + END +} {A} +do_test e_expr-25.1.4 { set ::varlist } {a} + +# EVIDENCE-OF: R-34773-62253 The only difference between the following +# two CASE expressions is that the x expression is evaluated exactly +# once in the first example but might be evaluated multiple times in the +# second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN +# x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END +# +proc ceval {x} { + incr ::evalcount + return $x +} +db func ceval ceval +set ::evalcount 0 + +do_execsql_test e_expr-26.1.1 { + CREATE TABLE t2(x, w1, r1, w2, r2, r3); + INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); + INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); + INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); +} {} +do_execsql_test e_expr-26.1.2 { + SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 +} {R1 R2 R3} +do_execsql_test e_expr-26.1.3 { + SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 +} {R1 R2 R3} + +do_execsql_test e_expr-26.1.4 { + SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 +} {R1 R2 R3} +do_test e_expr-26.1.5 { set ::evalcount } {3} +set ::evalcount 0 +do_execsql_test e_expr-26.1.6 { + SELECT CASE + WHEN ceval(x)=w1 THEN r1 + 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-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), + typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER) +} {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. +# +do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' +do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' +do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' +rename db db2 +sqlite3 db :memory: +ifcapable {utf16} { +db eval { PRAGMA encoding = 'utf-16le' } +do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' +do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' +do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' +} +db close +sqlite3 db :memory: +db eval { PRAGMA encoding = 'utf-16be' } +ifcapable {utf16} { +do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' +do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' +do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' +} +db close +rename db2 db + +# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence +# of bytes that make up the BLOB is interpreted as text encoded using +# the database encoding. +# +do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi +do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g +rename db db2 +sqlite3 db :memory: +db eval { PRAGMA encoding = 'utf-16le' } +ifcapable {utf16} { +do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0 +do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi +} +db close +rename db2 db + +# EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT +# renders the value as if via sqlite3_snprintf() except that the +# resulting TEXT uses the encoding of the database connection. +# +do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 +do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 +do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 +do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 +do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 +do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 +do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 +do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 + +# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the +# value is first converted to TEXT. +# +do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 +do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0 +do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87 +do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001 +rename db db2 +sqlite3 db :memory: +ifcapable {utf16} { +db eval { PRAGMA encoding = 'utf-16le' } +do_expr_test e_expr-29.1.5 { + CAST (X'31002E0032003300' AS REAL) } real 1.23 +do_expr_test e_expr-29.1.6 { + CAST (X'3200330030002E003000' AS REAL) } real 230.0 +do_expr_test e_expr-29.1.7 { + CAST (X'2D0039002E0038003700' AS REAL) } real -9.87 +do_expr_test e_expr-29.1.8 { + CAST (X'30002E003000300030003100' AS REAL) } real 0.0001 +} +db close +rename db2 db + +# EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the +# longest possible prefix of the value that can be interpreted as a real +# number is extracted from the TEXT value and the remainder ignored. +# +do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23 +do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45 +do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212 +do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0 + +# EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are +# ignored when converging from TEXT to REAL. +# +do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23 +do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45 +do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212 +do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0 + +# EVIDENCE-OF: R-22662-28218 If there is no prefix that can be +# interpreted as a real number, the result of the conversion is 0.0. +# +do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0 +do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0 +do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0 + +# EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the +# value is first converted to TEXT. +# +do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123 +do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678 +do_expr_test e_expr-30.1.3 { + CAST(X'31303030303030' AS INTEGER) +} integer 1000000 +do_expr_test e_expr-30.1.4 { + CAST(X'2D31313235383939393036383432363234' AS INTEGER) +} integer -1125899906842624 + +rename db db2 +sqlite3 db :memory: +ifcapable {utf16} { +execsql { PRAGMA encoding = 'utf-16be' } +do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123 +do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678 +do_expr_test e_expr-30.1.7 { + CAST(X'0031003000300030003000300030' AS INTEGER) +} integer 1000000 +do_expr_test e_expr-30.1.8 { + CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) +} integer -1125899906842624 +} +db close +rename db2 db + +# EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the +# longest possible prefix of the value that can be interpreted as an +# integer number is extracted from the TEXT value and the remainder +# ignored. +# +do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123 +do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523 +do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2 +do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1 + +# EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when +# converting from TEXT to INTEGER are ignored. +# +do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123 +do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523 +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-08980-53124 The CAST operator understands decimal +# integers only — 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-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 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 9223372036854775807 + + +# EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC +# yields either an INTEGER or a REAL result. +# +# EVIDENCE-OF: R-48945-04866 If the input text looks like an integer +# (there is no decimal point nor exponent) and the value is small enough +# to fit in a 64-bit signed integer, then the result will be INTEGER. +# +# EVIDENCE-OF: R-47045-23194 Input text that looks like floating point +# (there is a decimal point and/or an exponent) and the text describes a +# value that can be losslessly converted back and forth between IEEE 754 +# 64-bit float and a 51-bit signed integer, then the result is INTEGER. +# +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 +do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 +do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 +do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000 +do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000 +do_test e_expr-32.1.8 { + set expr {CAST( '9.223372036e15' AS NUMERIC)} + db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; + list $type $value +} {real 9.22337e+15} +do_test e_expr-32.1.9 { + set expr {CAST('-9.223372036e15' AS NUMERIC)} + db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; + list $type $value +} {real -9.22337e+15} + +# EVIDENCE-OF: R-50300-26941 Any text input that describes a value +# outside the range of a 64-bit signed integer yields a REAL result. +# +do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \ + integer 9223372036854775807 +do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \ + real 9.22337203685478e+18 +do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \ + integer -9223372036854775808 +do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \ + real -9.22337203685478e+18 + +# EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC +# is a no-op, even if a real value could be losslessly converted to an +# integer. +# +do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0 +do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5 + +do_expr_test e_expr-32.2.3 { + CAST(-9223372036854775808 AS NUMERIC) +} integer -9223372036854775808 +do_expr_test e_expr-32.2.4 { + CAST(9223372036854775807 AS NUMERIC) +} integer 9223372036854775807 +do_expr_test e_expr-32.2.5 { + CAST('9223372036854775807 ' AS NUMERIC) +} integer 9223372036854775807 +do_expr_test e_expr-32.2.6 { + CAST(' 9223372036854775807 ' AS NUMERIC) +} integer 9223372036854775807 +do_expr_test e_expr-32.2.7 { + CAST(' ' AS NUMERIC) +} integer 0 +do_execsql_test e_expr-32.2.8 { + WITH t1(x) AS (VALUES + ('9000000000000000001'), + ('9000000000000000001x'), + ('9000000000000000001 '), + (' 9000000000000000001 '), + (' 9000000000000000001'), + (' 9000000000000000001.'), + ('9223372036854775807'), + ('9223372036854775807 '), + (' 9223372036854775807 '), + ('9223372036854775808'), + (' 9223372036854775808 '), + ('9223372036854775807.0'), + ('9223372036854775807e+0'), + ('-5.0'), + ('-5e+0')) + SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1; +} [list \ + integer 9000000000000000001 \ + integer 9000000000000000001 \ + integer 9000000000000000001 \ + integer 9000000000000000001 \ + integer 9000000000000000001 \ + real 9.0e+18 \ + integer 9223372036854775807 \ + integer 9223372036854775807 \ + integer 9223372036854775807 \ + real 9.22337203685478e+18 \ + real 9.22337203685478e+18 \ + real 9.22337203685478e+18 \ + real 9.22337203685478e+18 \ + integer -5 \ + integer -5 \ +] + +# EVIDENCE-OF: R-64550-29191 Note that the result from casting any +# non-BLOB value into a BLOB and the result from casting any BLOB value +# into a non-BLOB value may be different depending on whether the +# database encoding is UTF-8, UTF-16be, or UTF-16le. +# +ifcapable {utf16} { +sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' } +sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' } +sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' } +foreach {tn castexpr differs} { + 1 { CAST(123 AS BLOB) } 1 + 2 { CAST('' AS BLOB) } 0 + 3 { CAST('abcd' AS BLOB) } 1 + + 4 { CAST(X'abcd' AS TEXT) } 1 + 5 { CAST(X'' AS TEXT) } 0 +} { + set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"] + set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"] + set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"] + + if {$differs} { + set res [expr {$r1!=$r2 && $r2!=$r3}] + } else { + set res [expr {$r1==$r2 && $r2==$r3}] + } + + do_test e_expr-33.1.$tn {set res} 1 +} +db1 close +db2 close +db3 close +} + +#------------------------------------------------------------------------- +# Test statements related to the EXISTS and NOT EXISTS operators. +# +catch { db close } +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); +} {} + +# EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one +# of the integer values 0 and 1. +# +# This statement is not tested by itself. Instead, all e_expr-34.* tests +# following this point explicitly test that specific invocations of EXISTS +# return either integer 0 or integer 1. +# + +# EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified +# as the right-hand operand of the EXISTS operator would return one or +# more rows, then the EXISTS operator evaluates to 1. +# +foreach {tn expr} { + 1 { EXISTS ( SELECT a FROM t1 ) } + 2 { EXISTS ( SELECT b FROM t1 ) } + 3 { EXISTS ( SELECT 24 ) } + 4 { EXISTS ( SELECT NULL ) } + 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) } +} { + do_expr_test e_expr-34.2.$tn $expr integer 1 +} + +# EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no +# rows at all, then the EXISTS operator evaluates to 0. +# +foreach {tn expr} { + 1 { EXISTS ( SELECT a FROM t1 WHERE 0) } + 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) } + 3 { EXISTS ( SELECT 24 WHERE 0) } + 4 { EXISTS ( SELECT NULL WHERE 1=2) } +} { + do_expr_test e_expr-34.3.$tn $expr integer 0 +} + +# EVIDENCE-OF: R-35109-49139 The number of columns in each row returned +# by the SELECT statement (if any) and the specific values returned have +# no effect on the results of the EXISTS operator. +# +foreach {tn expr res} { + 1 { EXISTS ( SELECT * FROM t1 ) } 1 + 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1 + 3 { EXISTS ( SELECT 24, 25 ) } 1 + 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1 + 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1 + + 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0 + 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0 + 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0 + 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0 +} { + do_expr_test e_expr-34.4.$tn $expr integer $res +} + +# EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values +# are not handled any differently from rows without NULL values. +# +foreach {tn e1 e2} { + 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) } + 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 } +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); + } +} {} + +# 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-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 {/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-18318-14995 The value of a subquery expression is the +# first row of the result from the enclosed SELECT statement. +# +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-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. +# +do_execsql_test e_expr-37.1 { + SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END, iif(NULL,'true','false'); +} {false false} +do_execsql_test e_expr-37.2 { + SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END, iif(0.0,'true','false'); +} {false false} +do_execsql_test e_expr-37.3 { + SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END, iif(0,'true','false'); +} {false false} +do_execsql_test e_expr-37.4 { + SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END, iif('engligh','true','false'); +} {false false} +do_execsql_test e_expr-37.5 { + SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END, iif('0','true','false'); +} {false 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, iif(1,'true','false'); +} {true true} +do_execsql_test e_expr-37.7 { + SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END, iif(1.0,'true','false'); +} {true true} +do_execsql_test e_expr-37.8 { + SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END, iif(0.1,'true','false'); +} {true true} +do_execsql_test e_expr-37.9 { + SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END, iif(-0.1,'true','false'); +} {true true} +do_execsql_test e_expr-37.10 { + SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END, iif('1engl','true','false'); +} {true true} + + +finish_test |