# 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 # 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. # # 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 # # 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)< 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-08914-63790 The operator % outputs the value of its # left operand modulo its right operand. # 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} #------------------------------------------------------------------------- # Test that the results of all binary operators are either numeric or # NULL, except for the || operator, which may evaluate to either a text # value or NULL. # # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either # a numeric value or NULL, except for the || concatenation operator # which always evaluates 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. # # EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher # precedence (binds more tightly) than any prefix unary operator or any # binary operator. # 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} # EVIDENCE-OF: R-26921-59298 Scientific notation is supported for # floating 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-39344-59787 For 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. # # EVIDENCE-OF: R-04177-20688 -- 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-30740-26723 -- 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 # file delete -force 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} } } # EVIDENCE-OF: R-49462-56079 -- 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 } { 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 # 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 finish_test