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