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 20ifcapable !compound { 21 finish_test 22 return 23} 24 25proc do_expr_test {tn expr type value} { 26 uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [ 27 list [list $type $value] 28 ] 29} 30 31proc do_qexpr_test {tn expr value} { 32 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value] 33} 34 35# Set up three global variables: 36# 37# ::opname An array mapping from SQL operator to an easy to parse 38# name. The names are used as part of test case names. 39# 40# ::opprec An array mapping from SQL operator to a numeric 41# precedence value. Operators that group more tightly 42# have lower numeric precedences. 43# 44# ::oplist A list of all SQL operators supported by SQLite. 45# 46foreach {op opn} { 47 || cat * mul / div % mod + add 48 - sub << lshift >> rshift & bitand | bitor 49 < less <= lesseq > more >= moreeq = eq1 50 == eq2 <> ne1 != ne2 IS is LIKE like 51 GLOB glob AND and OR or MATCH match REGEXP regexp 52 {IS NOT} isnt 53} { 54 set ::opname($op) $opn 55} 56set oplist [list] 57foreach {prec opl} { 58 1 || 59 2 {* / %} 60 3 {+ -} 61 4 {<< >> & |} 62 5 {< <= > >=} 63 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} 64 7 AND 65 8 OR 66} { 67 foreach op $opl { 68 set ::opprec($op) $prec 69 lappend oplist $op 70 } 71} 72 73 74# Hook in definitions of MATCH and REGEX. The following implementations 75# cause MATCH and REGEX to behave similarly to the == operator. 76# 77proc matchfunc {a b} { return [expr {$a==$b}] } 78proc regexfunc {a b} { return [expr {$a==$b}] } 79db func match -argcount 2 matchfunc 80db func regexp -argcount 2 regexfunc 81 82#------------------------------------------------------------------------- 83# Test cases e_expr-1.* attempt to verify that all binary operators listed 84# in the documentation exist and that the relative precedences of the 85# operators are also as the documentation suggests. 86# 87# EVIDENCE-OF: R-15514-65163 SQLite understands the following binary 88# operators, in order from highest to lowest precedence: || * / % + - 89# << >> & | < <= > >= = == != <> IS IS 90# NOT IN LIKE GLOB MATCH REGEXP AND OR 91# 92# EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same 93# precedence as =. 94# 95 96unset -nocomplain untested 97foreach op1 $oplist { 98 foreach op2 $oplist { 99 set untested($op1,$op2) 1 100 foreach {tn A B C} { 101 1 22 45 66 102 2 0 0 0 103 3 0 0 1 104 4 0 1 0 105 5 0 1 1 106 6 1 0 0 107 7 1 0 1 108 8 1 1 0 109 9 1 1 1 110 10 5 6 1 111 11 1 5 6 112 12 1 5 5 113 13 5 5 1 114 115 14 5 2 1 116 15 1 4 1 117 16 -1 0 1 118 17 0 1 -1 119 120 } { 121 set testname "e_expr-1.$opname($op1).$opname($op2).$tn" 122 123 # If $op2 groups more tightly than $op1, then the result 124 # of executing $sql1 whould be the same as executing $sql3. 125 # If $op1 groups more tightly, or if $op1 and $op2 have 126 # the same precedence, then executing $sql1 should return 127 # the same value as $sql2. 128 # 129 set sql1 "SELECT $A $op1 $B $op2 $C" 130 set sql2 "SELECT ($A $op1 $B) $op2 $C" 131 set sql3 "SELECT $A $op1 ($B $op2 $C)" 132 133 set a2 [db one $sql2] 134 set a3 [db one $sql3] 135 136 do_execsql_test $testname $sql1 [list [ 137 if {$opprec($op2) < $opprec($op1)} {set a3} {set a2} 138 ]] 139 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) } 140 } 141 } 142} 143 144foreach op {* AND OR + || & |} { unset untested($op,$op) } 145unset untested(+,-) ;# Since (a+b)-c == a+(b-c) 146unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c) 147 148do_test e_expr-1.1 { array names untested } {} 149 150# At one point, test 1.2.2 was failing. Instead of the correct result, it 151# was returning {1 1 0}. This would seem to indicate that LIKE has the 152# same precedence as '<'. Which is incorrect. It has lower precedence. 153# 154do_execsql_test e_expr-1.2.1 { 155 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1) 156} {1 1 0} 157do_execsql_test e_expr-1.2.2 { 158 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2) 159} {0 1 0} 160 161# Showing that LIKE and == have the same precedence 162# 163do_execsql_test e_expr-1.2.3 { 164 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1) 165} {1 1 0} 166do_execsql_test e_expr-1.2.4 { 167 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1) 168} {1 1 0} 169 170# Showing that < groups more tightly than == (< has higher precedence). 171# 172do_execsql_test e_expr-1.2.5 { 173 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1) 174} {1 1 0} 175do_execsql_test e_expr-1.6 { 176 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2) 177} {0 1 0} 178 179#------------------------------------------------------------------------- 180# Check that the four unary prefix operators mentioned in the 181# documentation exist. 182# 183# EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these: 184# - + ~ NOT 185# 186do_execsql_test e_expr-2.1 { SELECT - 10 } {-10} 187do_execsql_test e_expr-2.2 { SELECT + 10 } {10} 188do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11} 189do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0} 190 191#------------------------------------------------------------------------- 192# Tests for the two statements made regarding the unary + operator. 193# 194# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op. 195# 196# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers, 197# blobs or NULL and it always returns a result with the same value as 198# the operand. 199# 200foreach {tn literal type} { 201 1 'helloworld' text 202 2 45 integer 203 3 45.2 real 204 4 45.0 real 205 5 X'ABCDEF' blob 206 6 NULL null 207} { 208 set sql " SELECT quote( + $literal ), typeof( + $literal) " 209 do_execsql_test e_expr-3.$tn $sql [list $literal $type] 210} 211 212#------------------------------------------------------------------------- 213# Check that both = and == are both acceptable as the "equals" operator. 214# Similarly, either != or <> work as the not-equals operator. 215# 216# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==. 217# 218# EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or 219# <>. 220# 221foreach {tn literal different} { 222 1 'helloworld' '12345' 223 2 22 23 224 3 'xyz' X'78797A' 225 4 X'78797A00' 'xyz' 226} { 227 do_execsql_test e_expr-4.$tn " 228 SELECT $literal = $literal, $literal == $literal, 229 $literal = $different, $literal == $different, 230 $literal = NULL, $literal == NULL, 231 $literal != $literal, $literal <> $literal, 232 $literal != $different, $literal <> $different, 233 $literal != NULL, $literal != NULL 234 235 " {1 1 0 0 {} {} 0 0 1 1 {} {}} 236} 237 238#------------------------------------------------------------------------- 239# Test the || operator. 240# 241# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins 242# together the two strings of its operands. 243# 244foreach {tn a b} { 245 1 'helloworld' '12345' 246 2 22 23 247} { 248 set as [db one "SELECT $a"] 249 set bs [db one "SELECT $b"] 250 251 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"] 252} 253 254#------------------------------------------------------------------------- 255# Test the % operator. 256# 257# EVIDENCE-OF: R-04223-04352 The operator % outputs the integer value of 258# its left operand modulo its right operand. 259# 260do_execsql_test e_expr-6.1 {SELECT 72%5} {2} 261do_execsql_test e_expr-6.2 {SELECT 72%-5} {2} 262do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2} 263do_execsql_test e_expr-6.4 {SELECT -72%5} {-2} 264do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0} 265 266#------------------------------------------------------------------------- 267# Test that the results of all binary operators are either numeric or 268# NULL, except for the || operator, which may evaluate to either a text 269# value or NULL. 270# 271# EVIDENCE-OF: R-20665-17792 The result of any binary operator is either 272# a numeric value or NULL, except for the || concatenation operator 273# which always evaluates to either NULL or a text value. 274# 275set literals { 276 1 'abc' 2 'hexadecimal' 3 '' 277 4 123 5 -123 6 0 278 7 123.4 8 0.0 9 -123.4 279 10 X'ABCDEF' 11 X'' 12 X'0000' 280 13 NULL 281} 282foreach op $oplist { 283 foreach {n1 rhs} $literals { 284 foreach {n2 lhs} $literals { 285 286 set t [db one " SELECT typeof($lhs $op $rhs) "] 287 do_test e_expr-7.$opname($op).$n1.$n2 { 288 expr { 289 ($op=="||" && ($t == "text" || $t == "null")) 290 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null")) 291 } 292 } 1 293 294 }} 295} 296 297#------------------------------------------------------------------------- 298# Test the IS and IS NOT operators. 299# 300# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and 301# != except when one or both of the operands are NULL. 302# 303# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL, 304# then the IS operator evaluates to 1 (true) and the IS NOT operator 305# evaluates to 0 (false). 306# 307# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is 308# not, then the IS operator evaluates to 0 (false) and the IS NOT 309# operator is 1 (true). 310# 311# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT 312# expression to evaluate to NULL. 313# 314do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1} 315do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0} 316do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0} 317do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1} 318do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}} 319do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}} 320do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}} 321do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1} 322do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0} 323do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1} 324do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1} 325do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0} 326do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}} 327do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}} 328do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}} 329do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0} 330 331foreach {n1 rhs} $literals { 332 foreach {n2 lhs} $literals { 333 if {$rhs!="NULL" && $lhs!="NULL"} { 334 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"] 335 } else { 336 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \ 337 [expr {$lhs!="NULL" || $rhs!="NULL"}] 338 ] 339 } 340 set test e_expr-8.2.$n1.$n2 341 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq 342 do_execsql_test $test.2 " 343 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL 344 " {0 0} 345 } 346} 347 348#------------------------------------------------------------------------- 349# Run some tests on the COLLATE "unary postfix operator". 350# 351# This collation sequence reverses both arguments before using 352# [string compare] to compare them. For example, when comparing the 353# strings 'one' and 'four', return the result of: 354# 355# string compare eno ruof 356# 357proc reverse_str {zStr} { 358 set out "" 359 foreach c [split $zStr {}] { set out "${c}${out}" } 360 set out 361} 362proc reverse_collate {zLeft zRight} { 363 string compare [reverse_str $zLeft] [reverse_str $zRight] 364} 365db collate reverse reverse_collate 366 367# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix 368# operator that assigns a collating sequence to an expression. 369# 370# EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher 371# precedence (binds more tightly) than any binary operator and any unary 372# prefix operator except "~". 373# 374do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0 375do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1 376do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0 377do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1 378 379do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1 380do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0 381do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1 382do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0 383 384do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1 385do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0 386do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1 387do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0 388do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1 389do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0 390 391do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0 392do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1 393do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0 394do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1 395do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0 396do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1 397 398do_execsql_test e_expr-9.22 { 399 SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase 400} 1 401do_execsql_test e_expr-9.23 { 402 SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase 403} 0 404 405# EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE 406# operator overrides the collating sequence determined by the COLLATE 407# clause in a table column definition. 408# 409do_execsql_test e_expr-9.24 { 410 CREATE TABLE t24(a COLLATE NOCASE, b); 411 INSERT INTO t24 VALUES('aaa', 1); 412 INSERT INTO t24 VALUES('bbb', 2); 413 INSERT INTO t24 VALUES('ccc', 3); 414} {} 415do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0} 416do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0} 417do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0} 418do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0} 419 420#------------------------------------------------------------------------- 421# Test statements related to literal values. 422# 423# EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating 424# point numbers, strings, BLOBs, or NULLs. 425# 426do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer} 427do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real} 428do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text} 429do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob} 430do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null} 431 432# "Scientific notation is supported for point literal values." 433# 434do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real} 435do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real} 436do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034} 437do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0} 438 439# EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing 440# the string in single quotes ('). 441# 442# EVIDENCE-OF: R-07100-06606 A single quote within the string can be 443# encoded by putting two single quotes in a row - as in Pascal. 444# 445do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}} 446do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text} 447do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't} 448do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text} 449 450# EVIDENCE-OF: R-09593-03321 BLOB literals are string literals 451# containing hexadecimal data and preceded by a single "x" or "X" 452# character. 453# 454# EVIDENCE-OF: R-19836-11244 Example: X'53514C697465' 455# 456do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob 457do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob 458do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob 459do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob 460do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob 461 462# EVIDENCE-OF: R-23914-51476 A literal value can also be the token 463# "NULL". 464# 465do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}} 466do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null} 467 468#------------------------------------------------------------------------- 469# Test statements related to bound parameters 470# 471 472proc parameter_test {tn sql params result} { 473 set stmt [sqlite3_prepare_v2 db $sql -1] 474 475 foreach {number name} $params { 476 set nm [sqlite3_bind_parameter_name $stmt $number] 477 do_test $tn.name.$number [list set {} $nm] $name 478 sqlite3_bind_int $stmt $number [expr -1 * $number] 479 } 480 481 sqlite3_step $stmt 482 483 set res [list] 484 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} { 485 lappend res [sqlite3_column_text $stmt $i] 486 } 487 488 set rc [sqlite3_finalize $stmt] 489 do_test $tn.rc [list set {} $rc] SQLITE_OK 490 do_test $tn.res [list set {} $res] $result 491} 492 493# EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN 494# holds a spot for the NNN-th parameter. NNN must be between 1 and 495# SQLITE_MAX_VARIABLE_NUMBER. 496# 497set mvn $SQLITE_MAX_VARIABLE_NUMBER 498parameter_test e_expr-11.1 " 499 SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4 500" "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4" 501 502set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER" 503foreach {tn param_number} [list \ 504 2 0 \ 505 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \ 506 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \ 507 5 12345678903456789034567890234567890 \ 508 6 2147483648 \ 509 7 2147483649 \ 510 8 4294967296 \ 511 9 4294967297 \ 512 10 9223372036854775808 \ 513 11 9223372036854775809 \ 514 12 18446744073709551616 \ 515 13 18446744073709551617 \ 516] { 517 do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg] 518} 519 520# EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a 521# number creates a parameter with a number one greater than the largest 522# parameter number already assigned. 523# 524# EVIDENCE-OF: R-42938-07030 If this means the parameter number is 525# greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error. 526# 527parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1 528parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2} 529parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6} 530parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5} 531parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" { 532 1 {} 456 ?456 457 {} 533} {-1 -456 -457} 534parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" { 535 1 {} 456 ?456 4 ?4 457 {} 536} {-1 -456 -4 -457} 537foreach {tn sql} [list \ 538 1 "SELECT ?$mvn, ?" \ 539 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \ 540 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \ 541] { 542 do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}] 543} 544 545# EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name 546# holds a spot for a named parameter with the name :AAAA. 547# 548# Identifiers in SQLite consist of alphanumeric, '_' and '$' characters, 549# and any UTF characters with codepoints larger than 127 (non-ASCII 550# characters). 551# 552parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1 553parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1 554parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1 555parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1 556parameter_test e_expr-11.2.5 " 557 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 558" "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 559parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1 560 561# EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon, 562# except that the name of the parameter created is @AAAA. 563# 564parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1 565parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1 566parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1 567parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1 568parameter_test e_expr-11.3.5 " 569 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 570" "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 571parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1 572 573# EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier 574# name also holds a spot for a named parameter with the name $AAAA. 575# 576# EVIDENCE-OF: R-55025-21042 The identifier name in this case can 577# include one or more occurrences of "::" and a suffix enclosed in 578# "(...)" containing any text at all. 579# 580# Note: Looks like an identifier cannot consist entirely of "::" 581# characters or just a suffix. Also, the other named variable characters 582# (: and @) work the same way internally. Why not just document it that way? 583# 584parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1 585parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1 586parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1 587parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1 588parameter_test e_expr-11.4.5 " 589 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 590" "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 591parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1 592 593parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1 594parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1 595parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1 596 597# EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The 598# number assigned is one greater than the largest parameter number 599# already assigned. 600# 601# EVIDENCE-OF: R-42620-22184 If this means the parameter would be 602# assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an 603# error. 604# 605parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2} 606parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124} 607parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} { 608 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c 609} {-1 -8 -9 -10 -2 -11} 610foreach {tn sql} [list \ 611 1 "SELECT ?$mvn, \$::a" \ 612 2 "SELECT ?$mvn, ?4, @a1" \ 613 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \ 614] { 615 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}] 616} 617 618# EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values 619# using sqlite3_bind() are treated as NULL. 620# 621do_test e_expr-11.7.1 { 622 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1] 623 sqlite3_step $stmt 624 625 list [sqlite3_column_type $stmt 0] \ 626 [sqlite3_column_type $stmt 1] \ 627 [sqlite3_column_type $stmt 2] \ 628 [sqlite3_column_type $stmt 3] 629} {NULL NULL NULL NULL} 630do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK 631 632#------------------------------------------------------------------------- 633# "Test" the syntax diagrams in lang_expr.html. 634# 635# -- syntax diagram signed-number 636# 637do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0} 638do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1} 639do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2} 640do_execsql_test e_expr-12.1.4 { 641 SELECT 1.4, +1.4, -1.4 642} {1.4 1.4 -1.4} 643do_execsql_test e_expr-12.1.5 { 644 SELECT 1.5e+5, +1.5e+5, -1.5e+5 645} {150000.0 150000.0 -150000.0} 646do_execsql_test e_expr-12.1.6 { 647 SELECT 0.0001, +0.0001, -0.0001 648} {0.0001 0.0001 -0.0001} 649 650# -- syntax diagram literal-value 651# 652set sqlite_current_time 1 653do_execsql_test e_expr-12.2.1 {SELECT 123} {123} 654do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0} 655do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde} 656do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC} 657do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}} 658do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01} 659do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01} 660do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}} 661set sqlite_current_time 0 662 663# -- syntax diagram expr 664# 665forcedelete test.db2 666execsql { 667 ATTACH 'test.db2' AS dbname; 668 CREATE TABLE dbname.tblname(cname); 669} 670 671proc glob {args} {return 1} 672db function glob glob 673db function match glob 674db function regexp glob 675 676foreach {tn expr} { 677 1 123 678 2 123.4e05 679 3 'abcde' 680 4 X'414243' 681 5 NULL 682 6 CURRENT_TIME 683 7 CURRENT_DATE 684 8 CURRENT_TIMESTAMP 685 686 9 ? 687 10 ?123 688 11 @hello 689 12 :world 690 13 $tcl 691 14 $tcl(array) 692 693 15 cname 694 16 tblname.cname 695 17 dbname.tblname.cname 696 697 18 "+ EXPR" 698 19 "- EXPR" 699 20 "NOT EXPR" 700 21 "~ EXPR" 701 702 22 "EXPR1 || EXPR2" 703 23 "EXPR1 * EXPR2" 704 24 "EXPR1 / EXPR2" 705 25 "EXPR1 % EXPR2" 706 26 "EXPR1 + EXPR2" 707 27 "EXPR1 - EXPR2" 708 28 "EXPR1 << EXPR2" 709 29 "EXPR1 >> EXPR2" 710 30 "EXPR1 & EXPR2" 711 31 "EXPR1 | EXPR2" 712 32 "EXPR1 < EXPR2" 713 33 "EXPR1 <= EXPR2" 714 34 "EXPR1 > EXPR2" 715 35 "EXPR1 >= EXPR2" 716 36 "EXPR1 = EXPR2" 717 37 "EXPR1 == EXPR2" 718 38 "EXPR1 != EXPR2" 719 39 "EXPR1 <> EXPR2" 720 40 "EXPR1 IS EXPR2" 721 41 "EXPR1 IS NOT EXPR2" 722 42 "EXPR1 AND EXPR2" 723 43 "EXPR1 OR EXPR2" 724 725 44 "count(*)" 726 45 "count(DISTINCT EXPR)" 727 46 "substr(EXPR, 10, 20)" 728 47 "changes()" 729 730 48 "( EXPR )" 731 732 49 "CAST ( EXPR AS integer )" 733 50 "CAST ( EXPR AS 'abcd' )" 734 51 "CAST ( EXPR AS 'ab$ $cd' )" 735 736 52 "EXPR COLLATE nocase" 737 53 "EXPR COLLATE binary" 738 739 54 "EXPR1 LIKE EXPR2" 740 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR" 741 56 "EXPR1 GLOB EXPR2" 742 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR" 743 58 "EXPR1 REGEXP EXPR2" 744 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR" 745 60 "EXPR1 MATCH EXPR2" 746 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR" 747 62 "EXPR1 NOT LIKE EXPR2" 748 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR" 749 64 "EXPR1 NOT GLOB EXPR2" 750 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR" 751 66 "EXPR1 NOT REGEXP EXPR2" 752 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR" 753 68 "EXPR1 NOT MATCH EXPR2" 754 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR" 755 756 70 "EXPR ISNULL" 757 71 "EXPR NOTNULL" 758 72 "EXPR NOT NULL" 759 760 73 "EXPR1 IS EXPR2" 761 74 "EXPR1 IS NOT EXPR2" 762 763 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2" 764 76 "EXPR BETWEEN EXPR1 AND EXPR2" 765 766 77 "EXPR NOT IN (SELECT cname FROM tblname)" 767 78 "EXPR NOT IN (1)" 768 79 "EXPR NOT IN (1, 2, 3)" 769 80 "EXPR NOT IN tblname" 770 81 "EXPR NOT IN dbname.tblname" 771 82 "EXPR IN (SELECT cname FROM tblname)" 772 83 "EXPR IN (1)" 773 84 "EXPR IN (1, 2, 3)" 774 85 "EXPR IN tblname" 775 86 "EXPR IN dbname.tblname" 776 777 87 "EXISTS (SELECT cname FROM tblname)" 778 88 "NOT EXISTS (SELECT cname FROM tblname)" 779 780 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END" 781 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END" 782 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" 783 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" 784 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END" 785 94 "CASE WHEN EXPR1 THEN EXPR2 END" 786 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" 787 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" 788} { 789 790 # If the expression string being parsed contains "EXPR2", then replace 791 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it 792 # contains "EXPR", then replace EXPR with an arbitrary SQL expression. 793 # 794 set elist [list $expr] 795 if {[string match *EXPR2* $expr]} { 796 set elist [list] 797 foreach {e1 e2} { cname "34+22" } { 798 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr] 799 } 800 } 801 if {[string match *EXPR* $expr]} { 802 set elist2 [list] 803 foreach el $elist { 804 foreach e { cname "34+22" } { 805 lappend elist2 [string map [list EXPR $e] $el] 806 } 807 } 808 set elist $elist2 809 } 810 811 set x 0 812 foreach e $elist { 813 incr x 814 do_test e_expr-12.3.$tn.$x { 815 set rc [catch { execsql "SELECT $e FROM tblname" } msg] 816 } {0} 817 } 818} 819 820# -- syntax diagram raise-function 821# 822foreach {tn raiseexpr} { 823 1 "RAISE(IGNORE)" 824 2 "RAISE(ROLLBACK, 'error message')" 825 3 "RAISE(ABORT, 'error message')" 826 4 "RAISE(FAIL, 'error message')" 827} { 828 do_execsql_test e_expr-12.4.$tn " 829 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN 830 SELECT $raiseexpr ; 831 END; 832 " {} 833} 834 835#------------------------------------------------------------------------- 836# Test the statements related to the BETWEEN operator. 837# 838# EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically 839# equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent 840# to "x>=y AND x<=z" except that with BETWEEN, the x expression is 841# only evaluated once. 842# 843db func x x 844proc x {} { incr ::xcount ; return [expr $::x] } 845foreach {tn x expr res nEval} { 846 1 10 "x() >= 5 AND x() <= 15" 1 2 847 2 10 "x() BETWEEN 5 AND 15" 1 1 848 849 3 5 "x() >= 5 AND x() <= 5" 1 2 850 4 5 "x() BETWEEN 5 AND 5" 1 1 851 852 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2 853 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1 854} { 855 do_test e_expr-13.1.$tn { 856 set ::xcount 0 857 set a [execsql "SELECT $expr"] 858 list $::xcount $a 859 } [list $nEval $res] 860} 861 862# EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is 863# the same as the precedence as operators == and != and LIKE and groups 864# left to right. 865# 866# Therefore, BETWEEN groups more tightly than operator "AND", but less 867# so than "<". 868# 869do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1 870do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1 871do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0 872do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1 873do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1 874do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0 875 876do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1 877do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1 878do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0 879do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1 880do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1 881do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0 882 883do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1 884do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1 885do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0 886do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1 887do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1 888do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0 889 890do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0 891do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0 892do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1 893do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0 894do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0 895do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1 896 897do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1 898do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1 899do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0 900do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0 901do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0 902do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1 903 904#------------------------------------------------------------------------- 905# Test the statements related to the LIKE and GLOB operators. 906# 907# EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching 908# comparison. 909# 910# EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE 911# operator contains the pattern and the left hand operand contains the 912# string to match against the pattern. 913# 914do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0 915do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1 916 917# EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern 918# matches any sequence of zero or more characters in the string. 919# 920do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1 921do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1 922do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1 923 924# EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern 925# matches any single character in the string. 926# 927do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0 928do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1 929do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0 930 931# EVIDENCE-OF: R-59007-20454 Any other character matches itself or its 932# lower/upper case equivalent (i.e. case-insensitive matching). 933# 934do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1 935do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1 936do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0 937 938# EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case 939# for ASCII characters by default. 940# 941# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by 942# default for unicode characters that are beyond the ASCII range. 943# 944# EVIDENCE-OF: R-44381-11669 the expression 945# 'a' LIKE 'A' is TRUE but 946# 'æ' LIKE 'Æ' is FALSE. 947# 948# The restriction to ASCII characters does not apply if the ICU 949# library is compiled in. When ICU is enabled SQLite does not act 950# as it does "by default". 951# 952do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1 953ifcapable !icu { 954 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0 955} 956 957# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present, 958# then the expression following the ESCAPE keyword must evaluate to a 959# string consisting of a single character. 960# 961do_catchsql_test e_expr-14.6.1 { 962 SELECT 'A' LIKE 'a' ESCAPE '12' 963} {1 {ESCAPE expression must be a single character}} 964do_catchsql_test e_expr-14.6.2 { 965 SELECT 'A' LIKE 'a' ESCAPE '' 966} {1 {ESCAPE expression must be a single character}} 967do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1} 968do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1} 969 970# EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE 971# pattern to include literal percent or underscore characters. 972# 973# EVIDENCE-OF: R-13345-31830 The escape character followed by a percent 974# symbol (%), underscore (_), or a second instance of the escape 975# character itself matches a literal percent symbol, underscore, or a 976# single escape character, respectively. 977# 978do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1 979do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0 980do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0 981do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0 982do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0 983 984do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1 985do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0 986do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0 987do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0 988do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0 989 990do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1 991do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0 992do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0 993do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0 994 995# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by 996# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z). 997# 998proc likefunc {args} { 999 eval lappend ::likeargs $args 1000 return 1 1001} 1002db func like -argcount 2 likefunc 1003db func like -argcount 3 likefunc 1004set ::likeargs [list] 1005do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1 1006do_test e_expr-15.1.2 { set likeargs } {def abc} 1007set ::likeargs [list] 1008do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1 1009do_test e_expr-15.1.4 { set likeargs } {def abc X} 1010db close 1011sqlite3 db test.db 1012 1013# EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case 1014# sensitive using the case_sensitive_like pragma. 1015# 1016do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1 1017do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {} 1018do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0 1019do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 1020do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {} 1021do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1 1022do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 1023 1024# EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but 1025# uses the Unix file globbing syntax for its wildcards. 1026# 1027# EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE. 1028# 1029do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0 1030do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1 1031do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0 1032do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1 1033 1034do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1 1035do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0 1036do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0 1037 1038# EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the 1039# NOT keyword to invert the sense of the test. 1040# 1041do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1 1042do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0 1043do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0 1044do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0 1045do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1 1046 1047db nullvalue null 1048do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null 1049do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null 1050do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null 1051do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null 1052db nullvalue {} 1053 1054# EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by 1055# calling the function glob(Y,X) and can be modified by overriding that 1056# function. 1057proc globfunc {args} { 1058 eval lappend ::globargs $args 1059 return 1 1060} 1061db func glob -argcount 2 globfunc 1062set ::globargs [list] 1063do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1 1064do_test e_expr-17.3.2 { set globargs } {def abc} 1065set ::globargs [list] 1066do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0 1067do_test e_expr-17.3.4 { set globargs } {Y X} 1068sqlite3 db test.db 1069 1070# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by 1071# default and so use of the REGEXP operator will normally result in an 1072# error message. 1073# 1074# There is a regexp function if ICU is enabled though. 1075# 1076ifcapable !icu { 1077 do_catchsql_test e_expr-18.1.1 { 1078 SELECT regexp('abc', 'def') 1079 } {1 {no such function: regexp}} 1080 do_catchsql_test e_expr-18.1.2 { 1081 SELECT 'abc' REGEXP 'def' 1082 } {1 {no such function: REGEXP}} 1083} 1084 1085# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for 1086# the regexp() user function. 1087# 1088# EVIDENCE-OF: R-65524-61849 If an application-defined SQL function 1089# named "regexp" is added at run-time, then the "X REGEXP Y" operator 1090# will be implemented as a call to "regexp(Y,X)". 1091# 1092proc regexpfunc {args} { 1093 eval lappend ::regexpargs $args 1094 return 1 1095} 1096db func regexp -argcount 2 regexpfunc 1097set ::regexpargs [list] 1098do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1 1099do_test e_expr-18.2.2 { set regexpargs } {def abc} 1100set ::regexpargs [list] 1101do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 1102do_test e_expr-18.2.4 { set regexpargs } {Y X} 1103sqlite3 db test.db 1104 1105# EVIDENCE-OF: R-42037-37826 The default match() function implementation 1106# raises an exception and is not really useful for anything. 1107# 1108do_catchsql_test e_expr-19.1.1 { 1109 SELECT 'abc' MATCH 'def' 1110} {1 {unable to use function MATCH in the requested context}} 1111do_catchsql_test e_expr-19.1.2 { 1112 SELECT match('abc', 'def') 1113} {1 {unable to use function MATCH in the requested context}} 1114 1115# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for 1116# the match() application-defined function. 1117# 1118# EVIDENCE-OF: R-06021-09373 But extensions can override the match() 1119# function with more helpful logic. 1120# 1121proc matchfunc {args} { 1122 eval lappend ::matchargs $args 1123 return 1 1124} 1125db func match -argcount 2 matchfunc 1126set ::matchargs [list] 1127do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 1128do_test e_expr-19.2.2 { set matchargs } {def abc} 1129set ::matchargs [list] 1130do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 1131do_test e_expr-19.2.4 { set matchargs } {Y X} 1132sqlite3 db test.db 1133 1134#------------------------------------------------------------------------- 1135# Test cases for the testable statements related to the CASE expression. 1136# 1137# EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE 1138# expression: those with a base expression and those without. 1139# 1140do_execsql_test e_expr-20.1 { 1141 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 1142} {true} 1143do_execsql_test e_expr-20.2 { 1144 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 1145} {false} 1146 1147proc var {nm} { 1148 lappend ::varlist $nm 1149 return [set "::$nm"] 1150} 1151db func var var 1152 1153# EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each 1154# WHEN expression is evaluated and the result treated as a boolean, 1155# starting with the leftmost and continuing to the right. 1156# 1157foreach {a b c} {0 0 0} break 1158set varlist [list] 1159do_execsql_test e_expr-21.1.1 { 1160 SELECT CASE WHEN var('a') THEN 'A' 1161 WHEN var('b') THEN 'B' 1162 WHEN var('c') THEN 'C' END 1163} {{}} 1164do_test e_expr-21.1.2 { set varlist } {a b c} 1165set varlist [list] 1166do_execsql_test e_expr-21.1.3 { 1167 SELECT CASE WHEN var('c') THEN 'C' 1168 WHEN var('b') THEN 'B' 1169 WHEN var('a') THEN 'A' 1170 ELSE 'no result' 1171 END 1172} {{no result}} 1173do_test e_expr-21.1.4 { set varlist } {c b a} 1174 1175# EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the 1176# evaluation of the THEN expression that corresponds to the first WHEN 1177# expression that evaluates to true. 1178# 1179foreach {a b c} {0 1 0} break 1180do_execsql_test e_expr-21.2.1 { 1181 SELECT CASE WHEN var('a') THEN 'A' 1182 WHEN var('b') THEN 'B' 1183 WHEN var('c') THEN 'C' 1184 ELSE 'no result' 1185 END 1186} {B} 1187foreach {a b c} {0 1 1} break 1188do_execsql_test e_expr-21.2.2 { 1189 SELECT CASE WHEN var('a') THEN 'A' 1190 WHEN var('b') THEN 'B' 1191 WHEN var('c') THEN 'C' 1192 ELSE 'no result' 1193 END 1194} {B} 1195foreach {a b c} {0 0 1} break 1196do_execsql_test e_expr-21.2.3 { 1197 SELECT CASE WHEN var('a') THEN 'A' 1198 WHEN var('b') THEN 'B' 1199 WHEN var('c') THEN 'C' 1200 ELSE 'no result' 1201 END 1202} {C} 1203 1204# EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions 1205# evaluate to true, the result of evaluating the ELSE expression, if 1206# any. 1207# 1208foreach {a b c} {0 0 0} break 1209do_execsql_test e_expr-21.3.1 { 1210 SELECT CASE WHEN var('a') THEN 'A' 1211 WHEN var('b') THEN 'B' 1212 WHEN var('c') THEN 'C' 1213 ELSE 'no result' 1214 END 1215} {{no result}} 1216 1217# EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of 1218# the WHEN expressions are true, then the overall result is NULL. 1219# 1220db nullvalue null 1221do_execsql_test e_expr-21.3.2 { 1222 SELECT CASE WHEN var('a') THEN 'A' 1223 WHEN var('b') THEN 'B' 1224 WHEN var('c') THEN 'C' 1225 END 1226} {null} 1227db nullvalue {} 1228 1229# EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when 1230# evaluating WHEN terms. 1231# 1232do_execsql_test e_expr-21.4.1 { 1233 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END 1234} {B} 1235do_execsql_test e_expr-21.4.2 { 1236 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END 1237} {C} 1238 1239# EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base 1240# expression is evaluated just once and the result is compared against 1241# the evaluation of each WHEN expression from left to right. 1242# 1243# Note: This test case tests the "evaluated just once" part of the above 1244# statement. Tests associated with the next two statements test that the 1245# comparisons take place. 1246# 1247foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break 1248set ::varlist [list] 1249do_execsql_test e_expr-22.1.1 { 1250 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END 1251} {C} 1252do_test e_expr-22.1.2 { set ::varlist } {a} 1253 1254# EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the 1255# evaluation of the THEN expression that corresponds to the first WHEN 1256# expression for which the comparison is true. 1257# 1258do_execsql_test e_expr-22.2.1 { 1259 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1260} {B} 1261do_execsql_test e_expr-22.2.2 { 1262 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1263} {A} 1264 1265# EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions 1266# evaluate to a value equal to the base expression, the result of 1267# evaluating the ELSE expression, if any. 1268# 1269do_execsql_test e_expr-22.3.1 { 1270 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END 1271} {D} 1272 1273# EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of 1274# the WHEN expressions produce a result equal to the base expression, 1275# the overall result is NULL. 1276# 1277do_execsql_test e_expr-22.4.1 { 1278 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1279} {{}} 1280db nullvalue null 1281do_execsql_test e_expr-22.4.2 { 1282 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1283} {null} 1284db nullvalue {} 1285 1286# EVIDENCE-OF: R-11479-62774 When comparing a base expression against a 1287# WHEN expression, the same collating sequence, affinity, and 1288# NULL-handling rules apply as if the base expression and WHEN 1289# expression are respectively the left- and right-hand operands of an = 1290# operator. 1291# 1292proc rev {str} { 1293 set ret "" 1294 set chars [split $str] 1295 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} { 1296 append ret [lindex $chars $i] 1297 } 1298 set ret 1299} 1300proc reverse {lhs rhs} { 1301 string compare [rev $lhs] [rev $rhs] 1302} 1303db collate reverse reverse 1304do_execsql_test e_expr-23.1.1 { 1305 CREATE TABLE t1( 1306 a TEXT COLLATE NOCASE, 1307 b COLLATE REVERSE, 1308 c INTEGER, 1309 d BLOB 1310 ); 1311 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5); 1312} {} 1313do_execsql_test e_expr-23.1.2 { 1314 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1 1315} {B} 1316do_execsql_test e_expr-23.1.3 { 1317 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1 1318} {B} 1319do_execsql_test e_expr-23.1.4 { 1320 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 1321} {B} 1322do_execsql_test e_expr-23.1.5 { 1323 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 1324} {B} 1325do_execsql_test e_expr-23.1.6 { 1326 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END 1327} {B} 1328do_execsql_test e_expr-23.1.7 { 1329 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 1330} {A} 1331do_execsql_test e_expr-23.1.8 { 1332 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1 1333} {B} 1334do_execsql_test e_expr-23.1.9 { 1335 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END 1336} {B} 1337 1338# EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the 1339# result of the CASE is always the result of evaluating the ELSE 1340# expression if it exists, or NULL if it does not. 1341# 1342do_execsql_test e_expr-24.1.1 { 1343 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; 1344} {{}} 1345do_execsql_test e_expr-24.1.2 { 1346 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; 1347} {C} 1348 1349# EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, 1350# or short-circuit, evaluation. 1351# 1352set varlist [list] 1353foreach {a b c} {0 1 0} break 1354do_execsql_test e_expr-25.1.1 { 1355 SELECT CASE WHEN var('a') THEN 'A' 1356 WHEN var('b') THEN 'B' 1357 WHEN var('c') THEN 'C' 1358 END 1359} {B} 1360do_test e_expr-25.1.2 { set ::varlist } {a b} 1361set varlist [list] 1362do_execsql_test e_expr-25.1.3 { 1363 SELECT CASE '0' WHEN var('a') THEN 'A' 1364 WHEN var('b') THEN 'B' 1365 WHEN var('c') THEN 'C' 1366 END 1367} {A} 1368do_test e_expr-25.1.4 { set ::varlist } {a} 1369 1370# EVIDENCE-OF: R-34773-62253 The only difference between the following 1371# two CASE expressions is that the x expression is evaluated exactly 1372# once in the first example but might be evaluated multiple times in the 1373# second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN 1374# x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END 1375# 1376proc ceval {x} { 1377 incr ::evalcount 1378 return $x 1379} 1380db func ceval ceval 1381set ::evalcount 0 1382 1383do_execsql_test e_expr-26.1.1 { 1384 CREATE TABLE t2(x, w1, r1, w2, r2, r3); 1385 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); 1386 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); 1387 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); 1388} {} 1389do_execsql_test e_expr-26.1.2 { 1390 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 1391} {R1 R2 R3} 1392do_execsql_test e_expr-26.1.3 { 1393 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 1394} {R1 R2 R3} 1395 1396do_execsql_test e_expr-26.1.4 { 1397 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 1398} {R1 R2 R3} 1399do_test e_expr-26.1.5 { set ::evalcount } {3} 1400set ::evalcount 0 1401do_execsql_test e_expr-26.1.6 { 1402 SELECT CASE 1403 WHEN ceval(x)=w1 THEN r1 1404 WHEN ceval(x)=w2 THEN r2 1405 ELSE r3 END 1406 FROM t2 1407} {R1 R2 R3} 1408do_test e_expr-26.1.6 { set ::evalcount } {5} 1409 1410 1411#------------------------------------------------------------------------- 1412# Test statements related to CAST expressions. 1413# 1414# EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the 1415# conversion that takes place when a column affinity is applied to a 1416# value except that with the CAST operator the conversion always takes 1417# place even if the conversion lossy and irreversible, whereas column 1418# affinity only changes the data type of a value if the change is 1419# lossless and reversible. 1420# 1421do_execsql_test e_expr-27.1.1 { 1422 CREATE TABLE t3(a TEXT, b REAL, c INTEGER); 1423 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5); 1424 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3; 1425} {blob UVU text 1.23abc real 4.5} 1426do_execsql_test e_expr-27.1.2 { 1427 SELECT 1428 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT), 1429 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL), 1430 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER) 1431} {text UVU real 1.23 integer 4} 1432 1433# EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the 1434# result of the CAST expression is also NULL. 1435# 1436do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {} 1437do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {} 1438do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {} 1439do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {} 1440 1441# EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result 1442# is determined by applying the rules for determining column affinity to 1443# the type-name. 1444# 1445# The R-29283-15561 requirement above is demonstrated by all of the 1446# subsequent e_expr-26 tests. 1447# 1448# EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no 1449# affinity causes the value to be converted into a BLOB. 1450# 1451do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc 1452do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def 1453do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi 1454 1455# EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting 1456# the value to TEXT in the encoding of the database connection, then 1457# interpreting the resulting byte sequence as a BLOB instead of as TEXT. 1458# 1459do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' 1460do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' 1461do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' 1462rename db db2 1463sqlite3 db :memory: 1464ifcapable {utf16} { 1465db eval { PRAGMA encoding = 'utf-16le' } 1466do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' 1467do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' 1468do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' 1469} 1470db close 1471sqlite3 db :memory: 1472db eval { PRAGMA encoding = 'utf-16be' } 1473ifcapable {utf16} { 1474do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' 1475do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' 1476do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' 1477} 1478db close 1479rename db2 db 1480 1481# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence 1482# of bytes that make up the BLOB is interpreted as text encoded using 1483# the database encoding. 1484# 1485do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi 1486do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g 1487rename db db2 1488sqlite3 db :memory: 1489db eval { PRAGMA encoding = 'utf-16le' } 1490ifcapable {utf16} { 1491do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0 1492do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi 1493} 1494db close 1495rename db2 db 1496 1497# EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT 1498# renders the value as if via sqlite3_snprintf() except that the 1499# resulting TEXT uses the encoding of the database connection. 1500# 1501do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 1502do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 1503do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 1504do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 1505do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 1506do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 1507do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 1508do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 1509 1510# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the 1511# value is first converted to TEXT. 1512# 1513do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 1514do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0 1515do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87 1516do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001 1517rename db db2 1518sqlite3 db :memory: 1519ifcapable {utf16} { 1520db eval { PRAGMA encoding = 'utf-16le' } 1521do_expr_test e_expr-29.1.5 { 1522 CAST (X'31002E0032003300' AS REAL) } real 1.23 1523do_expr_test e_expr-29.1.6 { 1524 CAST (X'3200330030002E003000' AS REAL) } real 230.0 1525do_expr_test e_expr-29.1.7 { 1526 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87 1527do_expr_test e_expr-29.1.8 { 1528 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001 1529} 1530db close 1531rename db2 db 1532 1533# EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the 1534# longest possible prefix of the value that can be interpreted as a real 1535# number is extracted from the TEXT value and the remainder ignored. 1536# 1537do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23 1538do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45 1539do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212 1540do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0 1541 1542# EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are 1543# ignored when converging from TEXT to REAL. 1544# 1545do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23 1546do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45 1547do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212 1548do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0 1549 1550# EVIDENCE-OF: R-22662-28218 If there is no prefix that can be 1551# interpreted as a real number, the result of the conversion is 0.0. 1552# 1553do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0 1554do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0 1555do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0 1556 1557# EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the 1558# value is first converted to TEXT. 1559# 1560do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123 1561do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678 1562do_expr_test e_expr-30.1.3 { 1563 CAST(X'31303030303030' AS INTEGER) 1564} integer 1000000 1565do_expr_test e_expr-30.1.4 { 1566 CAST(X'2D31313235383939393036383432363234' AS INTEGER) 1567} integer -1125899906842624 1568 1569rename db db2 1570sqlite3 db :memory: 1571ifcapable {utf16} { 1572execsql { PRAGMA encoding = 'utf-16be' } 1573do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123 1574do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678 1575do_expr_test e_expr-30.1.7 { 1576 CAST(X'0031003000300030003000300030' AS INTEGER) 1577} integer 1000000 1578do_expr_test e_expr-30.1.8 { 1579 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 1580} integer -1125899906842624 1581} 1582db close 1583rename db2 db 1584 1585# EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the 1586# longest possible prefix of the value that can be interpreted as an 1587# integer number is extracted from the TEXT value and the remainder 1588# ignored. 1589# 1590do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123 1591do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523 1592do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2 1593do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1 1594 1595# EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when 1596# converting from TEXT to INTEGER are ignored. 1597# 1598do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123 1599do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523 1600do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2 1601do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1 1602 1603# EVIDENCE-OF: R-43164-44276 If there is no prefix that can be 1604# interpreted as an integer number, the result of the conversion is 0. 1605# 1606do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0 1607do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0 1608do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0 1609 1610# EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal 1611# integers only — conversion of hexadecimal integers stops at 1612# the "x" in the "0x" prefix of the hexadecimal integer string and thus 1613# result of the CAST is always zero. 1614do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0 1615do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0 1616 1617# EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER 1618# results in the integer between the REAL value and zero that is closest 1619# to the REAL value. 1620# 1621do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3 1622do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1 1623do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1 1624do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0 1625 1626# EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest 1627# possible signed integer (+9223372036854775807) then the result is the 1628# greatest possible signed integer and if the REAL is less than the 1629# least possible signed integer (-9223372036854775808) then the result 1630# is the least possible signed integer. 1631# 1632do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807 1633do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808 1634do_expr_test e_expr-31.2.3 { 1635 CAST(-9223372036854775809.0 AS INT) 1636} integer -9223372036854775808 1637do_expr_test e_expr-31.2.4 { 1638 CAST(9223372036854775809.0 AS INT) 1639} integer 9223372036854775807 1640 1641 1642# EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC 1643# first does a forced conversion into REAL but then further converts the 1644# result into INTEGER if and only if the conversion from REAL to INTEGER 1645# is lossless and reversible. 1646# 1647do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 1648do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45 1649do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 1650do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 1651do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 1652 1653# EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC 1654# is a no-op, even if a real value could be losslessly converted to an 1655# integer. 1656# 1657do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0 1658do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5 1659 1660do_expr_test e_expr-32.2.3 { 1661 CAST(-9223372036854775808 AS NUMERIC) 1662} integer -9223372036854775808 1663do_expr_test e_expr-32.2.4 { 1664 CAST(9223372036854775807 AS NUMERIC) 1665} integer 9223372036854775807 1666do_expr_test e_expr-32.2.5 { 1667 CAST('9223372036854775807 ' AS NUMERIC) 1668} integer 9223372036854775807 1669do_expr_test e_expr-32.2.6 { 1670 CAST(' 9223372036854775807 ' AS NUMERIC) 1671} integer 9223372036854775807 1672do_expr_test e_expr-32.2.7 { 1673 CAST(' ' AS NUMERIC) 1674} integer 0 1675do_execsql_test e_expr-32.2.8 { 1676 WITH t1(x) AS (VALUES 1677 ('9000000000000000001'), 1678 ('9000000000000000001x'), 1679 ('9000000000000000001 '), 1680 (' 9000000000000000001 '), 1681 (' 9000000000000000001'), 1682 (' 9000000000000000001.'), 1683 ('9223372036854775807'), 1684 ('9223372036854775807 '), 1685 (' 9223372036854775807 '), 1686 ('9223372036854775808'), 1687 (' 9223372036854775808 '), 1688 ('9223372036854775807.0'), 1689 ('9223372036854775807e+0'), 1690 ('-5.0'), 1691 ('-5e+0')) 1692 SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1; 1693} [list \ 1694 integer 9000000000000000001 \ 1695 integer 9000000000000000001 \ 1696 integer 9000000000000000001 \ 1697 integer 9000000000000000001 \ 1698 integer 9000000000000000001 \ 1699 integer 9000000000000000001 \ 1700 integer 9223372036854775807 \ 1701 integer 9223372036854775807 \ 1702 integer 9223372036854775807 \ 1703 real 9.22337203685478e+18 \ 1704 real 9.22337203685478e+18 \ 1705 integer 9223372036854775807 \ 1706 integer 9223372036854775807 \ 1707 integer -5 \ 1708 integer -5 \ 1709] 1710 1711# EVIDENCE-OF: R-64550-29191 Note that the result from casting any 1712# non-BLOB value into a BLOB and the result from casting any BLOB value 1713# into a non-BLOB value may be different depending on whether the 1714# database encoding is UTF-8, UTF-16be, or UTF-16le. 1715# 1716ifcapable {utf16} { 1717sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' } 1718sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' } 1719sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' } 1720foreach {tn castexpr differs} { 1721 1 { CAST(123 AS BLOB) } 1 1722 2 { CAST('' AS BLOB) } 0 1723 3 { CAST('abcd' AS BLOB) } 1 1724 1725 4 { CAST(X'abcd' AS TEXT) } 1 1726 5 { CAST(X'' AS TEXT) } 0 1727} { 1728 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"] 1729 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"] 1730 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"] 1731 1732 if {$differs} { 1733 set res [expr {$r1!=$r2 && $r2!=$r3}] 1734 } else { 1735 set res [expr {$r1==$r2 && $r2==$r3}] 1736 } 1737 1738 do_test e_expr-33.1.$tn {set res} 1 1739} 1740db1 close 1741db2 close 1742db3 close 1743} 1744 1745#------------------------------------------------------------------------- 1746# Test statements related to the EXISTS and NOT EXISTS operators. 1747# 1748catch { db close } 1749forcedelete test.db 1750sqlite3 db test.db 1751 1752do_execsql_test e_expr-34.1 { 1753 CREATE TABLE t1(a, b); 1754 INSERT INTO t1 VALUES(1, 2); 1755 INSERT INTO t1 VALUES(NULL, 2); 1756 INSERT INTO t1 VALUES(1, NULL); 1757 INSERT INTO t1 VALUES(NULL, NULL); 1758} {} 1759 1760# EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one 1761# of the integer values 0 and 1. 1762# 1763# This statement is not tested by itself. Instead, all e_expr-34.* tests 1764# following this point explicitly test that specific invocations of EXISTS 1765# return either integer 0 or integer 1. 1766# 1767 1768# EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified 1769# as the right-hand operand of the EXISTS operator would return one or 1770# more rows, then the EXISTS operator evaluates to 1. 1771# 1772foreach {tn expr} { 1773 1 { EXISTS ( SELECT a FROM t1 ) } 1774 2 { EXISTS ( SELECT b FROM t1 ) } 1775 3 { EXISTS ( SELECT 24 ) } 1776 4 { EXISTS ( SELECT NULL ) } 1777 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) } 1778} { 1779 do_expr_test e_expr-34.2.$tn $expr integer 1 1780} 1781 1782# EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no 1783# rows at all, then the EXISTS operator evaluates to 0. 1784# 1785foreach {tn expr} { 1786 1 { EXISTS ( SELECT a FROM t1 WHERE 0) } 1787 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) } 1788 3 { EXISTS ( SELECT 24 WHERE 0) } 1789 4 { EXISTS ( SELECT NULL WHERE 1=2) } 1790} { 1791 do_expr_test e_expr-34.3.$tn $expr integer 0 1792} 1793 1794# EVIDENCE-OF: R-35109-49139 The number of columns in each row returned 1795# by the SELECT statement (if any) and the specific values returned have 1796# no effect on the results of the EXISTS operator. 1797# 1798foreach {tn expr res} { 1799 1 { EXISTS ( SELECT * FROM t1 ) } 1 1800 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1 1801 3 { EXISTS ( SELECT 24, 25 ) } 1 1802 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1 1803 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1 1804 1805 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0 1806 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0 1807 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0 1808 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0 1809} { 1810 do_expr_test e_expr-34.4.$tn $expr integer $res 1811} 1812 1813# EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values 1814# are not handled any differently from rows without NULL values. 1815# 1816foreach {tn e1 e2} { 1817 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) } 1818 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) } 1819} { 1820 set res [db one "SELECT $e1"] 1821 do_expr_test e_expr-34.5.${tn}a $e1 integer $res 1822 do_expr_test e_expr-34.5.${tn}b $e2 integer $res 1823} 1824 1825#------------------------------------------------------------------------- 1826# Test statements related to scalar sub-queries. 1827# 1828 1829catch { db close } 1830forcedelete test.db 1831sqlite3 db test.db 1832do_test e_expr-35.0 { 1833 execsql { 1834 CREATE TABLE t2(a, b); 1835 INSERT INTO t2 VALUES('one', 'two'); 1836 INSERT INTO t2 VALUES('three', NULL); 1837 INSERT INTO t2 VALUES(4, 5.0); 1838 } 1839} {} 1840 1841# EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses 1842# is a subquery. 1843# 1844# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including 1845# aggregate and compound SELECT queries (queries with keywords like 1846# UNION or EXCEPT) are allowed as scalar subqueries. 1847# 1848do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35 1849do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {} 1850 1851do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3 1852do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4 1853 1854do_expr_test e_expr-35.1.5 { 1855 (SELECT b FROM t2 UNION SELECT a+1 FROM t2) 1856} null {} 1857do_expr_test e_expr-35.1.6 { 1858 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1) 1859} integer 4 1860 1861# EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns 1862# is a row value subquery and can only be used as the operand of a 1863# comparison operator. 1864# 1865# The following block tests that errors are returned in a bunch of cases 1866# where a subquery returns more than one column. 1867# 1868set M {/1 {sub-select returns [23] columns - expected 1}/} 1869foreach {tn sql} { 1870 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } 1871 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } 1872 3 { SELECT (SELECT 1, 2) } 1873 4 { SELECT (SELECT NULL, NULL, NULL) } 1874 5 { SELECT (SELECT * FROM t2) } 1875 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } 1876} { 1877 do_catchsql_test e_expr-35.2.$tn $sql $M 1878} 1879 1880# EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the 1881# first row of the result from the enclosed SELECT statement. 1882# 1883# EVIDENCE-OF: R-15900-52156 In other words, an implied "LIMIT 1" is 1884# added to the subquery, overriding an explicitly coded LIMIT. 1885# 1886do_execsql_test e_expr-36.3.1 { 1887 CREATE TABLE t4(x, y); 1888 INSERT INTO t4 VALUES(1, 'one'); 1889 INSERT INTO t4 VALUES(2, 'two'); 1890 INSERT INTO t4 VALUES(3, 'three'); 1891} {} 1892 1893foreach {tn expr restype resval} { 1894 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1 1895 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1 1896 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3 1897 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2 1898 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two 1899 1900 7 { ( SELECT sum(x) FROM t4 ) } integer 6 1901 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree 1902 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 1903 1904} { 1905 do_expr_test e_expr-36.3.$tn $expr $restype $resval 1906} 1907 1908# EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL 1909# if the enclosed SELECT statement returns no rows. 1910# 1911foreach {tn expr} { 1912 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) } 1913 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) } 1914} { 1915 do_expr_test e_expr-36.4.$tn $expr null {} 1916} 1917 1918# EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0, 1919# 'english' and '0' are all considered to be false. 1920# 1921do_execsql_test e_expr-37.1 { 1922 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END; 1923} {false} 1924do_execsql_test e_expr-37.2 { 1925 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END; 1926} {false} 1927do_execsql_test e_expr-37.3 { 1928 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END; 1929} {false} 1930do_execsql_test e_expr-37.4 { 1931 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END; 1932} {false} 1933do_execsql_test e_expr-37.5 { 1934 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END; 1935} {false} 1936 1937# EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are 1938# considered to be true. 1939# 1940do_execsql_test e_expr-37.6 { 1941 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END; 1942} {true} 1943do_execsql_test e_expr-37.7 { 1944 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END; 1945} {true} 1946do_execsql_test e_expr-37.8 { 1947 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END; 1948} {true} 1949do_execsql_test e_expr-37.9 { 1950 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END; 1951} {true} 1952do_execsql_test e_expr-37.10 { 1953 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END; 1954} {true} 1955 1956 1957finish_test 1958