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