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.1 {
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.2 {
001239 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, iif(0,8,99);
001240 } {C 99}
001241
001242 # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
001243 # expression is evaluated just once and the result is compared against
001244 # the evaluation of each WHEN expression from left to right.
001245 #
001246 # Note: This test case tests the "evaluated just once" part of the above
001247 # statement. Tests associated with the next two statements test that the
001248 # comparisons take place.
001249 #
001250 foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
001251 set ::varlist [list]
001252 do_execsql_test e_expr-22.1.1 {
001253 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
001254 } {C}
001255 do_test e_expr-22.1.2 { set ::varlist } {a}
001256
001257 # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
001258 # evaluation of the THEN expression that corresponds to the first WHEN
001259 # expression for which the comparison is true.
001260 #
001261 do_execsql_test e_expr-22.2.1 {
001262 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001263 } {B}
001264 do_execsql_test e_expr-22.2.2 {
001265 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001266 } {A}
001267
001268 # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
001269 # evaluate to a value equal to the base expression, the result of
001270 # evaluating the ELSE expression, if any.
001271 #
001272 do_execsql_test e_expr-22.3.1 {
001273 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
001274 } {D}
001275
001276 # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
001277 # the WHEN expressions produce a result equal to the base expression,
001278 # the overall result is NULL.
001279 #
001280 do_execsql_test e_expr-22.4.1 {
001281 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001282 } {{}}
001283 db nullvalue null
001284 do_execsql_test e_expr-22.4.2 {
001285 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001286 } {null}
001287 db nullvalue {}
001288
001289 # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
001290 # WHEN expression, the same collating sequence, affinity, and
001291 # NULL-handling rules apply as if the base expression and WHEN
001292 # expression are respectively the left- and right-hand operands of an =
001293 # operator.
001294 #
001295 proc rev {str} {
001296 set ret ""
001297 set chars [split $str]
001298 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
001299 append ret [lindex $chars $i]
001300 }
001301 set ret
001302 }
001303 proc reverse {lhs rhs} {
001304 string compare [rev $lhs] [rev $rhs]
001305 }
001306 db collate reverse reverse
001307 do_execsql_test e_expr-23.1.1 {
001308 CREATE TABLE t1(
001309 a TEXT COLLATE NOCASE,
001310 b COLLATE REVERSE,
001311 c INTEGER,
001312 d BLOB
001313 );
001314 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
001315 } {}
001316 do_execsql_test e_expr-23.1.2 {
001317 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
001318 } {B}
001319 do_execsql_test e_expr-23.1.3 {
001320 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
001321 } {B}
001322 do_execsql_test e_expr-23.1.4 {
001323 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
001324 } {B}
001325 do_execsql_test e_expr-23.1.5 {
001326 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
001327 } {B}
001328 do_execsql_test e_expr-23.1.6 {
001329 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
001330 } {B}
001331 do_execsql_test e_expr-23.1.7 {
001332 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
001333 } {A}
001334 do_execsql_test e_expr-23.1.8 {
001335 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
001336 } {B}
001337 do_execsql_test e_expr-23.1.9 {
001338 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
001339 } {B}
001340
001341 # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
001342 # result of the CASE is always the result of evaluating the ELSE
001343 # expression if it exists, or NULL if it does not.
001344 #
001345 do_execsql_test e_expr-24.1.1 {
001346 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
001347 } {{}}
001348 do_execsql_test e_expr-24.1.2 {
001349 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
001350 } {C}
001351
001352 # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
001353 # or short-circuit, evaluation.
001354 #
001355 set varlist [list]
001356 foreach {a b c} {0 1 0} break
001357 do_execsql_test e_expr-25.1.1 {
001358 SELECT CASE WHEN var('a') THEN 'A'
001359 WHEN var('b') THEN 'B'
001360 WHEN var('c') THEN 'C'
001361 END
001362 } {B}
001363 do_test e_expr-25.1.2 { set ::varlist } {a b}
001364 set varlist [list]
001365 do_execsql_test e_expr-25.1.3 {
001366 SELECT CASE '0' WHEN var('a') THEN 'A'
001367 WHEN var('b') THEN 'B'
001368 WHEN var('c') THEN 'C'
001369 END
001370 } {A}
001371 do_test e_expr-25.1.4 { set ::varlist } {a}
001372
001373 # EVIDENCE-OF: R-34773-62253 The only difference between the following
001374 # two CASE expressions is that the x expression is evaluated exactly
001375 # once in the first example but might be evaluated multiple times in the
001376 # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
001377 # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
001378 #
001379 proc ceval {x} {
001380 incr ::evalcount
001381 return $x
001382 }
001383 db func ceval ceval
001384 set ::evalcount 0
001385
001386 do_execsql_test e_expr-26.1.1 {
001387 CREATE TABLE t2(x, w1, r1, w2, r2, r3);
001388 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
001389 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
001390 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
001391 } {}
001392 do_execsql_test e_expr-26.1.2 {
001393 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001394 } {R1 R2 R3}
001395 do_execsql_test e_expr-26.1.3 {
001396 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
001397 } {R1 R2 R3}
001398
001399 do_execsql_test e_expr-26.1.4 {
001400 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001401 } {R1 R2 R3}
001402 do_test e_expr-26.1.5 { set ::evalcount } {3}
001403 set ::evalcount 0
001404 do_execsql_test e_expr-26.1.6 {
001405 SELECT CASE
001406 WHEN ceval(x)=w1 THEN r1
001407 WHEN ceval(x)=w2 THEN r2
001408 ELSE r3 END
001409 FROM t2
001410 } {R1 R2 R3}
001411 do_test e_expr-26.1.6 { set ::evalcount } {5}
001412
001413
001414 #-------------------------------------------------------------------------
001415 # Test statements related to CAST expressions.
001416 #
001417 # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
001418 # conversion that takes place when a column affinity is applied to a
001419 # value except that with the CAST operator the conversion always takes
001420 # place even if the conversion lossy and irreversible, whereas column
001421 # affinity only changes the data type of a value if the change is
001422 # lossless and reversible.
001423 #
001424 do_execsql_test e_expr-27.1.1 {
001425 CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
001426 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
001427 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
001428 } {blob UVU text 1.23abc real 4.5}
001429 do_execsql_test e_expr-27.1.2 {
001430 SELECT
001431 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
001432 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
001433 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
001434 } {text UVU real 1.23 integer 4}
001435
001436 # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
001437 # result of the CAST expression is also NULL.
001438 #
001439 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
001440 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {}
001441 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {}
001442 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {}
001443
001444 # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
001445 # is determined by applying the rules for determining column affinity to
001446 # the type-name.
001447 #
001448 # The R-29283-15561 requirement above is demonstrated by all of the
001449 # subsequent e_expr-26 tests.
001450 #
001451 # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
001452 # affinity causes the value to be converted into a BLOB.
001453 #
001454 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc
001455 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
001456 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi
001457
001458 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
001459 # the value to TEXT in the encoding of the database connection, then
001460 # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
001461 #
001462 do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
001463 do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536'
001464 do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738'
001465 rename db db2
001466 sqlite3 db :memory:
001467 ifcapable {utf16} {
001468 db eval { PRAGMA encoding = 'utf-16le' }
001469 do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
001470 do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600'
001471 do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800'
001472 }
001473 db close
001474 sqlite3 db :memory:
001475 db eval { PRAGMA encoding = 'utf-16be' }
001476 ifcapable {utf16} {
001477 do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
001478 do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036'
001479 do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038'
001480 }
001481 db close
001482 rename db2 db
001483
001484 # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
001485 # of bytes that make up the BLOB is interpreted as text encoded using
001486 # the database encoding.
001487 #
001488 do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
001489 do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
001490 rename db db2
001491 sqlite3 db :memory:
001492 db eval { PRAGMA encoding = 'utf-16le' }
001493 ifcapable {utf16} {
001494 do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
001495 do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
001496 }
001497 db close
001498 rename db2 db
001499
001500 # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
001501 # renders the value as if via sqlite3_snprintf() except that the
001502 # resulting TEXT uses the encoding of the database connection.
001503 #
001504 do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1
001505 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45
001506 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45
001507 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8
001508 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0
001509 do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
001510 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0
001511 do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0
001512
001513 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
001514 # value is first converted to TEXT.
001515 #
001516 do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
001517 do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
001518 do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
001519 do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
001520 rename db db2
001521 sqlite3 db :memory:
001522 ifcapable {utf16} {
001523 db eval { PRAGMA encoding = 'utf-16le' }
001524 do_expr_test e_expr-29.1.5 {
001525 CAST (X'31002E0032003300' AS REAL) } real 1.23
001526 do_expr_test e_expr-29.1.6 {
001527 CAST (X'3200330030002E003000' AS REAL) } real 230.0
001528 do_expr_test e_expr-29.1.7 {
001529 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
001530 do_expr_test e_expr-29.1.8 {
001531 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
001532 }
001533 db close
001534 rename db2 db
001535
001536 # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
001537 # longest possible prefix of the value that can be interpreted as a real
001538 # number is extracted from the TEXT value and the remainder ignored.
001539 #
001540 do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
001541 do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
001542 do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
001543 do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
001544
001545 # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
001546 # ignored when converging from TEXT to REAL.
001547 #
001548 do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
001549 do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45
001550 do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212
001551 do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
001552
001553 # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
001554 # interpreted as a real number, the result of the conversion is 0.0.
001555 #
001556 do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
001557 do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
001558 do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
001559
001560 # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
001561 # value is first converted to TEXT.
001562 #
001563 do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
001564 do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
001565 do_expr_test e_expr-30.1.3 {
001566 CAST(X'31303030303030' AS INTEGER)
001567 } integer 1000000
001568 do_expr_test e_expr-30.1.4 {
001569 CAST(X'2D31313235383939393036383432363234' AS INTEGER)
001570 } integer -1125899906842624
001571
001572 rename db db2
001573 sqlite3 db :memory:
001574 ifcapable {utf16} {
001575 execsql { PRAGMA encoding = 'utf-16be' }
001576 do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
001577 do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
001578 do_expr_test e_expr-30.1.7 {
001579 CAST(X'0031003000300030003000300030' AS INTEGER)
001580 } integer 1000000
001581 do_expr_test e_expr-30.1.8 {
001582 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER)
001583 } integer -1125899906842624
001584 }
001585 db close
001586 rename db2 db
001587
001588 # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
001589 # longest possible prefix of the value that can be interpreted as an
001590 # integer number is extracted from the TEXT value and the remainder
001591 # ignored.
001592 #
001593 do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
001594 do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
001595 do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
001596 do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
001597
001598 # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
001599 # converting from TEXT to INTEGER are ignored.
001600 #
001601 do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123
001602 do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523
001603 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
001604 do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1
001605
001606 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
001607 # interpreted as an integer number, the result of the conversion is 0.
001608 #
001609 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
001610 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
001611 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
001612
001613 # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
001614 # integers only — conversion of hexadecimal integers stops at
001615 # the "x" in the "0x" prefix of the hexadecimal integer string and thus
001616 # result of the CAST is always zero.
001617 do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
001618 do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
001619
001620 # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
001621 # results in the integer between the REAL value and zero that is closest
001622 # to the REAL value.
001623 #
001624 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
001625 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
001626 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
001627 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
001628
001629 # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
001630 # possible signed integer (+9223372036854775807) then the result is the
001631 # greatest possible signed integer and if the REAL is less than the
001632 # least possible signed integer (-9223372036854775808) then the result
001633 # is the least possible signed integer.
001634 #
001635 do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
001636 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
001637 do_expr_test e_expr-31.2.3 {
001638 CAST(-9223372036854775809.0 AS INT)
001639 } integer -9223372036854775808
001640 do_expr_test e_expr-31.2.4 {
001641 CAST(9223372036854775809.0 AS INT)
001642 } integer 9223372036854775807
001643
001644
001645 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
001646 # yields either an INTEGER or a REAL result.
001647 #
001648 # EVIDENCE-OF: R-48945-04866 If the input text looks like an integer
001649 # (there is no decimal point nor exponent) and the value is small enough
001650 # to fit in a 64-bit signed integer, then the result will be INTEGER.
001651 #
001652 # EVIDENCE-OF: R-47045-23194 Input text that looks like floating point
001653 # (there is a decimal point and/or an exponent) and the text describes a
001654 # value that can be losslessly converted back and forth between IEEE 754
001655 # 64-bit float and a 51-bit signed integer, then the result is INTEGER.
001656 #
001657 do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45
001658 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45
001659 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2
001660 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
001661 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
001662 do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000
001663 do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000
001664 do_test e_expr-32.1.8 {
001665 set expr {CAST( '9.223372036e15' AS NUMERIC)}
001666 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
001667 list $type $value
001668 } {real 9.22337e+15}
001669 do_test e_expr-32.1.9 {
001670 set expr {CAST('-9.223372036e15' AS NUMERIC)}
001671 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
001672 list $type $value
001673 } {real -9.22337e+15}
001674
001675 # EVIDENCE-OF: R-50300-26941 Any text input that describes a value
001676 # outside the range of a 64-bit signed integer yields a REAL result.
001677 #
001678 do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \
001679 integer 9223372036854775807
001680 do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \
001681 real 9.22337203685478e+18
001682 do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \
001683 integer -9223372036854775808
001684 do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \
001685 real -9.22337203685478e+18
001686
001687 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
001688 # is a no-op, even if a real value could be losslessly converted to an
001689 # integer.
001690 #
001691 do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
001692 do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
001693
001694 do_expr_test e_expr-32.2.3 {
001695 CAST(-9223372036854775808 AS NUMERIC)
001696 } integer -9223372036854775808
001697 do_expr_test e_expr-32.2.4 {
001698 CAST(9223372036854775807 AS NUMERIC)
001699 } integer 9223372036854775807
001700 do_expr_test e_expr-32.2.5 {
001701 CAST('9223372036854775807 ' AS NUMERIC)
001702 } integer 9223372036854775807
001703 do_expr_test e_expr-32.2.6 {
001704 CAST(' 9223372036854775807 ' AS NUMERIC)
001705 } integer 9223372036854775807
001706 do_expr_test e_expr-32.2.7 {
001707 CAST(' ' AS NUMERIC)
001708 } integer 0
001709 do_execsql_test e_expr-32.2.8 {
001710 WITH t1(x) AS (VALUES
001711 ('9000000000000000001'),
001712 ('9000000000000000001x'),
001713 ('9000000000000000001 '),
001714 (' 9000000000000000001 '),
001715 (' 9000000000000000001'),
001716 (' 9000000000000000001.'),
001717 ('9223372036854775807'),
001718 ('9223372036854775807 '),
001719 (' 9223372036854775807 '),
001720 ('9223372036854775808'),
001721 (' 9223372036854775808 '),
001722 ('9223372036854775807.0'),
001723 ('9223372036854775807e+0'),
001724 ('-5.0'),
001725 ('-5e+0'))
001726 SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
001727 } [list \
001728 integer 9000000000000000001 \
001729 integer 9000000000000000001 \
001730 integer 9000000000000000001 \
001731 integer 9000000000000000001 \
001732 integer 9000000000000000001 \
001733 real 9.0e+18 \
001734 integer 9223372036854775807 \
001735 integer 9223372036854775807 \
001736 integer 9223372036854775807 \
001737 real 9.22337203685478e+18 \
001738 real 9.22337203685478e+18 \
001739 real 9.22337203685478e+18 \
001740 real 9.22337203685478e+18 \
001741 integer -5 \
001742 integer -5 \
001743 ]
001744
001745 # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
001746 # non-BLOB value into a BLOB and the result from casting any BLOB value
001747 # into a non-BLOB value may be different depending on whether the
001748 # database encoding is UTF-8, UTF-16be, or UTF-16le.
001749 #
001750 ifcapable {utf16} {
001751 sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
001752 sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
001753 sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
001754 foreach {tn castexpr differs} {
001755 1 { CAST(123 AS BLOB) } 1
001756 2 { CAST('' AS BLOB) } 0
001757 3 { CAST('abcd' AS BLOB) } 1
001758
001759 4 { CAST(X'abcd' AS TEXT) } 1
001760 5 { CAST(X'' AS TEXT) } 0
001761 } {
001762 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
001763 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
001764 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
001765
001766 if {$differs} {
001767 set res [expr {$r1!=$r2 && $r2!=$r3}]
001768 } else {
001769 set res [expr {$r1==$r2 && $r2==$r3}]
001770 }
001771
001772 do_test e_expr-33.1.$tn {set res} 1
001773 }
001774 db1 close
001775 db2 close
001776 db3 close
001777 }
001778
001779 #-------------------------------------------------------------------------
001780 # Test statements related to the EXISTS and NOT EXISTS operators.
001781 #
001782 catch { db close }
001783 forcedelete test.db
001784 sqlite3 db test.db
001785
001786 do_execsql_test e_expr-34.1 {
001787 CREATE TABLE t1(a, b);
001788 INSERT INTO t1 VALUES(1, 2);
001789 INSERT INTO t1 VALUES(NULL, 2);
001790 INSERT INTO t1 VALUES(1, NULL);
001791 INSERT INTO t1 VALUES(NULL, NULL);
001792 } {}
001793
001794 # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
001795 # of the integer values 0 and 1.
001796 #
001797 # This statement is not tested by itself. Instead, all e_expr-34.* tests
001798 # following this point explicitly test that specific invocations of EXISTS
001799 # return either integer 0 or integer 1.
001800 #
001801
001802 # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
001803 # as the right-hand operand of the EXISTS operator would return one or
001804 # more rows, then the EXISTS operator evaluates to 1.
001805 #
001806 foreach {tn expr} {
001807 1 { EXISTS ( SELECT a FROM t1 ) }
001808 2 { EXISTS ( SELECT b FROM t1 ) }
001809 3 { EXISTS ( SELECT 24 ) }
001810 4 { EXISTS ( SELECT NULL ) }
001811 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
001812 } {
001813 do_expr_test e_expr-34.2.$tn $expr integer 1
001814 }
001815
001816 # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
001817 # rows at all, then the EXISTS operator evaluates to 0.
001818 #
001819 foreach {tn expr} {
001820 1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
001821 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
001822 3 { EXISTS ( SELECT 24 WHERE 0) }
001823 4 { EXISTS ( SELECT NULL WHERE 1=2) }
001824 } {
001825 do_expr_test e_expr-34.3.$tn $expr integer 0
001826 }
001827
001828 # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
001829 # by the SELECT statement (if any) and the specific values returned have
001830 # no effect on the results of the EXISTS operator.
001831 #
001832 foreach {tn expr res} {
001833 1 { EXISTS ( SELECT * FROM t1 ) } 1
001834 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1
001835 3 { EXISTS ( SELECT 24, 25 ) } 1
001836 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1
001837 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1
001838
001839 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0
001840 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0
001841 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0
001842 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0
001843 } {
001844 do_expr_test e_expr-34.4.$tn $expr integer $res
001845 }
001846
001847 # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
001848 # are not handled any differently from rows without NULL values.
001849 #
001850 foreach {tn e1 e2} {
001851 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) }
001852 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) }
001853 } {
001854 set res [db one "SELECT $e1"]
001855 do_expr_test e_expr-34.5.${tn}a $e1 integer $res
001856 do_expr_test e_expr-34.5.${tn}b $e2 integer $res
001857 }
001858
001859 #-------------------------------------------------------------------------
001860 # Test statements related to scalar sub-queries.
001861 #
001862
001863 catch { db close }
001864 forcedelete test.db
001865 sqlite3 db test.db
001866 do_test e_expr-35.0 {
001867 execsql {
001868 CREATE TABLE t2(a, b);
001869 INSERT INTO t2 VALUES('one', 'two');
001870 INSERT INTO t2 VALUES('three', NULL);
001871 INSERT INTO t2 VALUES(4, 5.0);
001872 }
001873 } {}
001874
001875 # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
001876 # is a subquery.
001877 #
001878 # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
001879 # aggregate and compound SELECT queries (queries with keywords like
001880 # UNION or EXCEPT) are allowed as scalar subqueries.
001881 #
001882 do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35
001883 do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
001884
001885 do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
001886 do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
001887
001888 do_expr_test e_expr-35.1.5 {
001889 (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
001890 } null {}
001891 do_expr_test e_expr-35.1.6 {
001892 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
001893 } integer 4
001894
001895 # EVIDENCE-OF: R-43101-20178 A subquery that returns two or more columns
001896 # is a row value subquery and can only be used as an operand of a
001897 # comparison operator or as the value in an UPDATE SET clause whose
001898 # column name list has the same size.
001899 #
001900 # The following block tests that errors are returned in a bunch of cases
001901 # where a subquery returns more than one column.
001902 #
001903 set M {/1 {sub-select returns [23] columns - expected 1}/}
001904 foreach {tn sql} {
001905 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
001906 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
001907 3 { SELECT (SELECT 1, 2) }
001908 4 { SELECT (SELECT NULL, NULL, NULL) }
001909 5 { SELECT (SELECT * FROM t2) }
001910 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
001911 } {
001912 do_catchsql_test e_expr-35.2.$tn $sql $M
001913 }
001914
001915 # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the
001916 # first row of the result from the enclosed SELECT statement.
001917 #
001918 do_execsql_test e_expr-36.3.1 {
001919 CREATE TABLE t4(x, y);
001920 INSERT INTO t4 VALUES(1, 'one');
001921 INSERT INTO t4 VALUES(2, 'two');
001922 INSERT INTO t4 VALUES(3, 'three');
001923 } {}
001924
001925 foreach {tn expr restype resval} {
001926 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1
001927 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1
001928 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3
001929 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2
001930 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two
001931
001932 7 { ( SELECT sum(x) FROM t4 ) } integer 6
001933 8 { ( SELECT string_agg(y,'') FROM t4 ) } text onetwothree
001934 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2
001935
001936 } {
001937 do_expr_test e_expr-36.3.$tn $expr $restype $resval
001938 }
001939
001940 # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
001941 # if the enclosed SELECT statement returns no rows.
001942 #
001943 foreach {tn expr} {
001944 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) }
001945 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) }
001946 } {
001947 do_expr_test e_expr-36.4.$tn $expr null {}
001948 }
001949
001950 # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
001951 # 'english' and '0' are all considered to be false.
001952 #
001953 do_execsql_test e_expr-37.1 {
001954 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END, iif(NULL,'true','false');
001955 } {false false}
001956 do_execsql_test e_expr-37.2 {
001957 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END, iif(0.0,'true','false');
001958 } {false false}
001959 do_execsql_test e_expr-37.3 {
001960 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END, iif(0,'true','false');
001961 } {false false}
001962 do_execsql_test e_expr-37.4 {
001963 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END, iif('engligh','true','false');
001964 } {false false}
001965 do_execsql_test e_expr-37.5 {
001966 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END, iif('0','true','false');
001967 } {false false}
001968
001969 # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
001970 # considered to be true.
001971 #
001972 do_execsql_test e_expr-37.6 {
001973 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END, iif(1,'true','false');
001974 } {true true}
001975 do_execsql_test e_expr-37.7 {
001976 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END, iif(1.0,'true','false');
001977 } {true true}
001978 do_execsql_test e_expr-37.8 {
001979 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END, iif(0.1,'true','false');
001980 } {true true}
001981 do_execsql_test e_expr-37.9 {
001982 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END, iif(-0.1,'true','false');
001983 } {true true}
001984 do_execsql_test e_expr-37.10 {
001985 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END, iif('1engl','true','false');
001986 } {true true}
001987
001988
001989 finish_test