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