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