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