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