1# 2010 July 16 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# This file implements tests to verify that the "testable statements" in 13# the lang_expr.html document are correct. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/malloc_common.tcl 19 20# Set up three global variables: 21# 22# ::opname An array mapping from SQL operator to an easy to parse 23# name. The names are used as part of test case names. 24# 25# ::opprec An array mapping from SQL operator to a numeric 26# precedence value. Operators that group more tightly 27# have lower numeric precedences. 28# 29# ::oplist A list of all SQL operators supported by SQLite. 30# 31foreach {op opn} { 32 || cat * mul / div % mod + add 33 - sub << lshift >> rshift & bitand | bitor 34 < less <= lesseq > more >= moreeq = eq1 35 == eq2 <> ne1 != ne2 IS is LIKE like 36 GLOB glob AND and OR or MATCH match REGEXP regexp 37 {IS NOT} isnt 38} { 39 set ::opname($op) $opn 40} 41set oplist [list] 42foreach {prec opl} { 43 1 || 44 2 {* / %} 45 3 {+ -} 46 4 {<< >> & |} 47 5 {< <= > >=} 48 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} 49 7 AND 50 8 OR 51} { 52 foreach op $opl { 53 set ::opprec($op) $prec 54 lappend oplist $op 55 } 56} 57 58 59# Hook in definitions of MATCH and REGEX. The following implementations 60# cause MATCH and REGEX to behave similarly to the == operator. 61# 62proc matchfunc {a b} { return [expr {$a==$b}] } 63proc regexfunc {a b} { return [expr {$a==$b}] } 64db func match -argcount 2 matchfunc 65db func regexp -argcount 2 regexfunc 66 67#------------------------------------------------------------------------- 68# Test cases e_expr-1.* attempt to verify that all binary operators listed 69# in the documentation exist and that the relative precedences of the 70# operators are also as the documentation suggests. 71# 72# EVIDENCE-OF: R-15514-65163 SQLite understands the following binary 73# operators, in order from highest to lowest precedence: || * / % + - 74# << >> & | < <= > >= = == != <> IS IS 75# NOT IN LIKE GLOB MATCH REGEXP AND OR 76# 77# EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same 78# precedence as =. 79# 80 81unset -nocomplain untested 82foreach op1 $oplist { 83 foreach op2 $oplist { 84 set untested($op1,$op2) 1 85 foreach {tn A B C} { 86 1 22 45 66 87 2 0 0 0 88 3 0 0 1 89 4 0 1 0 90 5 0 1 1 91 6 1 0 0 92 7 1 0 1 93 8 1 1 0 94 9 1 1 1 95 10 5 6 1 96 11 1 5 6 97 12 1 5 5 98 13 5 5 1 99 100 14 5 2 1 101 15 1 4 1 102 16 -1 0 1 103 17 0 1 -1 104 105 } { 106 set testname "e_expr-1.$opname($op1).$opname($op2).$tn" 107 108 # If $op2 groups more tightly than $op1, then the result 109 # of executing $sql1 whould be the same as executing $sql3. 110 # If $op1 groups more tightly, or if $op1 and $op2 have 111 # the same precedence, then executing $sql1 should return 112 # the same value as $sql2. 113 # 114 set sql1 "SELECT $A $op1 $B $op2 $C" 115 set sql2 "SELECT ($A $op1 $B) $op2 $C" 116 set sql3 "SELECT $A $op1 ($B $op2 $C)" 117 118 set a2 [db one $sql2] 119 set a3 [db one $sql3] 120 121 do_execsql_test $testname $sql1 [list [ 122 if {$opprec($op2) < $opprec($op1)} {set a3} {set a2} 123 ]] 124 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) } 125 } 126 } 127} 128 129foreach op {* AND OR + || & |} { unset untested($op,$op) } 130unset untested(+,-) ;# Since (a+b)-c == a+(b-c) 131unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c) 132 133do_test e_expr-1.1 { array names untested } {} 134 135# At one point, test 1.2.2 was failing. Instead of the correct result, it 136# was returning {1 1 0}. This would seem to indicate that LIKE has the 137# same precedence as '<'. Which is incorrect. It has lower precedence. 138# 139do_execsql_test e_expr-1.2.1 { 140 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1) 141} {1 1 0} 142do_execsql_test e_expr-1.2.2 { 143 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2) 144} {0 1 0} 145 146# Showing that LIKE and == have the same precedence 147# 148do_execsql_test e_expr-1.2.3 { 149 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1) 150} {1 1 0} 151do_execsql_test e_expr-1.2.4 { 152 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1) 153} {1 1 0} 154 155# Showing that < groups more tightly than == (< has higher precedence). 156# 157do_execsql_test e_expr-1.2.5 { 158 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1) 159} {1 1 0} 160do_execsql_test e_expr-1.6 { 161 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2) 162} {0 1 0} 163 164#------------------------------------------------------------------------- 165# Check that the four unary prefix operators mentioned in the 166# documentation exist. 167# 168# EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these: 169# - + ~ NOT 170# 171do_execsql_test e_expr-2.1 { SELECT - 10 } {-10} 172do_execsql_test e_expr-2.2 { SELECT + 10 } {10} 173do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11} 174do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0} 175 176#------------------------------------------------------------------------- 177# Tests for the two statements made regarding the unary + operator. 178# 179# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op. 180# 181# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers, 182# blobs or NULL and it always returns a result with the same value as 183# the operand. 184# 185foreach {tn literal type} { 186 1 'helloworld' text 187 2 45 integer 188 3 45.2 real 189 4 45.0 real 190 5 X'ABCDEF' blob 191 6 NULL null 192} { 193 set sql " SELECT quote( + $literal ), typeof( + $literal) " 194 do_execsql_test e_expr-3.$tn $sql [list $literal $type] 195} 196 197#------------------------------------------------------------------------- 198# Check that both = and == are both acceptable as the "equals" operator. 199# Similarly, either != or <> work as the not-equals operator. 200# 201# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==. 202# 203# EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or 204# <>. 205# 206foreach {tn literal different} { 207 1 'helloworld' '12345' 208 2 22 23 209 3 'xyz' X'78797A' 210 4 X'78797A00' 'xyz' 211} { 212 do_execsql_test e_expr-4.$tn " 213 SELECT $literal = $literal, $literal == $literal, 214 $literal = $different, $literal == $different, 215 $literal = NULL, $literal == NULL, 216 $literal != $literal, $literal <> $literal, 217 $literal != $different, $literal <> $different, 218 $literal != NULL, $literal != NULL 219 220 " {1 1 0 0 {} {} 0 0 1 1 {} {}} 221} 222 223#------------------------------------------------------------------------- 224# Test the || operator. 225# 226# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins 227# together the two strings of its operands. 228# 229foreach {tn a b} { 230 1 'helloworld' '12345' 231 2 22 23 232} { 233 set as [db one "SELECT $a"] 234 set bs [db one "SELECT $b"] 235 236 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"] 237} 238 239#------------------------------------------------------------------------- 240# Test the % operator. 241# 242# EVIDENCE-OF: R-08914-63790 The operator % outputs the value of its 243# left operand modulo its right operand. 244# 245do_execsql_test e_expr-6.1 {SELECT 72%5} {2} 246do_execsql_test e_expr-6.2 {SELECT 72%-5} {2} 247do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2} 248do_execsql_test e_expr-6.4 {SELECT -72%5} {-2} 249 250#------------------------------------------------------------------------- 251# Test that the results of all binary operators are either numeric or 252# NULL, except for the || operator, which may evaluate to either a text 253# value or NULL. 254# 255# EVIDENCE-OF: R-20665-17792 The result of any binary operator is either 256# a numeric value or NULL, except for the || concatenation operator 257# which always evaluates to either NULL or a text value. 258# 259set literals { 260 1 'abc' 2 'hexadecimal' 3 '' 261 4 123 5 -123 6 0 262 7 123.4 8 0.0 9 -123.4 263 10 X'ABCDEF' 11 X'' 12 X'0000' 264 13 NULL 265} 266foreach op $oplist { 267 foreach {n1 rhs} $literals { 268 foreach {n2 lhs} $literals { 269 270 set t [db one " SELECT typeof($lhs $op $rhs) "] 271 do_test e_expr-7.$opname($op).$n1.$n2 { 272 expr { 273 ($op=="||" && ($t == "text" || $t == "null")) 274 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null")) 275 } 276 } 1 277 278 }} 279} 280 281#------------------------------------------------------------------------- 282# Test the IS and IS NOT operators. 283# 284# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and 285# != except when one or both of the operands are NULL. 286# 287# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL, 288# then the IS operator evaluates to 1 (true) and the IS NOT operator 289# evaluates to 0 (false). 290# 291# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is 292# not, then the IS operator evaluates to 0 (false) and the IS NOT 293# operator is 1 (true). 294# 295# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT 296# expression to evaluate to NULL. 297# 298do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1} 299do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0} 300do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0} 301do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1} 302do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}} 303do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}} 304do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}} 305do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1} 306do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0} 307do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1} 308do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1} 309do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0} 310do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}} 311do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}} 312do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}} 313do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0} 314 315foreach {n1 rhs} $literals { 316 foreach {n2 lhs} $literals { 317 if {$rhs!="NULL" && $lhs!="NULL"} { 318 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"] 319 } else { 320 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \ 321 [expr {$lhs!="NULL" || $rhs!="NULL"}] 322 ] 323 } 324 set test e_expr-8.2.$n1.$n2 325 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq 326 do_execsql_test $test.2 " 327 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL 328 " {0 0} 329 } 330} 331 332finish_test 333