000001 # 2010 July 16 000002 # 000003 # The author disclaims copyright to this source code. In place of 000004 # a legal notice, here is a blessing: 000005 # 000006 # May you do good and not evil. 000007 # May you find forgiveness for yourself and forgive others. 000008 # May you share freely, never taking more than you give. 000009 # 000010 #*********************************************************************** 000011 # 000012 # This file implements tests to verify that the "testable statements" in 000013 # the lang_expr.html document are correct. 000014 # 000015 000016 set testdir [file dirname $argv0] 000017 source $testdir/tester.tcl 000018 source $testdir/malloc_common.tcl 000019 000020 ifcapable !compound { 000021 finish_test 000022 return 000023 } 000024 000025 proc do_expr_test {tn expr type value} { 000026 uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [ 000027 list [list $type $value] 000028 ] 000029 } 000030 000031 proc do_qexpr_test {tn expr value} { 000032 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value] 000033 } 000034 000035 # Set up three global variables: 000036 # 000037 # ::opname An array mapping from SQL operator to an easy to parse 000038 # name. The names are used as part of test case names. 000039 # 000040 # ::opprec An array mapping from SQL operator to a numeric 000041 # precedence value. Operators that group more tightly 000042 # have lower numeric precedences. 000043 # 000044 # ::oplist A list of all SQL operators supported by SQLite. 000045 # 000046 foreach {op opn} { 000047 || cat * mul / div % mod + add 000048 - sub << lshift >> rshift & bitand | bitor 000049 < less <= lesseq > more >= moreeq = eq1 000050 == eq2 <> ne1 != ne2 IS is LIKE like 000051 GLOB glob AND and OR or MATCH match REGEXP regexp 000052 {IS NOT} isnt 000053 } { 000054 set ::opname($op) $opn 000055 } 000056 set oplist [list] 000057 foreach {prec opl} { 000058 1 || 000059 2 {* / %} 000060 3 {+ -} 000061 4 {<< >> & |} 000062 5 {< <= > >=} 000063 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} 000064 7 AND 000065 8 OR 000066 } { 000067 foreach op $opl { 000068 set ::opprec($op) $prec 000069 lappend oplist $op 000070 } 000071 } 000072 000073 000074 # Hook in definitions of MATCH and REGEX. The following implementations 000075 # cause MATCH and REGEX to behave similarly to the == operator. 000076 # 000077 proc matchfunc {a b} { return [expr {$a==$b}] } 000078 proc regexfunc {a b} { return [expr {$a==$b}] } 000079 db func match -argcount 2 matchfunc 000080 db func regexp -argcount 2 regexfunc 000081 000082 #------------------------------------------------------------------------- 000083 # Test cases e_expr-1.* attempt to verify that all binary operators listed 000084 # in the documentation exist and that the relative precedences of the 000085 # operators are also as the documentation suggests. 000086 # 000087 # EVIDENCE-OF: R-15514-65163 SQLite understands the following binary 000088 # operators, in order from highest to lowest precedence: || * / % + - 000089 # << >> & | < <= > >= = == != <> IS IS 000090 # NOT IN LIKE GLOB MATCH REGEXP AND OR 000091 # 000092 # EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same 000093 # precedence as =. 000094 # 000095 000096 unset -nocomplain untested 000097 foreach op1 $oplist { 000098 foreach op2 $oplist { 000099 set untested($op1,$op2) 1 000100 foreach {tn A B C} { 000101 1 22 45 66 000102 2 0 0 0 000103 3 0 0 1 000104 4 0 1 0 000105 5 0 1 1 000106 6 1 0 0 000107 7 1 0 1 000108 8 1 1 0 000109 9 1 1 1 000110 10 5 6 1 000111 11 1 5 6 000112 12 1 5 5 000113 13 5 5 1 000114 000115 14 5 2 1 000116 15 1 4 1 000117 16 -1 0 1 000118 17 0 1 -1 000119 000120 } { 000121 set testname "e_expr-1.$opname($op1).$opname($op2).$tn" 000122 000123 # If $op2 groups more tightly than $op1, then the result 000124 # of executing $sql1 whould be the same as executing $sql3. 000125 # If $op1 groups more tightly, or if $op1 and $op2 have 000126 # the same precedence, then executing $sql1 should return 000127 # the same value as $sql2. 000128 # 000129 set sql1 "SELECT $A $op1 $B $op2 $C" 000130 set sql2 "SELECT ($A $op1 $B) $op2 $C" 000131 set sql3 "SELECT $A $op1 ($B $op2 $C)" 000132 000133 set a2 [db one $sql2] 000134 set a3 [db one $sql3] 000135 000136 do_execsql_test $testname $sql1 [list [ 000137 if {$opprec($op2) < $opprec($op1)} {set a3} {set a2} 000138 ]] 000139 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) } 000140 } 000141 } 000142 } 000143 000144 foreach op {* AND OR + || & |} { unset untested($op,$op) } 000145 unset untested(+,-) ;# Since (a+b)-c == a+(b-c) 000146 unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c) 000147 000148 do_test e_expr-1.1 { array names untested } {} 000149 000150 # At one point, test 1.2.2 was failing. Instead of the correct result, it 000151 # was returning {1 1 0}. This would seem to indicate that LIKE has the 000152 # same precedence as '<'. Which is incorrect. It has lower precedence. 000153 # 000154 do_execsql_test e_expr-1.2.1 { 000155 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1) 000156 } {1 1 0} 000157 do_execsql_test e_expr-1.2.2 { 000158 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2) 000159 } {0 1 0} 000160 000161 # Showing that LIKE and == have the same precedence 000162 # 000163 do_execsql_test e_expr-1.2.3 { 000164 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1) 000165 } {1 1 0} 000166 do_execsql_test e_expr-1.2.4 { 000167 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1) 000168 } {1 1 0} 000169 000170 # Showing that < groups more tightly than == (< has higher precedence). 000171 # 000172 do_execsql_test e_expr-1.2.5 { 000173 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1) 000174 } {1 1 0} 000175 do_execsql_test e_expr-1.6 { 000176 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2) 000177 } {0 1 0} 000178 000179 #------------------------------------------------------------------------- 000180 # Check that the four unary prefix operators mentioned in the 000181 # documentation exist. 000182 # 000183 # EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these: 000184 # - + ~ NOT 000185 # 000186 do_execsql_test e_expr-2.1 { SELECT - 10 } {-10} 000187 do_execsql_test e_expr-2.2 { SELECT + 10 } {10} 000188 do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11} 000189 do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0} 000190 000191 #------------------------------------------------------------------------- 000192 # Tests for the two statements made regarding the unary + operator. 000193 # 000194 # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op. 000195 # 000196 # EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers, 000197 # blobs or NULL and it always returns a result with the same value as 000198 # the operand. 000199 # 000200 foreach {tn literal type} { 000201 1 'helloworld' text 000202 2 45 integer 000203 3 45.2 real 000204 4 45.0 real 000205 5 X'ABCDEF' blob 000206 6 NULL null 000207 } { 000208 set sql " SELECT quote( + $literal ), typeof( + $literal) " 000209 do_execsql_test e_expr-3.$tn $sql [list $literal $type] 000210 } 000211 000212 #------------------------------------------------------------------------- 000213 # Check that both = and == are both acceptable as the "equals" operator. 000214 # Similarly, either != or <> work as the not-equals operator. 000215 # 000216 # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==. 000217 # 000218 # EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or 000219 # <>. 000220 # 000221 foreach {tn literal different} { 000222 1 'helloworld' '12345' 000223 2 22 23 000224 3 'xyz' X'78797A' 000225 4 X'78797A00' 'xyz' 000226 } { 000227 do_execsql_test e_expr-4.$tn " 000228 SELECT $literal = $literal, $literal == $literal, 000229 $literal = $different, $literal == $different, 000230 $literal = NULL, $literal == NULL, 000231 $literal != $literal, $literal <> $literal, 000232 $literal != $different, $literal <> $different, 000233 $literal != NULL, $literal != NULL 000234 000235 " {1 1 0 0 {} {} 0 0 1 1 {} {}} 000236 } 000237 000238 #------------------------------------------------------------------------- 000239 # Test the || operator. 000240 # 000241 # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins 000242 # together the two strings of its operands. 000243 # 000244 foreach {tn a b} { 000245 1 'helloworld' '12345' 000246 2 22 23 000247 } { 000248 set as [db one "SELECT $a"] 000249 set bs [db one "SELECT $b"] 000250 000251 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"] 000252 } 000253 000254 #------------------------------------------------------------------------- 000255 # Test the % operator. 000256 # 000257 # EVIDENCE-OF: R-04223-04352 The operator % outputs the integer value of 000258 # its left operand modulo its right operand. 000259 # 000260 do_execsql_test e_expr-6.1 {SELECT 72%5} {2} 000261 do_execsql_test e_expr-6.2 {SELECT 72%-5} {2} 000262 do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2} 000263 do_execsql_test e_expr-6.4 {SELECT -72%5} {-2} 000264 do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0} 000265 000266 #------------------------------------------------------------------------- 000267 # Test that the results of all binary operators are either numeric or 000268 # NULL, except for the || operator, which may evaluate to either a text 000269 # value or NULL. 000270 # 000271 # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either 000272 # a numeric value or NULL, except for the || concatenation operator 000273 # which always evaluates to either NULL or a text value. 000274 # 000275 set literals { 000276 1 'abc' 2 'hexadecimal' 3 '' 000277 4 123 5 -123 6 0 000278 7 123.4 8 0.0 9 -123.4 000279 10 X'ABCDEF' 11 X'' 12 X'0000' 000280 13 NULL 000281 } 000282 foreach op $oplist { 000283 foreach {n1 rhs} $literals { 000284 foreach {n2 lhs} $literals { 000285 000286 set t [db one " SELECT typeof($lhs $op $rhs) "] 000287 do_test e_expr-7.$opname($op).$n1.$n2 { 000288 expr { 000289 ($op=="||" && ($t == "text" || $t == "null")) 000290 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null")) 000291 } 000292 } 1 000293 000294 }} 000295 } 000296 000297 #------------------------------------------------------------------------- 000298 # Test the IS and IS NOT operators. 000299 # 000300 # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and 000301 # != except when one or both of the operands are NULL. 000302 # 000303 # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL, 000304 # then the IS operator evaluates to 1 (true) and the IS NOT operator 000305 # evaluates to 0 (false). 000306 # 000307 # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is 000308 # not, then the IS operator evaluates to 0 (false) and the IS NOT 000309 # operator is 1 (true). 000310 # 000311 # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT 000312 # expression to evaluate to NULL. 000313 # 000314 do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1} 000315 do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0} 000316 do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0} 000317 do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1} 000318 do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}} 000319 do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}} 000320 do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}} 000321 do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1} 000322 do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0} 000323 do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1} 000324 do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1} 000325 do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0} 000326 do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}} 000327 do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}} 000328 do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}} 000329 do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0} 000330 000331 foreach {n1 rhs} $literals { 000332 foreach {n2 lhs} $literals { 000333 if {$rhs!="NULL" && $lhs!="NULL"} { 000334 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"] 000335 } else { 000336 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \ 000337 [expr {$lhs!="NULL" || $rhs!="NULL"}] 000338 ] 000339 } 000340 set test e_expr-8.2.$n1.$n2 000341 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq 000342 do_execsql_test $test.2 " 000343 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL 000344 " {0 0} 000345 } 000346 } 000347 000348 #------------------------------------------------------------------------- 000349 # Run some tests on the COLLATE "unary postfix operator". 000350 # 000351 # This collation sequence reverses both arguments before using 000352 # [string compare] to compare them. For example, when comparing the 000353 # strings 'one' and 'four', return the result of: 000354 # 000355 # string compare eno ruof 000356 # 000357 proc reverse_str {zStr} { 000358 set out "" 000359 foreach c [split $zStr {}] { set out "${c}${out}" } 000360 set out 000361 } 000362 proc reverse_collate {zLeft zRight} { 000363 string compare [reverse_str $zLeft] [reverse_str $zRight] 000364 } 000365 db collate reverse reverse_collate 000366 000367 # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix 000368 # operator that assigns a collating sequence to an expression. 000369 # 000370 # EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher 000371 # precedence (binds more tightly) than any binary operator and any unary 000372 # prefix operator except "~". 000373 # 000374 do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0 000375 do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1 000376 do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0 000377 do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1 000378 000379 do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1 000380 do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0 000381 do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1 000382 do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0 000383 000384 do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1 000385 do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0 000386 do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1 000387 do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0 000388 do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1 000389 do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0 000390 000391 do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0 000392 do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1 000393 do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0 000394 do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1 000395 do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0 000396 do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1 000397 000398 do_execsql_test e_expr-9.22 { 000399 SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase 000400 } 1 000401 do_execsql_test e_expr-9.23 { 000402 SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase 000403 } 0 000404 000405 # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE 000406 # operator overrides the collating sequence determined by the COLLATE 000407 # clause in a table column definition. 000408 # 000409 do_execsql_test e_expr-9.24 { 000410 CREATE TABLE t24(a COLLATE NOCASE, b); 000411 INSERT INTO t24 VALUES('aaa', 1); 000412 INSERT INTO t24 VALUES('bbb', 2); 000413 INSERT INTO t24 VALUES('ccc', 3); 000414 } {} 000415 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0} 000416 do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0} 000417 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0} 000418 do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0} 000419 000420 #------------------------------------------------------------------------- 000421 # Test statements related to literal values. 000422 # 000423 # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating 000424 # point numbers, strings, BLOBs, or NULLs. 000425 # 000426 do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer} 000427 do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real} 000428 do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text} 000429 do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob} 000430 do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null} 000431 000432 # "Scientific notation is supported for point literal values." 000433 # 000434 do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real} 000435 do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real} 000436 do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034} 000437 do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0} 000438 000439 # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing 000440 # the string in single quotes ('). 000441 # 000442 # EVIDENCE-OF: R-07100-06606 A single quote within the string can be 000443 # encoded by putting two single quotes in a row - as in Pascal. 000444 # 000445 do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}} 000446 do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text} 000447 do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't} 000448 do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text} 000449 000450 # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals 000451 # containing hexadecimal data and preceded by a single "x" or "X" 000452 # character. 000453 # 000454 # EVIDENCE-OF: R-19836-11244 Example: X'53514C697465' 000455 # 000456 do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob 000457 do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob 000458 do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob 000459 do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob 000460 do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob 000461 000462 # EVIDENCE-OF: R-23914-51476 A literal value can also be the token 000463 # "NULL". 000464 # 000465 do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}} 000466 do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null} 000467 000468 #------------------------------------------------------------------------- 000469 # Test statements related to bound parameters 000470 # 000471 000472 proc parameter_test {tn sql params result} { 000473 set stmt [sqlite3_prepare_v2 db $sql -1] 000474 000475 foreach {number name} $params { 000476 set nm [sqlite3_bind_parameter_name $stmt $number] 000477 do_test $tn.name.$number [list set {} $nm] $name 000478 sqlite3_bind_int $stmt $number [expr -1 * $number] 000479 } 000480 000481 sqlite3_step $stmt 000482 000483 set res [list] 000484 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} { 000485 lappend res [sqlite3_column_text $stmt $i] 000486 } 000487 000488 set rc [sqlite3_finalize $stmt] 000489 do_test $tn.rc [list set {} $rc] SQLITE_OK 000490 do_test $tn.res [list set {} $res] $result 000491 } 000492 000493 # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN 000494 # holds a spot for the NNN-th parameter. NNN must be between 1 and 000495 # SQLITE_MAX_VARIABLE_NUMBER. 000496 # 000497 set mvn $SQLITE_MAX_VARIABLE_NUMBER 000498 parameter_test e_expr-11.1 " 000499 SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4 000500 " "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4" 000501 000502 set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER" 000503 foreach {tn param_number} [list \ 000504 2 0 \ 000505 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \ 000506 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \ 000507 5 12345678903456789034567890234567890 \ 000508 6 2147483648 \ 000509 7 2147483649 \ 000510 8 4294967296 \ 000511 9 4294967297 \ 000512 10 9223372036854775808 \ 000513 11 9223372036854775809 \ 000514 12 18446744073709551616 \ 000515 13 18446744073709551617 \ 000516 ] { 000517 do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg] 000518 } 000519 000520 # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a 000521 # number creates a parameter with a number one greater than the largest 000522 # parameter number already assigned. 000523 # 000524 # EVIDENCE-OF: R-42938-07030 If this means the parameter number is 000525 # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error. 000526 # 000527 parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1 000528 parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2} 000529 parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6} 000530 parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5} 000531 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" { 000532 1 {} 456 ?456 457 {} 000533 } {-1 -456 -457} 000534 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" { 000535 1 {} 456 ?456 4 ?4 457 {} 000536 } {-1 -456 -4 -457} 000537 foreach {tn sql} [list \ 000538 1 "SELECT ?$mvn, ?" \ 000539 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \ 000540 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \ 000541 ] { 000542 do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}] 000543 } 000544 000545 # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name 000546 # holds a spot for a named parameter with the name :AAAA. 000547 # 000548 # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters, 000549 # and any UTF characters with codepoints larger than 127 (non-ASCII 000550 # characters). 000551 # 000552 parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1 000553 parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1 000554 parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1 000555 parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1 000556 parameter_test e_expr-11.2.5 " 000557 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 000558 " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 000559 parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1 000560 000561 # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon, 000562 # except that the name of the parameter created is @AAAA. 000563 # 000564 parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1 000565 parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1 000566 parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1 000567 parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1 000568 parameter_test e_expr-11.3.5 " 000569 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 000570 " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 000571 parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1 000572 000573 # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier 000574 # name also holds a spot for a named parameter with the name $AAAA. 000575 # 000576 # EVIDENCE-OF: R-55025-21042 The identifier name in this case can 000577 # include one or more occurrences of "::" and a suffix enclosed in 000578 # "(...)" containing any text at all. 000579 # 000580 # Note: Looks like an identifier cannot consist entirely of "::" 000581 # characters or just a suffix. Also, the other named variable characters 000582 # (: and @) work the same way internally. Why not just document it that way? 000583 # 000584 parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1 000585 parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1 000586 parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1 000587 parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1 000588 parameter_test e_expr-11.4.5 " 000589 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 000590 " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 000591 parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1 000592 000593 parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1 000594 parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1 000595 parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1 000596 000597 # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The 000598 # number assigned is one greater than the largest parameter number 000599 # already assigned. 000600 # 000601 # EVIDENCE-OF: R-42620-22184 If this means the parameter would be 000602 # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an 000603 # error. 000604 # 000605 parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2} 000606 parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124} 000607 parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} { 000608 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c 000609 } {-1 -8 -9 -10 -2 -11} 000610 foreach {tn sql} [list \ 000611 1 "SELECT ?$mvn, \$::a" \ 000612 2 "SELECT ?$mvn, ?4, @a1" \ 000613 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \ 000614 ] { 000615 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}] 000616 } 000617 000618 # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values 000619 # using sqlite3_bind() are treated as NULL. 000620 # 000621 do_test e_expr-11.7.1 { 000622 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1] 000623 sqlite3_step $stmt 000624 000625 list [sqlite3_column_type $stmt 0] \ 000626 [sqlite3_column_type $stmt 1] \ 000627 [sqlite3_column_type $stmt 2] \ 000628 [sqlite3_column_type $stmt 3] 000629 } {NULL NULL NULL NULL} 000630 do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK 000631 000632 #------------------------------------------------------------------------- 000633 # "Test" the syntax diagrams in lang_expr.html. 000634 # 000635 # -- syntax diagram signed-number 000636 # 000637 do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0} 000638 do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1} 000639 do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2} 000640 do_execsql_test e_expr-12.1.4 { 000641 SELECT 1.4, +1.4, -1.4 000642 } {1.4 1.4 -1.4} 000643 do_execsql_test e_expr-12.1.5 { 000644 SELECT 1.5e+5, +1.5e+5, -1.5e+5 000645 } {150000.0 150000.0 -150000.0} 000646 do_execsql_test e_expr-12.1.6 { 000647 SELECT 0.0001, +0.0001, -0.0001 000648 } {0.0001 0.0001 -0.0001} 000649 000650 # -- syntax diagram literal-value 000651 # 000652 set sqlite_current_time 1 000653 do_execsql_test e_expr-12.2.1 {SELECT 123} {123} 000654 do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0} 000655 do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde} 000656 do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC} 000657 do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}} 000658 do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01} 000659 do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01} 000660 do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}} 000661 set sqlite_current_time 0 000662 000663 # -- syntax diagram expr 000664 # 000665 forcedelete test.db2 000666 execsql { 000667 ATTACH 'test.db2' AS dbname; 000668 CREATE TABLE dbname.tblname(cname); 000669 } 000670 000671 proc glob {args} {return 1} 000672 db function glob glob 000673 db function match glob 000674 db function regexp glob 000675 000676 foreach {tn expr} { 000677 1 123 000678 2 123.4e05 000679 3 'abcde' 000680 4 X'414243' 000681 5 NULL 000682 6 CURRENT_TIME 000683 7 CURRENT_DATE 000684 8 CURRENT_TIMESTAMP 000685 000686 9 ? 000687 10 ?123 000688 11 @hello 000689 12 :world 000690 13 $tcl 000691 14 $tcl(array) 000692 000693 15 cname 000694 16 tblname.cname 000695 17 dbname.tblname.cname 000696 000697 18 "+ EXPR" 000698 19 "- EXPR" 000699 20 "NOT EXPR" 000700 21 "~ EXPR" 000701 000702 22 "EXPR1 || EXPR2" 000703 23 "EXPR1 * EXPR2" 000704 24 "EXPR1 / EXPR2" 000705 25 "EXPR1 % EXPR2" 000706 26 "EXPR1 + EXPR2" 000707 27 "EXPR1 - EXPR2" 000708 28 "EXPR1 << EXPR2" 000709 29 "EXPR1 >> EXPR2" 000710 30 "EXPR1 & EXPR2" 000711 31 "EXPR1 | EXPR2" 000712 32 "EXPR1 < EXPR2" 000713 33 "EXPR1 <= EXPR2" 000714 34 "EXPR1 > EXPR2" 000715 35 "EXPR1 >= EXPR2" 000716 36 "EXPR1 = EXPR2" 000717 37 "EXPR1 == EXPR2" 000718 38 "EXPR1 != EXPR2" 000719 39 "EXPR1 <> EXPR2" 000720 40 "EXPR1 IS EXPR2" 000721 41 "EXPR1 IS NOT EXPR2" 000722 42 "EXPR1 AND EXPR2" 000723 43 "EXPR1 OR EXPR2" 000724 000725 44 "count(*)" 000726 45 "count(DISTINCT EXPR)" 000727 46 "substr(EXPR, 10, 20)" 000728 47 "changes()" 000729 000730 48 "( EXPR )" 000731 000732 49 "CAST ( EXPR AS integer )" 000733 50 "CAST ( EXPR AS 'abcd' )" 000734 51 "CAST ( EXPR AS 'ab$ $cd' )" 000735 000736 52 "EXPR COLLATE nocase" 000737 53 "EXPR COLLATE binary" 000738 000739 54 "EXPR1 LIKE EXPR2" 000740 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR" 000741 56 "EXPR1 GLOB EXPR2" 000742 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR" 000743 58 "EXPR1 REGEXP EXPR2" 000744 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR" 000745 60 "EXPR1 MATCH EXPR2" 000746 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR" 000747 62 "EXPR1 NOT LIKE EXPR2" 000748 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR" 000749 64 "EXPR1 NOT GLOB EXPR2" 000750 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR" 000751 66 "EXPR1 NOT REGEXP EXPR2" 000752 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR" 000753 68 "EXPR1 NOT MATCH EXPR2" 000754 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR" 000755 000756 70 "EXPR ISNULL" 000757 71 "EXPR NOTNULL" 000758 72 "EXPR NOT NULL" 000759 000760 73 "EXPR1 IS EXPR2" 000761 74 "EXPR1 IS NOT EXPR2" 000762 000763 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2" 000764 76 "EXPR BETWEEN EXPR1 AND EXPR2" 000765 000766 77 "EXPR NOT IN (SELECT cname FROM tblname)" 000767 78 "EXPR NOT IN (1)" 000768 79 "EXPR NOT IN (1, 2, 3)" 000769 80 "EXPR NOT IN tblname" 000770 81 "EXPR NOT IN dbname.tblname" 000771 82 "EXPR IN (SELECT cname FROM tblname)" 000772 83 "EXPR IN (1)" 000773 84 "EXPR IN (1, 2, 3)" 000774 85 "EXPR IN tblname" 000775 86 "EXPR IN dbname.tblname" 000776 000777 87 "EXISTS (SELECT cname FROM tblname)" 000778 88 "NOT EXISTS (SELECT cname FROM tblname)" 000779 000780 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END" 000781 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END" 000782 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" 000783 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" 000784 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END" 000785 94 "CASE WHEN EXPR1 THEN EXPR2 END" 000786 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END" 000787 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END" 000788 } { 000789 000790 # If the expression string being parsed contains "EXPR2", then replace 000791 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it 000792 # contains "EXPR", then replace EXPR with an arbitrary SQL expression. 000793 # 000794 set elist [list $expr] 000795 if {[string match *EXPR2* $expr]} { 000796 set elist [list] 000797 foreach {e1 e2} { cname "34+22" } { 000798 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr] 000799 } 000800 } 000801 if {[string match *EXPR* $expr]} { 000802 set elist2 [list] 000803 foreach el $elist { 000804 foreach e { cname "34+22" } { 000805 lappend elist2 [string map [list EXPR $e] $el] 000806 } 000807 } 000808 set elist $elist2 000809 } 000810 000811 set x 0 000812 foreach e $elist { 000813 incr x 000814 do_test e_expr-12.3.$tn.$x { 000815 set rc [catch { execsql "SELECT $e FROM tblname" } msg] 000816 } {0} 000817 } 000818 } 000819 000820 # -- syntax diagram raise-function 000821 # 000822 foreach {tn raiseexpr} { 000823 1 "RAISE(IGNORE)" 000824 2 "RAISE(ROLLBACK, 'error message')" 000825 3 "RAISE(ABORT, 'error message')" 000826 4 "RAISE(FAIL, 'error message')" 000827 } { 000828 do_execsql_test e_expr-12.4.$tn " 000829 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN 000830 SELECT $raiseexpr ; 000831 END; 000832 " {} 000833 } 000834 000835 #------------------------------------------------------------------------- 000836 # Test the statements related to the BETWEEN operator. 000837 # 000838 # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically 000839 # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent 000840 # to "x>=y AND x<=z" except that with BETWEEN, the x expression is 000841 # only evaluated once. 000842 # 000843 db func x x 000844 proc x {} { incr ::xcount ; return [expr $::x] } 000845 foreach {tn x expr res nEval} { 000846 1 10 "x() >= 5 AND x() <= 15" 1 2 000847 2 10 "x() BETWEEN 5 AND 15" 1 1 000848 000849 3 5 "x() >= 5 AND x() <= 5" 1 2 000850 4 5 "x() BETWEEN 5 AND 5" 1 1 000851 000852 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2 000853 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1 000854 } { 000855 do_test e_expr-13.1.$tn { 000856 set ::xcount 0 000857 set a [execsql "SELECT $expr"] 000858 list $::xcount $a 000859 } [list $nEval $res] 000860 } 000861 000862 # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is 000863 # the same as the precedence as operators == and != and LIKE and groups 000864 # left to right. 000865 # 000866 # Therefore, BETWEEN groups more tightly than operator "AND", but less 000867 # so than "<". 000868 # 000869 do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1 000870 do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1 000871 do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0 000872 do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1 000873 do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1 000874 do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0 000875 000876 do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1 000877 do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1 000878 do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0 000879 do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1 000880 do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1 000881 do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0 000882 000883 do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1 000884 do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1 000885 do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0 000886 do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1 000887 do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1 000888 do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0 000889 000890 do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0 000891 do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0 000892 do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1 000893 do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0 000894 do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0 000895 do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1 000896 000897 do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1 000898 do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1 000899 do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0 000900 do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0 000901 do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0 000902 do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1 000903 000904 #------------------------------------------------------------------------- 000905 # Test the statements related to the LIKE and GLOB operators. 000906 # 000907 # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching 000908 # comparison. 000909 # 000910 # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE 000911 # operator contains the pattern and the left hand operand contains the 000912 # string to match against the pattern. 000913 # 000914 do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0 000915 do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1 000916 000917 # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern 000918 # matches any sequence of zero or more characters in the string. 000919 # 000920 do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1 000921 do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1 000922 do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1 000923 000924 # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern 000925 # matches any single character in the string. 000926 # 000927 do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0 000928 do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1 000929 do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0 000930 000931 # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its 000932 # lower/upper case equivalent (i.e. case-insensitive matching). 000933 # 000934 do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1 000935 do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1 000936 do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0 000937 000938 # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case 000939 # for ASCII characters by default. 000940 # 000941 # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by 000942 # default for unicode characters that are beyond the ASCII range. 000943 # 000944 # EVIDENCE-OF: R-44381-11669 the expression 000945 # 'a' LIKE 'A' is TRUE but 000946 # 'æ' LIKE 'Æ' is FALSE. 000947 # 000948 # The restriction to ASCII characters does not apply if the ICU 000949 # library is compiled in. When ICU is enabled SQLite does not act 000950 # as it does "by default". 000951 # 000952 do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1 000953 ifcapable !icu { 000954 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0 000955 } 000956 000957 # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present, 000958 # then the expression following the ESCAPE keyword must evaluate to a 000959 # string consisting of a single character. 000960 # 000961 do_catchsql_test e_expr-14.6.1 { 000962 SELECT 'A' LIKE 'a' ESCAPE '12' 000963 } {1 {ESCAPE expression must be a single character}} 000964 do_catchsql_test e_expr-14.6.2 { 000965 SELECT 'A' LIKE 'a' ESCAPE '' 000966 } {1 {ESCAPE expression must be a single character}} 000967 do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1} 000968 do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1} 000969 000970 # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE 000971 # pattern to include literal percent or underscore characters. 000972 # 000973 # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent 000974 # symbol (%), underscore (_), or a second instance of the escape 000975 # character itself matches a literal percent symbol, underscore, or a 000976 # single escape character, respectively. 000977 # 000978 do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1 000979 do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0 000980 do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0 000981 do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0 000982 do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0 000983 000984 do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1 000985 do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0 000986 do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0 000987 do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0 000988 do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0 000989 000990 do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1 000991 do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0 000992 do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0 000993 do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0 000994 000995 # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by 000996 # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z). 000997 # 000998 proc likefunc {args} { 000999 eval lappend ::likeargs $args 001000 return 1 001001 } 001002 db func like -argcount 2 likefunc 001003 db func like -argcount 3 likefunc 001004 set ::likeargs [list] 001005 do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1 001006 do_test e_expr-15.1.2 { set likeargs } {def abc} 001007 set ::likeargs [list] 001008 do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1 001009 do_test e_expr-15.1.4 { set likeargs } {def abc X} 001010 db close 001011 sqlite3 db test.db 001012 001013 # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case 001014 # sensitive using the case_sensitive_like pragma. 001015 # 001016 do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1 001017 do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 001018 do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {} 001019 do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0 001020 do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0 001021 do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 001022 do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 001023 do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {} 001024 do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1 001025 do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 001026 do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 001027 do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 001028 001029 # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but 001030 # uses the Unix file globbing syntax for its wildcards. 001031 # 001032 # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE. 001033 # 001034 do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0 001035 do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1 001036 do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0 001037 do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1 001038 001039 do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1 001040 do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0 001041 do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0 001042 001043 # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the 001044 # NOT keyword to invert the sense of the test. 001045 # 001046 do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1 001047 do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0 001048 do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0 001049 do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0 001050 do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1 001051 001052 db nullvalue null 001053 do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null 001054 do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null 001055 do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null 001056 do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null 001057 db nullvalue {} 001058 001059 # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by 001060 # calling the function glob(Y,X) and can be modified by overriding that 001061 # function. 001062 proc globfunc {args} { 001063 eval lappend ::globargs $args 001064 return 1 001065 } 001066 db func glob -argcount 2 globfunc 001067 set ::globargs [list] 001068 do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1 001069 do_test e_expr-17.3.2 { set globargs } {def abc} 001070 set ::globargs [list] 001071 do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0 001072 do_test e_expr-17.3.4 { set globargs } {Y X} 001073 sqlite3 db test.db 001074 001075 # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by 001076 # default and so use of the REGEXP operator will normally result in an 001077 # error message. 001078 # 001079 # There is a regexp function if ICU is enabled though. 001080 # 001081 ifcapable !icu { 001082 do_catchsql_test e_expr-18.1.1 { 001083 SELECT regexp('abc', 'def') 001084 } {1 {no such function: regexp}} 001085 do_catchsql_test e_expr-18.1.2 { 001086 SELECT 'abc' REGEXP 'def' 001087 } {1 {no such function: REGEXP}} 001088 } 001089 001090 # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for 001091 # the regexp() user function. 001092 # 001093 # EVIDENCE-OF: R-65524-61849 If an application-defined SQL function 001094 # named "regexp" is added at run-time, then the "X REGEXP Y" operator 001095 # will be implemented as a call to "regexp(Y,X)". 001096 # 001097 proc regexpfunc {args} { 001098 eval lappend ::regexpargs $args 001099 return 1 001100 } 001101 db func regexp -argcount 2 regexpfunc 001102 set ::regexpargs [list] 001103 do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1 001104 do_test e_expr-18.2.2 { set regexpargs } {def abc} 001105 set ::regexpargs [list] 001106 do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 001107 do_test e_expr-18.2.4 { set regexpargs } {Y X} 001108 sqlite3 db test.db 001109 001110 # EVIDENCE-OF: R-42037-37826 The default match() function implementation 001111 # raises an exception and is not really useful for anything. 001112 # 001113 do_catchsql_test e_expr-19.1.1 { 001114 SELECT 'abc' MATCH 'def' 001115 } {1 {unable to use function MATCH in the requested context}} 001116 do_catchsql_test e_expr-19.1.2 { 001117 SELECT match('abc', 'def') 001118 } {1 {unable to use function MATCH in the requested context}} 001119 001120 # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for 001121 # the match() application-defined function. 001122 # 001123 # EVIDENCE-OF: R-06021-09373 But extensions can override the match() 001124 # function with more helpful logic. 001125 # 001126 proc matchfunc {args} { 001127 eval lappend ::matchargs $args 001128 return 1 001129 } 001130 db func match -argcount 2 matchfunc 001131 set ::matchargs [list] 001132 do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 001133 do_test e_expr-19.2.2 { set matchargs } {def abc} 001134 set ::matchargs [list] 001135 do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 001136 do_test e_expr-19.2.4 { set matchargs } {Y X} 001137 sqlite3 db test.db 001138 001139 #------------------------------------------------------------------------- 001140 # Test cases for the testable statements related to the CASE expression. 001141 # 001142 # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE 001143 # expression: those with a base expression and those without. 001144 # 001145 do_execsql_test e_expr-20.1 { 001146 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 001147 } {true} 001148 do_execsql_test e_expr-20.2 { 001149 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; 001150 } {false} 001151 001152 proc var {nm} { 001153 lappend ::varlist $nm 001154 return [set "::$nm"] 001155 } 001156 db func var var 001157 001158 # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each 001159 # WHEN expression is evaluated and the result treated as a boolean, 001160 # starting with the leftmost and continuing to the right. 001161 # 001162 foreach {a b c} {0 0 0} break 001163 set varlist [list] 001164 do_execsql_test e_expr-21.1.1 { 001165 SELECT CASE WHEN var('a') THEN 'A' 001166 WHEN var('b') THEN 'B' 001167 WHEN var('c') THEN 'C' END 001168 } {{}} 001169 do_test e_expr-21.1.2 { set varlist } {a b c} 001170 set varlist [list] 001171 do_execsql_test e_expr-21.1.3 { 001172 SELECT CASE WHEN var('c') THEN 'C' 001173 WHEN var('b') THEN 'B' 001174 WHEN var('a') THEN 'A' 001175 ELSE 'no result' 001176 END 001177 } {{no result}} 001178 do_test e_expr-21.1.4 { set varlist } {c b a} 001179 001180 # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the 001181 # evaluation of the THEN expression that corresponds to the first WHEN 001182 # expression that evaluates to true. 001183 # 001184 foreach {a b c} {0 1 0} break 001185 do_execsql_test e_expr-21.2.1 { 001186 SELECT CASE WHEN var('a') THEN 'A' 001187 WHEN var('b') THEN 'B' 001188 WHEN var('c') THEN 'C' 001189 ELSE 'no result' 001190 END 001191 } {B} 001192 foreach {a b c} {0 1 1} break 001193 do_execsql_test e_expr-21.2.2 { 001194 SELECT CASE WHEN var('a') THEN 'A' 001195 WHEN var('b') THEN 'B' 001196 WHEN var('c') THEN 'C' 001197 ELSE 'no result' 001198 END 001199 } {B} 001200 foreach {a b c} {0 0 1} break 001201 do_execsql_test e_expr-21.2.3 { 001202 SELECT CASE WHEN var('a') THEN 'A' 001203 WHEN var('b') THEN 'B' 001204 WHEN var('c') THEN 'C' 001205 ELSE 'no result' 001206 END 001207 } {C} 001208 001209 # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions 001210 # evaluate to true, the result of evaluating the ELSE expression, if 001211 # any. 001212 # 001213 foreach {a b c} {0 0 0} break 001214 do_execsql_test e_expr-21.3.1 { 001215 SELECT CASE WHEN var('a') THEN 'A' 001216 WHEN var('b') THEN 'B' 001217 WHEN var('c') THEN 'C' 001218 ELSE 'no result' 001219 END 001220 } {{no result}} 001221 001222 # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of 001223 # the WHEN expressions are true, then the overall result is NULL. 001224 # 001225 db nullvalue null 001226 do_execsql_test e_expr-21.3.2 { 001227 SELECT CASE WHEN var('a') THEN 'A' 001228 WHEN var('b') THEN 'B' 001229 WHEN var('c') THEN 'C' 001230 END 001231 } {null} 001232 db nullvalue {} 001233 001234 # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when 001235 # evaluating WHEN terms. 001236 # 001237 do_execsql_test e_expr-21.4.1 { 001238 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END 001239 } {B} 001240 do_execsql_test e_expr-21.4.2 { 001241 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END 001242 } {C} 001243 001244 # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base 001245 # expression is evaluated just once and the result is compared against 001246 # the evaluation of each WHEN expression from left to right. 001247 # 001248 # Note: This test case tests the "evaluated just once" part of the above 001249 # statement. Tests associated with the next two statements test that the 001250 # comparisons take place. 001251 # 001252 foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break 001253 set ::varlist [list] 001254 do_execsql_test e_expr-22.1.1 { 001255 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END 001256 } {C} 001257 do_test e_expr-22.1.2 { set ::varlist } {a} 001258 001259 # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the 001260 # evaluation of the THEN expression that corresponds to the first WHEN 001261 # expression for which the comparison is true. 001262 # 001263 do_execsql_test e_expr-22.2.1 { 001264 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 001265 } {B} 001266 do_execsql_test e_expr-22.2.2 { 001267 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 001268 } {A} 001269 001270 # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions 001271 # evaluate to a value equal to the base expression, the result of 001272 # evaluating the ELSE expression, if any. 001273 # 001274 do_execsql_test e_expr-22.3.1 { 001275 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END 001276 } {D} 001277 001278 # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of 001279 # the WHEN expressions produce a result equal to the base expression, 001280 # the overall result is NULL. 001281 # 001282 do_execsql_test e_expr-22.4.1 { 001283 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 001284 } {{}} 001285 db nullvalue null 001286 do_execsql_test e_expr-22.4.2 { 001287 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END 001288 } {null} 001289 db nullvalue {} 001290 001291 # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a 001292 # WHEN expression, the same collating sequence, affinity, and 001293 # NULL-handling rules apply as if the base expression and WHEN 001294 # expression are respectively the left- and right-hand operands of an = 001295 # operator. 001296 # 001297 proc rev {str} { 001298 set ret "" 001299 set chars [split $str] 001300 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} { 001301 append ret [lindex $chars $i] 001302 } 001303 set ret 001304 } 001305 proc reverse {lhs rhs} { 001306 string compare [rev $lhs] [rev $rhs] 001307 } 001308 db collate reverse reverse 001309 do_execsql_test e_expr-23.1.1 { 001310 CREATE TABLE t1( 001311 a TEXT COLLATE NOCASE, 001312 b COLLATE REVERSE, 001313 c INTEGER, 001314 d BLOB 001315 ); 001316 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5); 001317 } {} 001318 do_execsql_test e_expr-23.1.2 { 001319 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1 001320 } {B} 001321 do_execsql_test e_expr-23.1.3 { 001322 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1 001323 } {B} 001324 do_execsql_test e_expr-23.1.4 { 001325 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 001326 } {B} 001327 do_execsql_test e_expr-23.1.5 { 001328 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 001329 } {B} 001330 do_execsql_test e_expr-23.1.6 { 001331 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END 001332 } {B} 001333 do_execsql_test e_expr-23.1.7 { 001334 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 001335 } {A} 001336 do_execsql_test e_expr-23.1.8 { 001337 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1 001338 } {B} 001339 do_execsql_test e_expr-23.1.9 { 001340 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END 001341 } {B} 001342 001343 # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the 001344 # result of the CASE is always the result of evaluating the ELSE 001345 # expression if it exists, or NULL if it does not. 001346 # 001347 do_execsql_test e_expr-24.1.1 { 001348 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; 001349 } {{}} 001350 do_execsql_test e_expr-24.1.2 { 001351 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; 001352 } {C} 001353 001354 # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, 001355 # or short-circuit, evaluation. 001356 # 001357 set varlist [list] 001358 foreach {a b c} {0 1 0} break 001359 do_execsql_test e_expr-25.1.1 { 001360 SELECT CASE WHEN var('a') THEN 'A' 001361 WHEN var('b') THEN 'B' 001362 WHEN var('c') THEN 'C' 001363 END 001364 } {B} 001365 do_test e_expr-25.1.2 { set ::varlist } {a b} 001366 set varlist [list] 001367 do_execsql_test e_expr-25.1.3 { 001368 SELECT CASE '0' WHEN var('a') THEN 'A' 001369 WHEN var('b') THEN 'B' 001370 WHEN var('c') THEN 'C' 001371 END 001372 } {A} 001373 do_test e_expr-25.1.4 { set ::varlist } {a} 001374 001375 # EVIDENCE-OF: R-34773-62253 The only difference between the following 001376 # two CASE expressions is that the x expression is evaluated exactly 001377 # once in the first example but might be evaluated multiple times in the 001378 # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN 001379 # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END 001380 # 001381 proc ceval {x} { 001382 incr ::evalcount 001383 return $x 001384 } 001385 db func ceval ceval 001386 set ::evalcount 0 001387 001388 do_execsql_test e_expr-26.1.1 { 001389 CREATE TABLE t2(x, w1, r1, w2, r2, r3); 001390 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); 001391 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); 001392 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); 001393 } {} 001394 do_execsql_test e_expr-26.1.2 { 001395 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 001396 } {R1 R2 R3} 001397 do_execsql_test e_expr-26.1.3 { 001398 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 001399 } {R1 R2 R3} 001400 001401 do_execsql_test e_expr-26.1.4 { 001402 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 001403 } {R1 R2 R3} 001404 do_test e_expr-26.1.5 { set ::evalcount } {3} 001405 set ::evalcount 0 001406 do_execsql_test e_expr-26.1.6 { 001407 SELECT CASE 001408 WHEN ceval(x)=w1 THEN r1 001409 WHEN ceval(x)=w2 THEN r2 001410 ELSE r3 END 001411 FROM t2 001412 } {R1 R2 R3} 001413 do_test e_expr-26.1.6 { set ::evalcount } {5} 001414 001415 001416 #------------------------------------------------------------------------- 001417 # Test statements related to CAST expressions. 001418 # 001419 # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the 001420 # conversion that takes place when a column affinity is applied to a 001421 # value except that with the CAST operator the conversion always takes 001422 # place even if the conversion lossy and irreversible, whereas column 001423 # affinity only changes the data type of a value if the change is 001424 # lossless and reversible. 001425 # 001426 do_execsql_test e_expr-27.1.1 { 001427 CREATE TABLE t3(a TEXT, b REAL, c INTEGER); 001428 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5); 001429 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3; 001430 } {blob UVU text 1.23abc real 4.5} 001431 do_execsql_test e_expr-27.1.2 { 001432 SELECT 001433 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT), 001434 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL), 001435 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER) 001436 } {text UVU real 1.23 integer 4} 001437 001438 # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the 001439 # result of the CAST expression is also NULL. 001440 # 001441 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {} 001442 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {} 001443 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {} 001444 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {} 001445 001446 # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result 001447 # is determined by applying the rules for determining column affinity to 001448 # the type-name. 001449 # 001450 # The R-29283-15561 requirement above is demonstrated by all of the 001451 # subsequent e_expr-26 tests. 001452 # 001453 # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no 001454 # affinity causes the value to be converted into a BLOB. 001455 # 001456 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc 001457 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def 001458 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi 001459 001460 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting 001461 # the value to TEXT in the encoding of the database connection, then 001462 # interpreting the resulting byte sequence as a BLOB instead of as TEXT. 001463 # 001464 do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' 001465 do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' 001466 do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' 001467 rename db db2 001468 sqlite3 db :memory: 001469 ifcapable {utf16} { 001470 db eval { PRAGMA encoding = 'utf-16le' } 001471 do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' 001472 do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' 001473 do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' 001474 } 001475 db close 001476 sqlite3 db :memory: 001477 db eval { PRAGMA encoding = 'utf-16be' } 001478 ifcapable {utf16} { 001479 do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' 001480 do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' 001481 do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' 001482 } 001483 db close 001484 rename db2 db 001485 001486 # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence 001487 # of bytes that make up the BLOB is interpreted as text encoded using 001488 # the database encoding. 001489 # 001490 do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi 001491 do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g 001492 rename db db2 001493 sqlite3 db :memory: 001494 db eval { PRAGMA encoding = 'utf-16le' } 001495 ifcapable {utf16} { 001496 do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0 001497 do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi 001498 } 001499 db close 001500 rename db2 db 001501 001502 # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT 001503 # renders the value as if via sqlite3_snprintf() except that the 001504 # resulting TEXT uses the encoding of the database connection. 001505 # 001506 do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 001507 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 001508 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 001509 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 001510 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 001511 do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 001512 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 001513 do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 001514 001515 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the 001516 # value is first converted to TEXT. 001517 # 001518 do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 001519 do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0 001520 do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87 001521 do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001 001522 rename db db2 001523 sqlite3 db :memory: 001524 ifcapable {utf16} { 001525 db eval { PRAGMA encoding = 'utf-16le' } 001526 do_expr_test e_expr-29.1.5 { 001527 CAST (X'31002E0032003300' AS REAL) } real 1.23 001528 do_expr_test e_expr-29.1.6 { 001529 CAST (X'3200330030002E003000' AS REAL) } real 230.0 001530 do_expr_test e_expr-29.1.7 { 001531 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87 001532 do_expr_test e_expr-29.1.8 { 001533 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001 001534 } 001535 db close 001536 rename db2 db 001537 001538 # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the 001539 # longest possible prefix of the value that can be interpreted as a real 001540 # number is extracted from the TEXT value and the remainder ignored. 001541 # 001542 do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23 001543 do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45 001544 do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212 001545 do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0 001546 001547 # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are 001548 # ignored when converging from TEXT to REAL. 001549 # 001550 do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23 001551 do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45 001552 do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212 001553 do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0 001554 001555 # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be 001556 # interpreted as a real number, the result of the conversion is 0.0. 001557 # 001558 do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0 001559 do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0 001560 do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0 001561 001562 # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the 001563 # value is first converted to TEXT. 001564 # 001565 do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123 001566 do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678 001567 do_expr_test e_expr-30.1.3 { 001568 CAST(X'31303030303030' AS INTEGER) 001569 } integer 1000000 001570 do_expr_test e_expr-30.1.4 { 001571 CAST(X'2D31313235383939393036383432363234' AS INTEGER) 001572 } integer -1125899906842624 001573 001574 rename db db2 001575 sqlite3 db :memory: 001576 ifcapable {utf16} { 001577 execsql { PRAGMA encoding = 'utf-16be' } 001578 do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123 001579 do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678 001580 do_expr_test e_expr-30.1.7 { 001581 CAST(X'0031003000300030003000300030' AS INTEGER) 001582 } integer 1000000 001583 do_expr_test e_expr-30.1.8 { 001584 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 001585 } integer -1125899906842624 001586 } 001587 db close 001588 rename db2 db 001589 001590 # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the 001591 # longest possible prefix of the value that can be interpreted as an 001592 # integer number is extracted from the TEXT value and the remainder 001593 # ignored. 001594 # 001595 do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123 001596 do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523 001597 do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2 001598 do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1 001599 001600 # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when 001601 # converting from TEXT to INTEGER are ignored. 001602 # 001603 do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123 001604 do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523 001605 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2 001606 do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1 001607 001608 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be 001609 # interpreted as an integer number, the result of the conversion is 0. 001610 # 001611 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0 001612 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0 001613 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0 001614 001615 # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal 001616 # integers only — conversion of hexadecimal integers stops at 001617 # the "x" in the "0x" prefix of the hexadecimal integer string and thus 001618 # result of the CAST is always zero. 001619 do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0 001620 do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0 001621 001622 # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER 001623 # results in the integer between the REAL value and zero that is closest 001624 # to the REAL value. 001625 # 001626 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3 001627 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1 001628 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1 001629 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0 001630 001631 # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest 001632 # possible signed integer (+9223372036854775807) then the result is the 001633 # greatest possible signed integer and if the REAL is less than the 001634 # least possible signed integer (-9223372036854775808) then the result 001635 # is the least possible signed integer. 001636 # 001637 do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807 001638 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808 001639 do_expr_test e_expr-31.2.3 { 001640 CAST(-9223372036854775809.0 AS INT) 001641 } integer -9223372036854775808 001642 do_expr_test e_expr-31.2.4 { 001643 CAST(9223372036854775809.0 AS INT) 001644 } integer 9223372036854775807 001645 001646 001647 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC 001648 # yields either an INTEGER or a REAL result. 001649 # 001650 # EVIDENCE-OF: R-48945-04866 If the input text looks like an integer 001651 # (there is no decimal point nor exponent) and the value is small enough 001652 # to fit in a 64-bit signed integer, then the result will be INTEGER. 001653 # 001654 # EVIDENCE-OF: R-47045-23194 Input text that looks like floating point 001655 # (there is a decimal point and/or an exponent) and the text describes a 001656 # value that can be losslessly converted back and forth between IEEE 754 001657 # 64-bit float and a 51-bit signed integer, then the result is INTEGER. 001658 # 001659 do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 001660 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45 001661 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 001662 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 001663 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 001664 do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000 001665 do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000 001666 do_test e_expr-32.1.8 { 001667 set expr {CAST( '9.223372036e15' AS NUMERIC)} 001668 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; 001669 list $type $value 001670 } {real 9.22337e+15} 001671 do_test e_expr-32.1.9 { 001672 set expr {CAST('-9.223372036e15' AS NUMERIC)} 001673 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; 001674 list $type $value 001675 } {real -9.22337e+15} 001676 001677 # EVIDENCE-OF: R-50300-26941 Any text input that describes a value 001678 # outside the range of a 64-bit signed integer yields a REAL result. 001679 # 001680 do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \ 001681 integer 9223372036854775807 001682 do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \ 001683 real 9.22337203685478e+18 001684 do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \ 001685 integer -9223372036854775808 001686 do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \ 001687 real -9.22337203685478e+18 001688 001689 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC 001690 # is a no-op, even if a real value could be losslessly converted to an 001691 # integer. 001692 # 001693 do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0 001694 do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5 001695 001696 do_expr_test e_expr-32.2.3 { 001697 CAST(-9223372036854775808 AS NUMERIC) 001698 } integer -9223372036854775808 001699 do_expr_test e_expr-32.2.4 { 001700 CAST(9223372036854775807 AS NUMERIC) 001701 } integer 9223372036854775807 001702 do_expr_test e_expr-32.2.5 { 001703 CAST('9223372036854775807 ' AS NUMERIC) 001704 } integer 9223372036854775807 001705 do_expr_test e_expr-32.2.6 { 001706 CAST(' 9223372036854775807 ' AS NUMERIC) 001707 } integer 9223372036854775807 001708 do_expr_test e_expr-32.2.7 { 001709 CAST(' ' AS NUMERIC) 001710 } integer 0 001711 do_execsql_test e_expr-32.2.8 { 001712 WITH t1(x) AS (VALUES 001713 ('9000000000000000001'), 001714 ('9000000000000000001x'), 001715 ('9000000000000000001 '), 001716 (' 9000000000000000001 '), 001717 (' 9000000000000000001'), 001718 (' 9000000000000000001.'), 001719 ('9223372036854775807'), 001720 ('9223372036854775807 '), 001721 (' 9223372036854775807 '), 001722 ('9223372036854775808'), 001723 (' 9223372036854775808 '), 001724 ('9223372036854775807.0'), 001725 ('9223372036854775807e+0'), 001726 ('-5.0'), 001727 ('-5e+0')) 001728 SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1; 001729 } [list \ 001730 integer 9000000000000000001 \ 001731 integer 9000000000000000001 \ 001732 integer 9000000000000000001 \ 001733 integer 9000000000000000001 \ 001734 integer 9000000000000000001 \ 001735 real 9.0e+18 \ 001736 integer 9223372036854775807 \ 001737 integer 9223372036854775807 \ 001738 integer 9223372036854775807 \ 001739 real 9.22337203685478e+18 \ 001740 real 9.22337203685478e+18 \ 001741 real 9.22337203685478e+18 \ 001742 real 9.22337203685478e+18 \ 001743 integer -5 \ 001744 integer -5 \ 001745 ] 001746 001747 # EVIDENCE-OF: R-64550-29191 Note that the result from casting any 001748 # non-BLOB value into a BLOB and the result from casting any BLOB value 001749 # into a non-BLOB value may be different depending on whether the 001750 # database encoding is UTF-8, UTF-16be, or UTF-16le. 001751 # 001752 ifcapable {utf16} { 001753 sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' } 001754 sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' } 001755 sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' } 001756 foreach {tn castexpr differs} { 001757 1 { CAST(123 AS BLOB) } 1 001758 2 { CAST('' AS BLOB) } 0 001759 3 { CAST('abcd' AS BLOB) } 1 001760 001761 4 { CAST(X'abcd' AS TEXT) } 1 001762 5 { CAST(X'' AS TEXT) } 0 001763 } { 001764 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"] 001765 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"] 001766 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"] 001767 001768 if {$differs} { 001769 set res [expr {$r1!=$r2 && $r2!=$r3}] 001770 } else { 001771 set res [expr {$r1==$r2 && $r2==$r3}] 001772 } 001773 001774 do_test e_expr-33.1.$tn {set res} 1 001775 } 001776 db1 close 001777 db2 close 001778 db3 close 001779 } 001780 001781 #------------------------------------------------------------------------- 001782 # Test statements related to the EXISTS and NOT EXISTS operators. 001783 # 001784 catch { db close } 001785 forcedelete test.db 001786 sqlite3 db test.db 001787 001788 do_execsql_test e_expr-34.1 { 001789 CREATE TABLE t1(a, b); 001790 INSERT INTO t1 VALUES(1, 2); 001791 INSERT INTO t1 VALUES(NULL, 2); 001792 INSERT INTO t1 VALUES(1, NULL); 001793 INSERT INTO t1 VALUES(NULL, NULL); 001794 } {} 001795 001796 # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one 001797 # of the integer values 0 and 1. 001798 # 001799 # This statement is not tested by itself. Instead, all e_expr-34.* tests 001800 # following this point explicitly test that specific invocations of EXISTS 001801 # return either integer 0 or integer 1. 001802 # 001803 001804 # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified 001805 # as the right-hand operand of the EXISTS operator would return one or 001806 # more rows, then the EXISTS operator evaluates to 1. 001807 # 001808 foreach {tn expr} { 001809 1 { EXISTS ( SELECT a FROM t1 ) } 001810 2 { EXISTS ( SELECT b FROM t1 ) } 001811 3 { EXISTS ( SELECT 24 ) } 001812 4 { EXISTS ( SELECT NULL ) } 001813 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) } 001814 } { 001815 do_expr_test e_expr-34.2.$tn $expr integer 1 001816 } 001817 001818 # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no 001819 # rows at all, then the EXISTS operator evaluates to 0. 001820 # 001821 foreach {tn expr} { 001822 1 { EXISTS ( SELECT a FROM t1 WHERE 0) } 001823 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) } 001824 3 { EXISTS ( SELECT 24 WHERE 0) } 001825 4 { EXISTS ( SELECT NULL WHERE 1=2) } 001826 } { 001827 do_expr_test e_expr-34.3.$tn $expr integer 0 001828 } 001829 001830 # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned 001831 # by the SELECT statement (if any) and the specific values returned have 001832 # no effect on the results of the EXISTS operator. 001833 # 001834 foreach {tn expr res} { 001835 1 { EXISTS ( SELECT * FROM t1 ) } 1 001836 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1 001837 3 { EXISTS ( SELECT 24, 25 ) } 1 001838 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1 001839 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1 001840 001841 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0 001842 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0 001843 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0 001844 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0 001845 } { 001846 do_expr_test e_expr-34.4.$tn $expr integer $res 001847 } 001848 001849 # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values 001850 # are not handled any differently from rows without NULL values. 001851 # 001852 foreach {tn e1 e2} { 001853 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) } 001854 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) } 001855 } { 001856 set res [db one "SELECT $e1"] 001857 do_expr_test e_expr-34.5.${tn}a $e1 integer $res 001858 do_expr_test e_expr-34.5.${tn}b $e2 integer $res 001859 } 001860 001861 #------------------------------------------------------------------------- 001862 # Test statements related to scalar sub-queries. 001863 # 001864 001865 catch { db close } 001866 forcedelete test.db 001867 sqlite3 db test.db 001868 do_test e_expr-35.0 { 001869 execsql { 001870 CREATE TABLE t2(a, b); 001871 INSERT INTO t2 VALUES('one', 'two'); 001872 INSERT INTO t2 VALUES('three', NULL); 001873 INSERT INTO t2 VALUES(4, 5.0); 001874 } 001875 } {} 001876 001877 # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses 001878 # is a subquery. 001879 # 001880 # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including 001881 # aggregate and compound SELECT queries (queries with keywords like 001882 # UNION or EXCEPT) are allowed as scalar subqueries. 001883 # 001884 do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35 001885 do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {} 001886 001887 do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3 001888 do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4 001889 001890 do_expr_test e_expr-35.1.5 { 001891 (SELECT b FROM t2 UNION SELECT a+1 FROM t2) 001892 } null {} 001893 do_expr_test e_expr-35.1.6 { 001894 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1) 001895 } integer 4 001896 001897 # EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns 001898 # is a row value subquery and can only be used as the operand of a 001899 # comparison operator. 001900 # 001901 # The following block tests that errors are returned in a bunch of cases 001902 # where a subquery returns more than one column. 001903 # 001904 set M {/1 {sub-select returns [23] columns - expected 1}/} 001905 foreach {tn sql} { 001906 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } 001907 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } 001908 3 { SELECT (SELECT 1, 2) } 001909 4 { SELECT (SELECT NULL, NULL, NULL) } 001910 5 { SELECT (SELECT * FROM t2) } 001911 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } 001912 } { 001913 do_catchsql_test e_expr-35.2.$tn $sql $M 001914 } 001915 001916 # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the 001917 # first row of the result from the enclosed SELECT statement. 001918 # 001919 do_execsql_test e_expr-36.3.1 { 001920 CREATE TABLE t4(x, y); 001921 INSERT INTO t4 VALUES(1, 'one'); 001922 INSERT INTO t4 VALUES(2, 'two'); 001923 INSERT INTO t4 VALUES(3, 'three'); 001924 } {} 001925 001926 foreach {tn expr restype resval} { 001927 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1 001928 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1 001929 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3 001930 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2 001931 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two 001932 001933 7 { ( SELECT sum(x) FROM t4 ) } integer 6 001934 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree 001935 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 001936 001937 } { 001938 do_expr_test e_expr-36.3.$tn $expr $restype $resval 001939 } 001940 001941 # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL 001942 # if the enclosed SELECT statement returns no rows. 001943 # 001944 foreach {tn expr} { 001945 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) } 001946 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) } 001947 } { 001948 do_expr_test e_expr-36.4.$tn $expr null {} 001949 } 001950 001951 # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0, 001952 # 'english' and '0' are all considered to be false. 001953 # 001954 do_execsql_test e_expr-37.1 { 001955 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END; 001956 } {false} 001957 do_execsql_test e_expr-37.2 { 001958 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END; 001959 } {false} 001960 do_execsql_test e_expr-37.3 { 001961 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END; 001962 } {false} 001963 do_execsql_test e_expr-37.4 { 001964 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END; 001965 } {false} 001966 do_execsql_test e_expr-37.5 { 001967 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END; 001968 } {false} 001969 001970 # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are 001971 # considered to be true. 001972 # 001973 do_execsql_test e_expr-37.6 { 001974 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END; 001975 } {true} 001976 do_execsql_test e_expr-37.7 { 001977 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END; 001978 } {true} 001979 do_execsql_test e_expr-37.8 { 001980 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END; 001981 } {true} 001982 do_execsql_test e_expr-37.9 { 001983 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END; 001984 } {true} 001985 do_execsql_test e_expr-37.10 { 001986 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END; 001987 } {true} 001988 001989 001990 finish_test