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