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