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