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 do_test e_expr-13.1.$tn { 852 set ::xcount 0 853 set a [execsql "SELECT $expr"] 854 list $::xcount $a 855 } [list $nEval $res] 856} 857 858# EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is 859# the same as the precedence as operators == and != and LIKE and groups 860# left to right. 861# 862# Therefore, BETWEEN groups more tightly than operator "AND", but less 863# so than "<". 864# 865do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1 866do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1 867do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0 868do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1 869do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1 870do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0 871 872do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1 873do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1 874do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0 875do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1 876do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1 877do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0 878 879do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1 880do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1 881do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0 882do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1 883do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1 884do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0 885 886do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0 887do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0 888do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1 889do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0 890do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0 891do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1 892 893do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1 894do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1 895do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0 896do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0 897do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0 898do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1 899 900#------------------------------------------------------------------------- 901# Test the statements related to the LIKE and GLOB operators. 902# 903# EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching 904# comparison. 905# 906# EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE 907# operator contains the pattern and the left hand operand contains the 908# string to match against the pattern. 909# 910do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0 911do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1 912 913# EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern 914# matches any sequence of zero or more characters in the string. 915# 916do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1 917do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1 918do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1 919 920# EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern 921# matches any single character in the string. 922# 923do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0 924do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1 925do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0 926 927# EVIDENCE-OF: R-59007-20454 Any other character matches itself or its 928# lower/upper case equivalent (i.e. case-insensitive matching). 929# 930do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1 931do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1 932do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0 933 934# EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case 935# for ASCII characters by default. 936# 937# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by 938# default for unicode characters that are beyond the ASCII range. 939# 940# EVIDENCE-OF: R-44381-11669 the expression 941# 'a' LIKE 'A' is TRUE but 942# 'æ' LIKE 'Æ' is FALSE. 943# 944# The restriction to ASCII characters does not apply if the ICU 945# library is compiled in. When ICU is enabled SQLite does not act 946# as it does "by default". 947# 948do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1 949ifcapable !icu { 950 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0 951} 952 953# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present, 954# then the expression following the ESCAPE keyword must evaluate to a 955# string consisting of a single character. 956# 957do_catchsql_test e_expr-14.6.1 { 958 SELECT 'A' LIKE 'a' ESCAPE '12' 959} {1 {ESCAPE expression must be a single character}} 960do_catchsql_test e_expr-14.6.2 { 961 SELECT 'A' LIKE 'a' ESCAPE '' 962} {1 {ESCAPE expression must be a single character}} 963do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1} 964do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1} 965 966# EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE 967# pattern to include literal percent or underscore characters. 968# 969# EVIDENCE-OF: R-13345-31830 The escape character followed by a percent 970# symbol (%), underscore (_), or a second instance of the escape 971# character itself matches a literal percent symbol, underscore, or a 972# single escape character, respectively. 973# 974do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1 975do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0 976do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0 977do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0 978do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0 979 980do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1 981do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0 982do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0 983do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0 984do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0 985 986do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1 987do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0 988do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0 989do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0 990 991# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by 992# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z). 993# 994proc likefunc {args} { 995 eval lappend ::likeargs $args 996 return 1 997} 998db func like -argcount 2 likefunc 999db func like -argcount 3 likefunc 1000set ::likeargs [list] 1001do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1 1002do_test e_expr-15.1.2 { set likeargs } {def abc} 1003set ::likeargs [list] 1004do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1 1005do_test e_expr-15.1.4 { set likeargs } {def abc X} 1006db close 1007sqlite3 db test.db 1008 1009# EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case 1010# sensitive using the case_sensitive_like pragma. 1011# 1012do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1 1013do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {} 1014do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0 1015do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 1016do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {} 1017do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1 1018do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 1019 1020# EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but 1021# uses the Unix file globbing syntax for its wildcards. 1022# 1023# EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE. 1024# 1025do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0 1026do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1 1027do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0 1028do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1 1029 1030do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1 1031do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0 1032do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0 1033 1034# EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the 1035# NOT keyword to invert the sense of the test. 1036# 1037do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1 1038do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0 1039do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0 1040do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0 1041do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1 1042 1043db nullvalue null 1044do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null 1045do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null 1046do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null 1047do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null 1048db nullvalue {} 1049 1050# EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by 1051# calling the function glob(Y,X) and can be modified by overriding that 1052# function. 1053proc globfunc {args} { 1054 eval lappend ::globargs $args 1055 return 1 1056} 1057db func glob -argcount 2 globfunc 1058set ::globargs [list] 1059do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1 1060do_test e_expr-17.3.2 { set globargs } {def abc} 1061set ::globargs [list] 1062do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0 1063do_test e_expr-17.3.4 { set globargs } {Y X} 1064sqlite3 db test.db 1065 1066# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by 1067# default and so use of the REGEXP operator will normally result in an 1068# error message. 1069# 1070# There is a regexp function if ICU is enabled though. 1071# 1072ifcapable !icu { 1073 do_catchsql_test e_expr-18.1.1 { 1074 SELECT regexp('abc', 'def') 1075 } {1 {no such function: regexp}} 1076 do_catchsql_test e_expr-18.1.2 { 1077 SELECT 'abc' REGEXP 'def' 1078 } {1 {no such function: REGEXP}} 1079} 1080 1081# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for 1082# the regexp() user function. 1083# 1084# EVIDENCE-OF: R-65524-61849 If an application-defined SQL function 1085# named "regexp" is added at run-time, then the "X REGEXP Y" operator 1086# will be implemented as a call to "regexp(Y,X)". 1087# 1088proc regexpfunc {args} { 1089 eval lappend ::regexpargs $args 1090 return 1 1091} 1092db func regexp -argcount 2 regexpfunc 1093set ::regexpargs [list] 1094do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1 1095do_test e_expr-18.2.2 { set regexpargs } {def abc} 1096set ::regexpargs [list] 1097do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 1098do_test e_expr-18.2.4 { set regexpargs } {Y X} 1099sqlite3 db test.db 1100 1101# EVIDENCE-OF: R-42037-37826 The default match() function implementation 1102# raises an exception and is not really useful for anything. 1103# 1104do_catchsql_test e_expr-19.1.1 { 1105 SELECT 'abc' MATCH 'def' 1106} {1 {unable to use function MATCH in the requested context}} 1107do_catchsql_test e_expr-19.1.2 { 1108 SELECT match('abc', 'def') 1109} {1 {unable to use function MATCH in the requested context}} 1110 1111# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for 1112# the match() application-defined function. 1113# 1114# EVIDENCE-OF: R-06021-09373 But extensions can override the match() 1115# function with more helpful logic. 1116# 1117proc matchfunc {args} { 1118 eval lappend ::matchargs $args 1119 return 1 1120} 1121db func match -argcount 2 matchfunc 1122set ::matchargs [list] 1123do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 1124do_test e_expr-19.2.2 { set matchargs } {def abc} 1125set ::matchargs [list] 1126do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 1127do_test e_expr-19.2.4 { set matchargs } {Y X} 1128sqlite3 db test.db 1129 1130#------------------------------------------------------------------------- 1131# Test cases for the testable statements related to the CASE expression. 1132# 1133# EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE 1134# expression: those with a base expression and those without. 1135# 1136do_execsql_test e_expr-20.1 { 1137 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 1138} {true} 1139do_execsql_test e_expr-20.2 { 1140 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 1141} {false} 1142 1143proc var {nm} { 1144 lappend ::varlist $nm 1145 return [set "::$nm"] 1146} 1147db func var var 1148 1149# EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each 1150# WHEN expression is evaluated and the result treated as a boolean, 1151# starting with the leftmost and continuing to the right. 1152# 1153foreach {a b c} {0 0 0} break 1154set varlist [list] 1155do_execsql_test e_expr-21.1.1 { 1156 SELECT CASE WHEN var('a') THEN 'A' 1157 WHEN var('b') THEN 'B' 1158 WHEN var('c') THEN 'C' END 1159} {{}} 1160do_test e_expr-21.1.2 { set varlist } {a b c} 1161set varlist [list] 1162do_execsql_test e_expr-21.1.3 { 1163 SELECT CASE WHEN var('c') THEN 'C' 1164 WHEN var('b') THEN 'B' 1165 WHEN var('a') THEN 'A' 1166 ELSE 'no result' 1167 END 1168} {{no result}} 1169do_test e_expr-21.1.4 { set varlist } {c b a} 1170 1171# EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the 1172# evaluation of the THEN expression that corresponds to the first WHEN 1173# expression that evaluates to true. 1174# 1175foreach {a b c} {0 1 0} break 1176do_execsql_test e_expr-21.2.1 { 1177 SELECT CASE WHEN var('a') THEN 'A' 1178 WHEN var('b') THEN 'B' 1179 WHEN var('c') THEN 'C' 1180 ELSE 'no result' 1181 END 1182} {B} 1183foreach {a b c} {0 1 1} break 1184do_execsql_test e_expr-21.2.2 { 1185 SELECT CASE WHEN var('a') THEN 'A' 1186 WHEN var('b') THEN 'B' 1187 WHEN var('c') THEN 'C' 1188 ELSE 'no result' 1189 END 1190} {B} 1191foreach {a b c} {0 0 1} break 1192do_execsql_test e_expr-21.2.3 { 1193 SELECT CASE WHEN var('a') THEN 'A' 1194 WHEN var('b') THEN 'B' 1195 WHEN var('c') THEN 'C' 1196 ELSE 'no result' 1197 END 1198} {C} 1199 1200# EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions 1201# evaluate to true, the result of evaluating the ELSE expression, if 1202# any. 1203# 1204foreach {a b c} {0 0 0} break 1205do_execsql_test e_expr-21.3.1 { 1206 SELECT CASE WHEN var('a') THEN 'A' 1207 WHEN var('b') THEN 'B' 1208 WHEN var('c') THEN 'C' 1209 ELSE 'no result' 1210 END 1211} {{no result}} 1212 1213# EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of 1214# the WHEN expressions are true, then the overall result is NULL. 1215# 1216db nullvalue null 1217do_execsql_test e_expr-21.3.2 { 1218 SELECT CASE WHEN var('a') THEN 'A' 1219 WHEN var('b') THEN 'B' 1220 WHEN var('c') THEN 'C' 1221 END 1222} {null} 1223db nullvalue {} 1224 1225# EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when 1226# evaluating WHEN terms. 1227# 1228do_execsql_test e_expr-21.4.1 { 1229 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END 1230} {B} 1231do_execsql_test e_expr-21.4.2 { 1232 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END 1233} {C} 1234 1235# EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base 1236# expression is evaluated just once and the result is compared against 1237# the evaluation of each WHEN expression from left to right. 1238# 1239# Note: This test case tests the "evaluated just once" part of the above 1240# statement. Tests associated with the next two statements test that the 1241# comparisons take place. 1242# 1243foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break 1244set ::varlist [list] 1245do_execsql_test e_expr-22.1.1 { 1246 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END 1247} {C} 1248do_test e_expr-22.1.2 { set ::varlist } {a} 1249 1250# EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the 1251# evaluation of the THEN expression that corresponds to the first WHEN 1252# expression for which the comparison is true. 1253# 1254do_execsql_test e_expr-22.2.1 { 1255 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1256} {B} 1257do_execsql_test e_expr-22.2.2 { 1258 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1259} {A} 1260 1261# EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions 1262# evaluate to a value equal to the base expression, the result of 1263# evaluating the ELSE expression, if any. 1264# 1265do_execsql_test e_expr-22.3.1 { 1266 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END 1267} {D} 1268 1269# EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of 1270# the WHEN expressions produce a result equal to the base expression, 1271# the overall result is NULL. 1272# 1273do_execsql_test e_expr-22.4.1 { 1274 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1275} {{}} 1276db nullvalue null 1277do_execsql_test e_expr-22.4.2 { 1278 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 1279} {null} 1280db nullvalue {} 1281 1282# EVIDENCE-OF: R-11479-62774 When comparing a base expression against a 1283# WHEN expression, the same collating sequence, affinity, and 1284# NULL-handling rules apply as if the base expression and WHEN 1285# expression are respectively the left- and right-hand operands of an = 1286# operator. 1287# 1288proc rev {str} { 1289 set ret "" 1290 set chars [split $str] 1291 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} { 1292 append ret [lindex $chars $i] 1293 } 1294 set ret 1295} 1296proc reverse {lhs rhs} { 1297 string compare [rev $lhs] [rev $rhs] 1298} 1299db collate reverse reverse 1300do_execsql_test e_expr-23.1.1 { 1301 CREATE TABLE t1( 1302 a TEXT COLLATE NOCASE, 1303 b COLLATE REVERSE, 1304 c INTEGER, 1305 d BLOB 1306 ); 1307 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5); 1308} {} 1309do_execsql_test e_expr-23.1.2 { 1310 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1 1311} {B} 1312do_execsql_test e_expr-23.1.3 { 1313 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1 1314} {B} 1315do_execsql_test e_expr-23.1.4 { 1316 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 1317} {B} 1318do_execsql_test e_expr-23.1.5 { 1319 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 1320} {B} 1321do_execsql_test e_expr-23.1.6 { 1322 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END 1323} {B} 1324do_execsql_test e_expr-23.1.7 { 1325 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 1326} {A} 1327do_execsql_test e_expr-23.1.8 { 1328 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1 1329} {B} 1330do_execsql_test e_expr-23.1.9 { 1331 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END 1332} {B} 1333 1334# EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the 1335# result of the CASE is always the result of evaluating the ELSE 1336# expression if it exists, or NULL if it does not. 1337# 1338do_execsql_test e_expr-24.1.1 { 1339 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; 1340} {{}} 1341do_execsql_test e_expr-24.1.2 { 1342 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; 1343} {C} 1344 1345# EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, 1346# or short-circuit, evaluation. 1347# 1348set varlist [list] 1349foreach {a b c} {0 1 0} break 1350do_execsql_test e_expr-25.1.1 { 1351 SELECT CASE WHEN var('a') THEN 'A' 1352 WHEN var('b') THEN 'B' 1353 WHEN var('c') THEN 'C' 1354 END 1355} {B} 1356do_test e_expr-25.1.2 { set ::varlist } {a b} 1357set varlist [list] 1358do_execsql_test e_expr-25.1.3 { 1359 SELECT CASE '0' WHEN var('a') THEN 'A' 1360 WHEN var('b') THEN 'B' 1361 WHEN var('c') THEN 'C' 1362 END 1363} {A} 1364do_test e_expr-25.1.4 { set ::varlist } {a} 1365 1366# EVIDENCE-OF: R-34773-62253 The only difference between the following 1367# two CASE expressions is that the x expression is evaluated exactly 1368# once in the first example but might be evaluated multiple times in the 1369# second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN 1370# x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END 1371# 1372proc ceval {x} { 1373 incr ::evalcount 1374 return $x 1375} 1376db func ceval ceval 1377set ::evalcount 0 1378 1379do_execsql_test e_expr-26.1.1 { 1380 CREATE TABLE t2(x, w1, r1, w2, r2, r3); 1381 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); 1382 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); 1383 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); 1384} {} 1385do_execsql_test e_expr-26.1.2 { 1386 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 1387} {R1 R2 R3} 1388do_execsql_test e_expr-26.1.3 { 1389 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 1390} {R1 R2 R3} 1391 1392do_execsql_test e_expr-26.1.4 { 1393 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 1394} {R1 R2 R3} 1395do_test e_expr-26.1.5 { set ::evalcount } {3} 1396set ::evalcount 0 1397do_execsql_test e_expr-26.1.6 { 1398 SELECT CASE 1399 WHEN ceval(x)=w1 THEN r1 1400 WHEN ceval(x)=w2 THEN r2 1401 ELSE r3 END 1402 FROM t2 1403} {R1 R2 R3} 1404do_test e_expr-26.1.6 { set ::evalcount } {5} 1405 1406 1407#------------------------------------------------------------------------- 1408# Test statements related to CAST expressions. 1409# 1410# EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the 1411# conversion that takes place when a column affinity is applied to a 1412# value except that with the CAST operator the conversion always takes 1413# place even if the conversion lossy and irreversible, whereas column 1414# affinity only changes the data type of a value if the change is 1415# lossless and reversible. 1416# 1417do_execsql_test e_expr-27.1.1 { 1418 CREATE TABLE t3(a TEXT, b REAL, c INTEGER); 1419 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5); 1420 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3; 1421} {blob UVU text 1.23abc real 4.5} 1422do_execsql_test e_expr-27.1.2 { 1423 SELECT 1424 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT), 1425 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL), 1426 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER) 1427} {text UVU real 1.23 integer 4} 1428 1429# EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the 1430# result of the CAST expression is also NULL. 1431# 1432do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {} 1433do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {} 1434do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {} 1435do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {} 1436 1437# EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no 1438# affinity causes the value to be converted into a BLOB. 1439# 1440do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc 1441do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def 1442do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi 1443 1444# EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting 1445# the value to TEXT in the encoding of the database connection, then 1446# interpreting the resulting byte sequence as a BLOB instead of as TEXT. 1447# 1448do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' 1449do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' 1450do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' 1451rename db db2 1452sqlite3 db :memory: 1453ifcapable {utf16} { 1454db eval { PRAGMA encoding = 'utf-16le' } 1455do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' 1456do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' 1457do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' 1458} 1459db close 1460sqlite3 db :memory: 1461db eval { PRAGMA encoding = 'utf-16be' } 1462ifcapable {utf16} { 1463do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' 1464do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' 1465do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' 1466} 1467db close 1468rename db2 db 1469 1470# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence 1471# of bytes that make up the BLOB is interpreted as text encoded using 1472# the database encoding. 1473# 1474do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi 1475do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g 1476rename db db2 1477sqlite3 db :memory: 1478db eval { PRAGMA encoding = 'utf-16le' } 1479ifcapable {utf16} { 1480do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0 1481do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi 1482} 1483db close 1484rename db2 db 1485 1486# EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT 1487# renders the value as if via sqlite3_snprintf() except that the 1488# resulting TEXT uses the encoding of the database connection. 1489# 1490do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 1491do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 1492do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 1493do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 1494do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 1495do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 1496do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 1497do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 1498 1499# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the 1500# value is first converted to TEXT. 1501# 1502do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 1503do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0 1504do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87 1505do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001 1506rename db db2 1507sqlite3 db :memory: 1508ifcapable {utf16} { 1509db eval { PRAGMA encoding = 'utf-16le' } 1510do_expr_test e_expr-29.1.5 { 1511 CAST (X'31002E0032003300' AS REAL) } real 1.23 1512do_expr_test e_expr-29.1.6 { 1513 CAST (X'3200330030002E003000' AS REAL) } real 230.0 1514do_expr_test e_expr-29.1.7 { 1515 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87 1516do_expr_test e_expr-29.1.8 { 1517 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001 1518} 1519db close 1520rename db2 db 1521 1522# EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the 1523# longest possible prefix of the value that can be interpreted as a real 1524# number is extracted from the TEXT value and the remainder ignored. 1525# 1526do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23 1527do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45 1528do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212 1529do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0 1530 1531# EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are 1532# ignored when converging from TEXT to REAL. 1533# 1534do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23 1535do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45 1536do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212 1537do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0 1538 1539# EVIDENCE-OF: R-22662-28218 If there is no prefix that can be 1540# interpreted as a real number, the result of the conversion is 0.0. 1541# 1542do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0 1543do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0 1544do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0 1545 1546# EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the 1547# value is first converted to TEXT. 1548# 1549do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123 1550do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678 1551do_expr_test e_expr-30.1.3 { 1552 CAST(X'31303030303030' AS INTEGER) 1553} integer 1000000 1554do_expr_test e_expr-30.1.4 { 1555 CAST(X'2D31313235383939393036383432363234' AS INTEGER) 1556} integer -1125899906842624 1557 1558rename db db2 1559sqlite3 db :memory: 1560ifcapable {utf16} { 1561execsql { PRAGMA encoding = 'utf-16be' } 1562do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123 1563do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678 1564do_expr_test e_expr-30.1.7 { 1565 CAST(X'0031003000300030003000300030' AS INTEGER) 1566} integer 1000000 1567do_expr_test e_expr-30.1.8 { 1568 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 1569} integer -1125899906842624 1570} 1571db close 1572rename db2 db 1573 1574# EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the 1575# longest possible prefix of the value that can be interpreted as an 1576# integer number is extracted from the TEXT value and the remainder 1577# ignored. 1578# 1579do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123 1580do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523 1581do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2 1582do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1 1583 1584# EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when 1585# converting from TEXT to INTEGER are ignored. 1586# 1587do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123 1588do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523 1589do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2 1590do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1 1591 1592# EVIDENCE-OF: R-43164-44276 If there is no prefix that can be 1593# interpreted as an integer number, the result of the conversion is 0. 1594# 1595do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0 1596do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0 1597do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0 1598 1599# EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal 1600# integers only — conversion of hexadecimal integers stops at 1601# the "x" in the "0x" prefix of the hexadecimal integer string and thus 1602# result of the CAST is always zero. 1603do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0 1604do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0 1605 1606# EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER 1607# results in the integer between the REAL value and zero that is closest 1608# to the REAL value. 1609# 1610do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3 1611do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1 1612do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1 1613do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0 1614 1615# EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest 1616# possible signed integer (+9223372036854775807) then the result is the 1617# greatest possible signed integer and if the REAL is less than the 1618# least possible signed integer (-9223372036854775808) then the result 1619# is the least possible signed integer. 1620# 1621do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807 1622do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808 1623do_expr_test e_expr-31.2.3 { 1624 CAST(-9223372036854775809.0 AS INT) 1625} integer -9223372036854775808 1626do_expr_test e_expr-31.2.4 { 1627 CAST(9223372036854775809.0 AS INT) 1628} integer 9223372036854775807 1629 1630 1631# EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC 1632# first does a forced conversion into REAL but then further converts the 1633# result into INTEGER if and only if the conversion from REAL to INTEGER 1634# is lossless and reversible. 1635# 1636do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 1637do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45 1638do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 1639do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 1640do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 1641 1642# EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC 1643# is a no-op, even if a real value could be losslessly converted to an 1644# integer. 1645# 1646do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0 1647do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5 1648 1649do_expr_test e_expr-32.2.3 { 1650 CAST(-9223372036854775808 AS NUMERIC) 1651} integer -9223372036854775808 1652do_expr_test e_expr-32.2.4 { 1653 CAST(9223372036854775807 AS NUMERIC) 1654} integer 9223372036854775807 1655 1656# EVIDENCE-OF: R-64550-29191 Note that the result from casting any 1657# non-BLOB value into a BLOB and the result from casting any BLOB value 1658# into a non-BLOB value may be different depending on whether the 1659# database encoding is UTF-8, UTF-16be, or UTF-16le. 1660# 1661ifcapable {utf16} { 1662sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' } 1663sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' } 1664sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' } 1665foreach {tn castexpr differs} { 1666 1 { CAST(123 AS BLOB) } 1 1667 2 { CAST('' AS BLOB) } 0 1668 3 { CAST('abcd' AS BLOB) } 1 1669 1670 4 { CAST(X'abcd' AS TEXT) } 1 1671 5 { CAST(X'' AS TEXT) } 0 1672} { 1673 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"] 1674 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"] 1675 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"] 1676 1677 if {$differs} { 1678 set res [expr {$r1!=$r2 && $r2!=$r3}] 1679 } else { 1680 set res [expr {$r1==$r2 && $r2==$r3}] 1681 } 1682 1683 do_test e_expr-33.1.$tn {set res} 1 1684} 1685db1 close 1686db2 close 1687db3 close 1688} 1689 1690#------------------------------------------------------------------------- 1691# Test statements related to the EXISTS and NOT EXISTS operators. 1692# 1693catch { db close } 1694forcedelete test.db 1695sqlite3 db test.db 1696 1697do_execsql_test e_expr-34.1 { 1698 CREATE TABLE t1(a, b); 1699 INSERT INTO t1 VALUES(1, 2); 1700 INSERT INTO t1 VALUES(NULL, 2); 1701 INSERT INTO t1 VALUES(1, NULL); 1702 INSERT INTO t1 VALUES(NULL, NULL); 1703} {} 1704 1705# EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one 1706# of the integer values 0 and 1. 1707# 1708# This statement is not tested by itself. Instead, all e_expr-34.* tests 1709# following this point explicitly test that specific invocations of EXISTS 1710# return either integer 0 or integer 1. 1711# 1712 1713# EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified 1714# as the right-hand operand of the EXISTS operator would return one or 1715# more rows, then the EXISTS operator evaluates to 1. 1716# 1717foreach {tn expr} { 1718 1 { EXISTS ( SELECT a FROM t1 ) } 1719 2 { EXISTS ( SELECT b FROM t1 ) } 1720 3 { EXISTS ( SELECT 24 ) } 1721 4 { EXISTS ( SELECT NULL ) } 1722 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) } 1723} { 1724 do_expr_test e_expr-34.2.$tn $expr integer 1 1725} 1726 1727# EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no 1728# rows at all, then the EXISTS operator evaluates to 0. 1729# 1730foreach {tn expr} { 1731 1 { EXISTS ( SELECT a FROM t1 WHERE 0) } 1732 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) } 1733 3 { EXISTS ( SELECT 24 WHERE 0) } 1734 4 { EXISTS ( SELECT NULL WHERE 1=2) } 1735} { 1736 do_expr_test e_expr-34.3.$tn $expr integer 0 1737} 1738 1739# EVIDENCE-OF: R-35109-49139 The number of columns in each row returned 1740# by the SELECT statement (if any) and the specific values returned have 1741# no effect on the results of the EXISTS operator. 1742# 1743foreach {tn expr res} { 1744 1 { EXISTS ( SELECT * FROM t1 ) } 1 1745 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1 1746 3 { EXISTS ( SELECT 24, 25 ) } 1 1747 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1 1748 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1 1749 1750 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0 1751 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0 1752 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0 1753 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0 1754} { 1755 do_expr_test e_expr-34.4.$tn $expr integer $res 1756} 1757 1758# EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values 1759# are not handled any differently from rows without NULL values. 1760# 1761foreach {tn e1 e2} { 1762 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) } 1763 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) } 1764} { 1765 set res [db one "SELECT $e1"] 1766 do_expr_test e_expr-34.5.${tn}a $e1 integer $res 1767 do_expr_test e_expr-34.5.${tn}b $e2 integer $res 1768} 1769 1770#------------------------------------------------------------------------- 1771# Test statements related to scalar sub-queries. 1772# 1773 1774catch { db close } 1775forcedelete test.db 1776sqlite3 db test.db 1777do_test e_expr-35.0 { 1778 execsql { 1779 CREATE TABLE t2(a, b); 1780 INSERT INTO t2 VALUES('one', 'two'); 1781 INSERT INTO t2 VALUES('three', NULL); 1782 INSERT INTO t2 VALUES(4, 5.0); 1783 } 1784} {} 1785 1786# EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses 1787# may appear as a scalar quantity. 1788# 1789# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including 1790# aggregate and compound SELECT queries (queries with keywords like 1791# UNION or EXCEPT) are allowed as scalar subqueries. 1792# 1793do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35 1794do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {} 1795 1796do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3 1797do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4 1798 1799do_expr_test e_expr-35.1.5 { 1800 (SELECT b FROM t2 UNION SELECT a+1 FROM t2) 1801} null {} 1802do_expr_test e_expr-35.1.6 { 1803 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1) 1804} integer 4 1805 1806# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must 1807# return a result set with a single column. 1808# 1809# The following block tests that errors are returned in a bunch of cases 1810# where a subquery returns more than one column. 1811# 1812set M {only a single result allowed for a SELECT that is part of an expression} 1813foreach {tn sql} { 1814 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } 1815 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } 1816 3 { SELECT (SELECT 1, 2) } 1817 4 { SELECT (SELECT NULL, NULL, NULL) } 1818 5 { SELECT (SELECT * FROM t2) } 1819 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } 1820} { 1821 do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M] 1822} 1823 1824# EVIDENCE-OF: R-35764-28041 The result of the expression is the value 1825# of the only column in the first row returned by the SELECT statement. 1826# 1827# EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result 1828# row, all rows after the first are ignored. 1829# 1830do_execsql_test e_expr-36.3.1 { 1831 CREATE TABLE t4(x, y); 1832 INSERT INTO t4 VALUES(1, 'one'); 1833 INSERT INTO t4 VALUES(2, 'two'); 1834 INSERT INTO t4 VALUES(3, 'three'); 1835} {} 1836 1837foreach {tn expr restype resval} { 1838 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1 1839 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1 1840 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3 1841 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2 1842 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two 1843 1844 7 { ( SELECT sum(x) FROM t4 ) } integer 6 1845 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree 1846 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 1847 1848} { 1849 do_expr_test e_expr-36.3.$tn $expr $restype $resval 1850} 1851 1852# EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the 1853# value of the expression is NULL. 1854# 1855foreach {tn expr} { 1856 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) } 1857 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) } 1858} { 1859 do_expr_test e_expr-36.4.$tn $expr null {} 1860} 1861 1862# EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0, 1863# 'english' and '0' are all considered to be false. 1864# 1865do_execsql_test e_expr-37.1 { 1866 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END; 1867} {false} 1868do_execsql_test e_expr-37.2 { 1869 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END; 1870} {false} 1871do_execsql_test e_expr-37.3 { 1872 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END; 1873} {false} 1874do_execsql_test e_expr-37.4 { 1875 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END; 1876} {false} 1877do_execsql_test e_expr-37.5 { 1878 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END; 1879} {false} 1880 1881# EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are 1882# considered to be true. 1883# 1884do_execsql_test e_expr-37.6 { 1885 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END; 1886} {true} 1887do_execsql_test e_expr-37.7 { 1888 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END; 1889} {true} 1890do_execsql_test e_expr-37.8 { 1891 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END; 1892} {true} 1893do_execsql_test e_expr-37.9 { 1894 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END; 1895} {true} 1896do_execsql_test e_expr-37.10 { 1897 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END; 1898} {true} 1899 1900 1901finish_test 1902