| # 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)<<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. |
| # |
| # 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-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 |
