ADDED test/e_expr.test Index: test/e_expr.test ================================================================== --- test/e_expr.test +++ test/e_expr.test @@ -0,0 +1,342 @@ +# 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 =. +# + + +# TODO: These tests are currently omitted because one or two cases +# related to LIKE/GLOB/MATCH/REGEXP fail. After this case is fixed, +# reinstate these tests. +# +if 0 { + +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 [ + expr {$opprec($op2) < $opprec($op1) ? $a3 : $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