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