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