# 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}] } finish_test