Debian Codegraph @

test/e_expr.test

raw
1 # 2010 July 16
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12 # This file implements tests to verify that the "testable statements" in
13 # the lang_expr.html document are correct.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 source $testdir/malloc_common.tcl
19
20 ifcapable !compound {
21 finish_test
22 return
23 }
24
25 proc do_expr_test {tn expr type value} {
26 uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
27 list [list $type $value]
28 ]
29 }
30
31 proc do_qexpr_test {tn expr value} {
32 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
33 }
34
35 # Set up three global variables:
36 #
37 # ::opname An array mapping from SQL operator to an easy to parse
38 # name. The names are used as part of test case names.
39 #
40 # ::opprec An array mapping from SQL operator to a numeric
41 # precedence value. Operators that group more tightly
42 # have lower numeric precedences.
43 #
44 # ::oplist A list of all SQL operators supported by SQLite.
45 #
46 foreach {op opn} {
47 || cat * mul / div % mod + add
48 - sub << lshift >> rshift & bitand | bitor
49 < less <= lesseq > more >= moreeq = eq1
50 == eq2 <> ne1 != ne2 IS is LIKE like
51 GLOB glob AND and OR or MATCH match REGEXP regexp
52 {IS NOT} isnt
53 } {
54 set ::opname($op) $opn
55 }
56 set oplist [list]
57 foreach {prec opl} {
58 1 ||
59 2 {* / %}
60 3 {+ -}
61 4 {<< >> & |}
62 5 {< <= > >=}
63 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
64 7 AND
65 8 OR
66 } {
67 foreach op $opl {
68 set ::opprec($op) $prec
69 lappend oplist $op
70 }
71 }
72
73
74 # Hook in definitions of MATCH and REGEX. The following implementations
75 # cause MATCH and REGEX to behave similarly to the == operator.
76 #
77 proc matchfunc {a b} { return [expr {$a==$b}] }
78 proc regexfunc {a b} { return [expr {$a==$b}] }
79 db func match -argcount 2 matchfunc
80 db func regexp -argcount 2 regexfunc
81
82 #-------------------------------------------------------------------------
83 # Test cases e_expr-1.* attempt to verify that all binary operators listed
84 # in the documentation exist and that the relative precedences of the
85 # operators are also as the documentation suggests.
86 #
87 # X-EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
88 # operators, in order from highest to lowest precedence: || * / % + -
89 # << >> & | < <= > >= = == != <> IS IS
90 # NOT IN LIKE GLOB MATCH REGEXP AND OR
91 #
92 # X-EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
93 # precedence as =.
94 #
95
96 unset -nocomplain untested
97 foreach op1 $oplist {
98 foreach op2 $oplist {
99 set untested($op1,$op2) 1
100 foreach {tn A B C} {
101 1 22 45 66
102 2 0 0 0
103 3 0 0 1
104 4 0 1 0
105 5 0 1 1
106 6 1 0 0
107 7 1 0 1
108 8 1 1 0
109 9 1 1 1
110 10 5 6 1
111 11 1 5 6
112 12 1 5 5
113 13 5 5 1
114
115 14 5 2 1
116 15 1 4 1
117 16 -1 0 1
118 17 0 1 -1
119
120 } {
121 set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
122
123 # If $op2 groups more tightly than $op1, then the result
124 # of executing $sql1 whould be the same as executing $sql3.
125 # If $op1 groups more tightly, or if $op1 and $op2 have
126 # the same precedence, then executing $sql1 should return
127 # the same value as $sql2.
128 #
129 set sql1 "SELECT $A $op1 $B $op2 $C"
130 set sql2 "SELECT ($A $op1 $B) $op2 $C"
131 set sql3 "SELECT $A $op1 ($B $op2 $C)"
132
133 set a2 [db one $sql2]
134 set a3 [db one $sql3]
135
136 do_execsql_test $testname $sql1 [list [
137 if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}
138 ]]
139 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
140 }
141 }
142 }
143
144 foreach op {* AND OR + || & |} { unset untested($op,$op) }
145 unset untested(+,-) ;# Since (a+b)-c == a+(b-c)
146 unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c)
147
148 do_test e_expr-1.1 { array names untested } {}
149
150 # At one point, test 1.2.2 was failing. Instead of the correct result, it
151 # was returning {1 1 0}. This would seem to indicate that LIKE has the
152 # same precedence as '<'. Which is incorrect. It has lower precedence.
153 #
154 do_execsql_test e_expr-1.2.1 {
155 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1)
156 } {1 1 0}
157 do_execsql_test e_expr-1.2.2 {
158 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2)
159 } {0 1 0}
160
161 # Showing that LIKE and == have the same precedence
162 #
163 do_execsql_test e_expr-1.2.3 {
164 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1)
165 } {1 1 0}
166 do_execsql_test e_expr-1.2.4 {
167 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1)
168 } {1 1 0}
169
170 # Showing that < groups more tightly than == (< has higher precedence).
171 #
172 do_execsql_test e_expr-1.2.5 {
173 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1)
174 } {1 1 0}
175 do_execsql_test e_expr-1.6 {
176 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2)
177 } {0 1 0}
178
179 #-------------------------------------------------------------------------
180 # Check that the four unary prefix operators mentioned in the
181 # documentation exist.
182 #
183 # X-EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
184 # - + ~ NOT
185 #
186 do_execsql_test e_expr-2.1 { SELECT - 10 } {-10}
187 do_execsql_test e_expr-2.2 { SELECT + 10 } {10}
188 do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11}
189 do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0}
190
191 #-------------------------------------------------------------------------
192 # Tests for the two statements made regarding the unary + operator.
193 #
194 # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
195 #
196 # EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
197 # blobs or NULL and it always returns a result with the same value as
198 # the operand.
199 #
200 foreach {tn literal type} {
201 1 'helloworld' text
202 2 45 integer
203 3 45.2 real
204 4 45.0 real
205 5 X'ABCDEF' blob
206 6 NULL null
207 } {
208 set sql " SELECT quote( + $literal ), typeof( + $literal) "
209 do_execsql_test e_expr-3.$tn $sql [list $literal $type]
210 }
211
212 #-------------------------------------------------------------------------
213 # Check that both = and == are both acceptable as the "equals" operator.
214 # Similarly, either != or <> work as the not-equals operator.
215 #
216 # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
217 #
218 # EVIDENCE-OF: R-49372-18364 The not-equal operator can be either != or
219 # <>.
220 #
221 foreach {tn literal different} {
222 1 'helloworld' '12345'
223 2 22 23
224 3 'xyz' X'78797A'
225 4 X'78797A00' 'xyz'
226 } {
227 do_execsql_test e_expr-4.$tn "
228 SELECT $literal = $literal, $literal == $literal,
229 $literal = $different, $literal == $different,
230 $literal = NULL, $literal == NULL,
231 $literal != $literal, $literal <> $literal,
232 $literal != $different, $literal <> $different,
233 $literal != NULL, $literal != NULL
234
235 " {1 1 0 0 {} {} 0 0 1 1 {} {}}
236 }
237
238 #-------------------------------------------------------------------------
239 # Test the || operator.
240 #
241 # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
242 # together the two strings of its operands.
243 #
244 foreach {tn a b} {
245 1 'helloworld' '12345'
246 2 22 23
247 } {
248 set as [db one "SELECT $a"]
249 set bs [db one "SELECT $b"]
250
251 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
252 }
253
254 #-------------------------------------------------------------------------
255 # Test the % operator.
256 #
257 # EVIDENCE-OF: R-53431-59159 The % operator casts both of its operands
258 # to type INTEGER and then computes the remainder after dividing the
259 # left integer by the right integer.
260 #
261 do_execsql_test e_expr-6.1 {SELECT 72%5} {2}
262 do_execsql_test e_expr-6.2 {SELECT 72%-5} {2}
263 do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
264 do_execsql_test e_expr-6.4 {SELECT -72%5} {-2}
265 do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0}
266
267 #-------------------------------------------------------------------------
268 # EVIDENCE-OF: R-15904-00746 The result of any binary operator is either
269 # a numeric value or NULL, except for the || concatenation operator, and
270 # the -> and ->> extract operators which evaluate to either
271 # NULL or a text value.
272 #
273 set literals {
274 1 'abc' 2 'hexadecimal' 3 ''
275 4 123 5 -123 6 0
276 7 123.4 8 0.0 9 -123.4
277 10 X'ABCDEF' 11 X'' 12 X'0000'
278 13 NULL
279 }
280 foreach op $oplist {
281 if {$op eq "AND" || $op eq "OR"} {
282 # These tests do not work with AND and OR due to short-circuit evaluation
283 continue
284 }
285 foreach {n1 rhs} $literals {
286 foreach {n2 lhs} $literals {
287
288 set t [db one " SELECT typeof($lhs $op $rhs) "]
289 do_test e_expr-7.$opname($op).$n1.$n2 {
290 expr {
291 ($op=="||" && ($t == "text" || $t == "null"))
292 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
293 }
294 } 1
295
296 }}
297 }
298
299 #-------------------------------------------------------------------------
300 # Test the IS and IS NOT operators.
301 #
302 # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
303 # != except when one or both of the operands are NULL.
304 #
305 # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
306 # then the IS operator evaluates to 1 (true) and the IS NOT operator
307 # evaluates to 0 (false).
308 #
309 # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
310 # not, then the IS operator evaluates to 0 (false) and the IS NOT
311 # operator is 1 (true).
312 #
313 # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
314 # expression to evaluate to NULL.
315 #
316 do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1}
317 do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0}
318 do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0}
319 do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1}
320 do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}}
321 do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}}
322 do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}}
323 do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1}
324 do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0}
325 do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
326 do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
327 do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
328 do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}}
329 do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}}
330 do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}}
331 do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0}
332
333 foreach {n1 rhs} $literals {
334 foreach {n2 lhs} $literals {
335 if {$rhs!="NULL" && $lhs!="NULL"} {
336 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
337 } else {
338 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
339 [expr {$lhs!="NULL" || $rhs!="NULL"}]
340 ]
341 }
342 set test e_expr-8.2.$n1.$n2
343 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
344 do_execsql_test $test.2 "
345 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
346 " {0 0}
347 }
348 }
349
350 #-------------------------------------------------------------------------
351 # Run some tests on the COLLATE "unary postfix operator".
352 #
353 # This collation sequence reverses both arguments before using
354 # [string compare] to compare them. For example, when comparing the
355 # strings 'one' and 'four', return the result of:
356 #
357 # string compare eno ruof
358 #
359 proc reverse_str {zStr} {
360 set out ""
361 foreach c [split $zStr {}] { set out "${c}${out}" }
362 set out
363 }
364 proc reverse_collate {zLeft zRight} {
365 string compare [reverse_str $zLeft] [reverse_str $zRight]
366 }
367 db collate reverse reverse_collate
368
369 # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
370 # operator that assigns a collating sequence to an expression.
371 #
372 # X-EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher
373 # precedence (binds more tightly) than any binary operator and any unary
374 # prefix operator except "~".
375 #
376 do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0
377 do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1
378 do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0
379 do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1
380
381 do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1
382 do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0
383 do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1
384 do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0
385
386 do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1
387 do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0
388 do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1
389 do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
390 do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1
391 do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
392
393 do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0
394 do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1
395 do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0
396 do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1
397 do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0
398 do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
399
400 do_execsql_test e_expr-9.22 {
401 SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase
402 } 1
403 do_execsql_test e_expr-9.23 {
404 SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase
405 } 0
406
407 # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
408 # operator overrides the collating sequence determined by the COLLATE
409 # clause in a table column definition.
410 #
411 do_execsql_test e_expr-9.24 {
412 CREATE TABLE t24(a COLLATE NOCASE, b);
413 INSERT INTO t24 VALUES('aaa', 1);
414 INSERT INTO t24 VALUES('bbb', 2);
415 INSERT INTO t24 VALUES('ccc', 3);
416 } {}
417 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
418 do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
419 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
420 do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
421
422 #-------------------------------------------------------------------------
423 # Test statements related to literal values.
424 #
425 # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
426 # point numbers, strings, BLOBs, or NULLs.
427 #
428 do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer}
429 do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real}
430 do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text}
431 do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
432 do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null}
433
434 # "Scientific notation is supported for point literal values."
435 #
436 do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real}
437 do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real}
438 do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034}
439 do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0}
440
441 # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
442 # the string in single quotes (').
443 #
444 # EVIDENCE-OF: R-07100-06606 A single quote within the string can be
445 # encoded by putting two single quotes in a row - as in Pascal.
446 #
447 do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}}
448 do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
449 do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't}
450 do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
451
452 # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
453 # containing hexadecimal data and preceded by a single "x" or "X"
454 # character.
455 #
456 # EVIDENCE-OF: R-19836-11244 Example: X'53514C697465'
457 #
458 do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
459 do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
460 do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
461 do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
462 do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob
463
464 # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
465 # "NULL".
466 #
467 do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}}
468 do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
469
470 #-------------------------------------------------------------------------
471 # Test statements related to bound parameters
472 #
473
474 proc parameter_test {tn sql params result} {
475 set stmt [sqlite3_prepare_v2 db $sql -1]
476
477 foreach {number name} $params {
478 set nm [sqlite3_bind_parameter_name $stmt $number]
479 do_test $tn.name.$number [list set {} $nm] $name
480 sqlite3_bind_int $stmt $number [expr -1 * $number]
481 }
482
483 sqlite3_step $stmt
484
485 set res [list]
486 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
487 lappend res [sqlite3_column_text $stmt $i]
488 }
489
490 set rc [sqlite3_finalize $stmt]
491 do_test $tn.rc [list set {} $rc] SQLITE_OK
492 do_test $tn.res [list set {} $res] $result
493 }
494
495 # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
496 # holds a spot for the NNN-th parameter. NNN must be between 1 and
497 # SQLITE_MAX_VARIABLE_NUMBER.
498 #
499 set mvn $SQLITE_MAX_VARIABLE_NUMBER
500 parameter_test e_expr-11.1 "
501 SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
502 " "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4"
503
504 set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
505 foreach {tn param_number} [list \
506 2 0 \
507 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
508 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
509 5 12345678903456789034567890234567890 \
510 6 2147483648 \
511 7 2147483649 \
512 8 4294967296 \
513 9 4294967297 \
514 10 9223372036854775808 \
515 11 9223372036854775809 \
516 12 18446744073709551616 \
517 13 18446744073709551617 \
518 ] {
519 do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
520 }
521
522 # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
523 # number creates a parameter with a number one greater than the largest
524 # parameter number already assigned.
525 #
526 # EVIDENCE-OF: R-42938-07030 If this means the parameter number is
527 # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
528 #
529 parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1
530 parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2}
531 parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6}
532 parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5}
533 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
534 1 {} 456 ?456 457 {}
535 } {-1 -456 -457}
536 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
537 1 {} 456 ?456 4 ?4 457 {}
538 } {-1 -456 -4 -457}
539 foreach {tn sql} [list \
540 1 "SELECT ?$mvn, ?" \
541 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \
542 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \
543 ] {
544 do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
545 }
546
547 # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
548 # holds a spot for a named parameter with the name :AAAA.
549 #
550 # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
551 # and any UTF characters with codepoints larger than 127 (non-ASCII
552 # characters).
553 #
554 parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1
555 parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1
556 parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1
557 parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1
558 parameter_test e_expr-11.2.5 "
559 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
560 " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
561 parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
562
563 # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
564 # except that the name of the parameter created is @AAAA.
565 #
566 parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1
567 parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1
568 parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1
569 parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1
570 parameter_test e_expr-11.3.5 "
571 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
572 " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
573 parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
574
575 # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
576 # name also holds a spot for a named parameter with the name $AAAA.
577 #
578 # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
579 # include one or more occurrences of "::" and a suffix enclosed in
580 # "(...)" containing any text at all.
581 #
582 # Note: Looks like an identifier cannot consist entirely of "::"
583 # characters or just a suffix. Also, the other named variable characters
584 # (: and @) work the same way internally. Why not just document it that way?
585 #
586 parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1
587 parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1
588 parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1
589 parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1
590 parameter_test e_expr-11.4.5 "
591 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
592 " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
593 parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
594
595 parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
596 parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
597 parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
598
599 # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
600 # number assigned is one greater than the largest parameter number
601 # already assigned.
602 #
603 # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
604 # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
605 # error.
606 #
607 parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}
608 parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}
609 parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
610 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
611 } {-1 -8 -9 -10 -2 -11}
612 foreach {tn sql} [list \
613 1 "SELECT ?$mvn, \$::a" \
614 2 "SELECT ?$mvn, ?4, @a1" \
615 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \
616 ] {
617 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
618 }
619
620 # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
621 # using sqlite3_bind() are treated as NULL.
622 #
623 do_test e_expr-11.7.1 {
624 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
625 sqlite3_step $stmt
626
627 list [sqlite3_column_type $stmt 0] \
628 [sqlite3_column_type $stmt 1] \
629 [sqlite3_column_type $stmt 2] \
630 [sqlite3_column_type $stmt 3]
631 } {NULL NULL NULL NULL}
632 do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
633
634 #-------------------------------------------------------------------------
635 # "Test" the syntax diagrams in lang_expr.html.
636 #
637 # -- syntax diagram signed-number
638 #
639 do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
640 do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
641 do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
642 do_execsql_test e_expr-12.1.4 {
643 SELECT 1.4, +1.4, -1.4
644 } {1.4 1.4 -1.4}
645 do_execsql_test e_expr-12.1.5 {
646 SELECT 1.5e+5, +1.5e+5, -1.5e+5
647 } {150000.0 150000.0 -150000.0}
648 do_execsql_test e_expr-12.1.6 {
649 SELECT 0.0001, +0.0001, -0.0001
650 } {0.0001 0.0001 -0.0001}
651
652 # -- syntax diagram literal-value
653 #
654 set sqlite_current_time 1
655 do_execsql_test e_expr-12.2.1 {SELECT 123} {123}
656 do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}
657 do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}
658 do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}
659 do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}
660 do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}
661 do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}
662 do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
663 set sqlite_current_time 0
664
665 # -- syntax diagram expr
666 #
667 forcedelete test.db2
668 execsql {
669 ATTACH 'test.db2' AS dbname;
670 CREATE TABLE dbname.tblname(cname);
671 }
672
673 proc glob {args} {return 1}
674 db function glob glob
675 db function match glob
676 db function regexp glob
677
678 foreach {tn expr} {
679 1 123
680 2 123.4e05
681 3 'abcde'
682 4 X'414243'
683 5 NULL
684 6 CURRENT_TIME
685 7 CURRENT_DATE
686 8 CURRENT_TIMESTAMP
687
688 9 ?
689 10 ?123
690 11 @hello
691 12 :world
692 13 $tcl
693 14 $tcl(array)
694
695 15 cname
696 16 tblname.cname
697 17 dbname.tblname.cname
698
699 18 "+ EXPR"
700 19 "- EXPR"
701 20 "NOT EXPR"
702 21 "~ EXPR"
703
704 22 "EXPR1 || EXPR2"
705 23 "EXPR1 * EXPR2"
706 24 "EXPR1 / EXPR2"
707 25 "EXPR1 % EXPR2"
708 26 "EXPR1 + EXPR2"
709 27 "EXPR1 - EXPR2"
710 28 "EXPR1 << EXPR2"
711 29 "EXPR1 >> EXPR2"
712 30 "EXPR1 & EXPR2"
713 31 "EXPR1 | EXPR2"
714 32 "EXPR1 < EXPR2"
715 33 "EXPR1 <= EXPR2"
716 34 "EXPR1 > EXPR2"
717 35 "EXPR1 >= EXPR2"
718 36 "EXPR1 = EXPR2"
719 37 "EXPR1 == EXPR2"
720 38 "EXPR1 != EXPR2"
721 39 "EXPR1 <> EXPR2"
722 40 "EXPR1 IS EXPR2"
723 41 "EXPR1 IS NOT EXPR2"
724 42 "EXPR1 AND EXPR2"
725 43 "EXPR1 OR EXPR2"
726
727 44 "count(*)"
728 45 "count(DISTINCT EXPR)"
729 46 "substr(EXPR, 10, 20)"
730 47 "changes()"
731
732 48 "( EXPR )"
733
734 49 "CAST ( EXPR AS integer )"
735 50 "CAST ( EXPR AS 'abcd' )"
736 51 "CAST ( EXPR AS 'ab$ $cd' )"
737
738 52 "EXPR COLLATE nocase"
739 53 "EXPR COLLATE binary"
740
741 54 "EXPR1 LIKE EXPR2"
742 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
743 56 "EXPR1 GLOB EXPR2"
744 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
745 58 "EXPR1 REGEXP EXPR2"
746 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
747 60 "EXPR1 MATCH EXPR2"
748 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
749 62 "EXPR1 NOT LIKE EXPR2"
750 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
751 64 "EXPR1 NOT GLOB EXPR2"
752 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
753 66 "EXPR1 NOT REGEXP EXPR2"
754 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
755 68 "EXPR1 NOT MATCH EXPR2"
756 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
757
758 70 "EXPR ISNULL"
759 71 "EXPR NOTNULL"
760 72 "EXPR NOT NULL"
761
762 73 "EXPR1 IS EXPR2"
763 74 "EXPR1 IS NOT EXPR2"
764
765 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
766 76 "EXPR BETWEEN EXPR1 AND EXPR2"
767
768 77 "EXPR NOT IN (SELECT cname FROM tblname)"
769 78 "EXPR NOT IN (1)"
770 79 "EXPR NOT IN (1, 2, 3)"
771 80 "EXPR NOT IN tblname"
772 81 "EXPR NOT IN dbname.tblname"
773 82 "EXPR IN (SELECT cname FROM tblname)"
774 83 "EXPR IN (1)"
775 84 "EXPR IN (1, 2, 3)"
776 85 "EXPR IN tblname"
777 86 "EXPR IN dbname.tblname"
778
779 87 "EXISTS (SELECT cname FROM tblname)"
780 88 "NOT EXISTS (SELECT cname FROM tblname)"
781
782 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
783 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
784 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
785 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
786 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
787 94 "CASE WHEN EXPR1 THEN EXPR2 END"
788 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
789 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
790 } {
791
792 # If the expression string being parsed contains "EXPR2", then replace
793 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it
794 # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
795 #
796 set elist [list $expr]
797 if {[string match *EXPR2* $expr]} {
798 set elist [list]
799 foreach {e1 e2} { cname "34+22" } {
800 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
801 }
802 }
803 if {[string match *EXPR* $expr]} {
804 set elist2 [list]
805 foreach el $elist {
806 foreach e { cname "34+22" } {
807 lappend elist2 [string map [list EXPR $e] $el]
808 }
809 }
810 set elist $elist2
811 }
812
813 set x 0
814 foreach e $elist {
815 incr x
816 do_test e_expr-12.3.$tn.$x {
817 set rc [catch { execsql "SELECT $e FROM tblname" } msg]
818 } {0}
819 }
820 }
821
822 # -- syntax diagram raise-function
823 #
824 foreach {tn raiseexpr} {
825 1 "RAISE(IGNORE)"
826 2 "RAISE(ROLLBACK, 'error message')"
827 3 "RAISE(ABORT, 'error message')"
828 4 "RAISE(FAIL, 'error message')"
829 } {
830 do_execsql_test e_expr-12.4.$tn "
831 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
832 SELECT $raiseexpr ;
833 END;
834 " {}
835 }
836
837 #-------------------------------------------------------------------------
838 # Test the statements related to the BETWEEN operator.
839 #
840 # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
841 # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
842 # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
843 # only evaluated once.
844 #
845 db func x x
846 proc x {} { incr ::xcount ; return [expr $::x] }
847 foreach {tn x expr res nEval} {
848 1 10 "x() >= 5 AND x() <= 15" 1 2
849 2 10 "x() BETWEEN 5 AND 15" 1 1
850
851 3 5 "x() >= 5 AND x() <= 5" 1 2
852 4 5 "x() BETWEEN 5 AND 5" 1 1
853
854 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2
855 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1
856 } {
857 do_test e_expr-13.1.$tn {
858 set ::xcount 0
859 set a [execsql "SELECT $expr"]
860 list $::xcount $a
861 } [list $nEval $res]
862 }
863
864 # X-EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
865 # the same as the precedence as operators == and != and LIKE and groups
866 # left to right.
867 #
868 # Therefore, BETWEEN groups more tightly than operator "AND", but less
869 # so than "<".
870 #
871 do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1
872 do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1
873 do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0
874 do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1
875 do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1
876 do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0
877
878 do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1
879 do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1
880 do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0
881 do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1
882 do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1
883 do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0
884
885 do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1
886 do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1
887 do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0
888 do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1
889 do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1
890 do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0
891
892 do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0
893 do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
894 do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
895 do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0
896 do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
897 do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
898
899 do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1
900 do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
901 do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
902 do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0
903 do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0
904 do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1
905
906 #-------------------------------------------------------------------------
907 # Test the statements related to the LIKE and GLOB operators.
908 #
909 # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
910 # comparison.
911 #
912 # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
913 # operator contains the pattern and the left hand operand contains the
914 # string to match against the pattern.
915 #
916 do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
917 do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
918
919 # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
920 # matches any sequence of zero or more characters in the string.
921 #
922 do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1
923 do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1
924 do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
925
926 # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
927 # matches any single character in the string.
928 #
929 do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0
930 do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1
931 do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
932
933 # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
934 # lower/upper case equivalent (i.e. case-insensitive matching).
935 #
936 do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
937 do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
938 do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0
939
940 # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
941 # for ASCII characters by default.
942 #
943 # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
944 # default for unicode characters that are beyond the ASCII range.
945 #
946 # EVIDENCE-OF: R-44381-11669 the expression
947 # 'a'&nbsp;LIKE&nbsp;'A' is TRUE but
948 # '&aelig;'&nbsp;LIKE&nbsp;'&AElig;' is FALSE.
949 #
950 # The restriction to ASCII characters does not apply if the ICU
951 # library is compiled in. When ICU is enabled SQLite does not act
952 # as it does "by default".
953 #
954 do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1
955 ifcapable !icu {
956 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
957 }
958
959 # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
960 # then the expression following the ESCAPE keyword must evaluate to a
961 # string consisting of a single character.
962 #
963 do_catchsql_test e_expr-14.6.1 {
964 SELECT 'A' LIKE 'a' ESCAPE '12'
965 } {1 {ESCAPE expression must be a single character}}
966 do_catchsql_test e_expr-14.6.2 {
967 SELECT 'A' LIKE 'a' ESCAPE ''
968 } {1 {ESCAPE expression must be a single character}}
969 do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1}
970 do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
971
972 # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
973 # pattern to include literal percent or underscore characters.
974 #
975 # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
976 # symbol (%), underscore (_), or a second instance of the escape
977 # character itself matches a literal percent symbol, underscore, or a
978 # single escape character, respectively.
979 #
980 do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1
981 do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0
982 do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0
983 do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
984 do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
985
986 do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1
987 do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0
988 do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0
989 do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
990 do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
991
992 do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1
993 do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0
994 do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0
995 do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
996
997 # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
998 # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
999 #
1000 proc likefunc {args} {
1001 eval lappend ::likeargs $args
1002 return 1
1003 }
1004 db func like -argcount 2 likefunc
1005 db func like -argcount 3 likefunc
1006 set ::likeargs [list]
1007 do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
1008 do_test e_expr-15.1.2 { set likeargs } {def abc}
1009 set ::likeargs [list]
1010 do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
1011 do_test e_expr-15.1.4 { set likeargs } {def abc X}
1012 db close
1013 sqlite3 db test.db
1014
1015 # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
1016 # sensitive using the case_sensitive_like pragma.
1017 #
1018 do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1019 do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
1020 do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}
1021 do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
1022 do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0
1023 do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1024 do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
1025 do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
1026 do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1027 do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
1028 do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1029 do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
1030
1031 # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
1032 # uses the Unix file globbing syntax for its wildcards.
1033 #
1034 # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
1035 #
1036 do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
1037 do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
1038 do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
1039 do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
1040
1041 do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
1042 do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
1043 do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
1044
1045 # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
1046 # NOT keyword to invert the sense of the test.
1047 #
1048 do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
1049 do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
1050 do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
1051 do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
1052 do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
1053
1054 db nullvalue null
1055 do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
1056 do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
1057 do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
1058 do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
1059 db nullvalue {}
1060
1061 # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
1062 # calling the function glob(Y,X) and can be modified by overriding that
1063 # function.
1064 proc globfunc {args} {
1065 eval lappend ::globargs $args
1066 return 1
1067 }
1068 db func glob -argcount 2 globfunc
1069 set ::globargs [list]
1070 do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
1071 do_test e_expr-17.3.2 { set globargs } {def abc}
1072 set ::globargs [list]
1073 do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
1074 do_test e_expr-17.3.4 { set globargs } {Y X}
1075 sqlite3 db test.db
1076
1077 # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
1078 # default and so use of the REGEXP operator will normally result in an
1079 # error message.
1080 #
1081 # There is a regexp function if ICU is enabled though.
1082 #
1083 ifcapable !icu {
1084 do_catchsql_test e_expr-18.1.1 {
1085 SELECT regexp('abc', 'def')
1086 } {1 {no such function: regexp}}
1087 do_catchsql_test e_expr-18.1.2 {
1088 SELECT 'abc' REGEXP 'def'
1089 } {1 {no such function: REGEXP}}
1090 }
1091
1092 # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
1093 # the regexp() user function.
1094 #
1095 # EVIDENCE-OF: R-65524-61849 If an application-defined SQL function
1096 # named "regexp" is added at run-time, then the "X REGEXP Y" operator
1097 # will be implemented as a call to "regexp(Y,X)".
1098 #
1099 proc regexpfunc {args} {
1100 eval lappend ::regexpargs $args
1101 return 1
1102 }
1103 db func regexp -argcount 2 regexpfunc
1104 set ::regexpargs [list]
1105 do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
1106 do_test e_expr-18.2.2 { set regexpargs } {def abc}
1107 set ::regexpargs [list]
1108 do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
1109 do_test e_expr-18.2.4 { set regexpargs } {Y X}
1110 sqlite3 db test.db
1111
1112 # EVIDENCE-OF: R-42037-37826 The default match() function implementation
1113 # raises an exception and is not really useful for anything.
1114 #
1115 do_catchsql_test e_expr-19.1.1 {
1116 SELECT 'abc' MATCH 'def'
1117 } {1 {unable to use function MATCH in the requested context}}
1118 do_catchsql_test e_expr-19.1.2 {
1119 SELECT match('abc', 'def')
1120 } {1 {unable to use function MATCH in the requested context}}
1121
1122 # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
1123 # the match() application-defined function.
1124 #
1125 # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
1126 # function with more helpful logic.
1127 #
1128 proc matchfunc {args} {
1129 eval lappend ::matchargs $args
1130 return 1
1131 }
1132 db func match -argcount 2 matchfunc
1133 set ::matchargs [list]
1134 do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
1135 do_test e_expr-19.2.2 { set matchargs } {def abc}
1136 set ::matchargs [list]
1137 do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
1138 do_test e_expr-19.2.4 { set matchargs } {Y X}
1139 sqlite3 db test.db
1140
1141 #-------------------------------------------------------------------------
1142 # Test cases for the testable statements related to the CASE expression.
1143 #
1144 # EVIDENCE-OF: R-57495-24088 There are two fundamental forms of the CASE
1145 # expression: those with a base expression and those without.
1146 #
1147 do_execsql_test e_expr-20.1 {
1148 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1149 } {true}
1150 do_execsql_test e_expr-20.2 {
1151 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1152 } {false}
1153
1154 proc var {nm} {
1155 lappend ::varlist $nm
1156 return [set "::$nm"]
1157 }
1158 db func var var
1159
1160 # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
1161 # WHEN expression is evaluated and the result treated as a boolean,
1162 # starting with the leftmost and continuing to the right.
1163 #
1164 foreach {a b c} {0 0 0} break
1165 set varlist [list]
1166 do_execsql_test e_expr-21.1.1 {
1167 SELECT CASE WHEN var('a') THEN 'A'
1168 WHEN var('b') THEN 'B'
1169 WHEN var('c') THEN 'C' END
1170 } {{}}
1171 do_test e_expr-21.1.2 { set varlist } {a b c}
1172 set varlist [list]
1173 do_execsql_test e_expr-21.1.3 {
1174 SELECT CASE WHEN var('c') THEN 'C'
1175 WHEN var('b') THEN 'B'
1176 WHEN var('a') THEN 'A'
1177 ELSE 'no result'
1178 END
1179 } {{no result}}
1180 do_test e_expr-21.1.4 { set varlist } {c b a}
1181
1182 # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
1183 # evaluation of the THEN expression that corresponds to the first WHEN
1184 # expression that evaluates to true.
1185 #
1186 foreach {a b c} {0 1 0} break
1187 do_execsql_test e_expr-21.2.1 {
1188 SELECT CASE WHEN var('a') THEN 'A'
1189 WHEN var('b') THEN 'B'
1190 WHEN var('c') THEN 'C'
1191 ELSE 'no result'
1192 END
1193 } {B}
1194 foreach {a b c} {0 1 1} break
1195 do_execsql_test e_expr-21.2.2 {
1196 SELECT CASE WHEN var('a') THEN 'A'
1197 WHEN var('b') THEN 'B'
1198 WHEN var('c') THEN 'C'
1199 ELSE 'no result'
1200 END
1201 } {B}
1202 foreach {a b c} {0 0 1} break
1203 do_execsql_test e_expr-21.2.3 {
1204 SELECT CASE WHEN var('a') THEN 'A'
1205 WHEN var('b') THEN 'B'
1206 WHEN var('c') THEN 'C'
1207 ELSE 'no result'
1208 END
1209 } {C}
1210
1211 # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
1212 # evaluate to true, the result of evaluating the ELSE expression, if
1213 # any.
1214 #
1215 foreach {a b c} {0 0 0} break
1216 do_execsql_test e_expr-21.3.1 {
1217 SELECT CASE WHEN var('a') THEN 'A'
1218 WHEN var('b') THEN 'B'
1219 WHEN var('c') THEN 'C'
1220 ELSE 'no result'
1221 END
1222 } {{no result}}
1223
1224 # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
1225 # the WHEN expressions are true, then the overall result is NULL.
1226 #
1227 db nullvalue null
1228 do_execsql_test e_expr-21.3.2 {
1229 SELECT CASE WHEN var('a') THEN 'A'
1230 WHEN var('b') THEN 'B'
1231 WHEN var('c') THEN 'C'
1232 END
1233 } {null}
1234 db nullvalue {}
1235
1236 # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
1237 # evaluating WHEN terms.
1238 #
1239 do_execsql_test e_expr-21.4.1a {
1240 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END, iif(NULL,8,99);
1241 } {B 99}
1242 do_execsql_test e_expr-21.4.1b {
1243 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END, if(NULL,8,99);
1244 } {B 99}
1245 do_execsql_test e_expr-21.4.2a {
1246 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, iif(0,8,99);
1247 } {C 99}
1248 do_execsql_test e_expr-21.4.2b {
1249 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, if(0,8,99);
1250 } {C 99}
1251
1252 # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
1253 # expression is evaluated just once and the result is compared against
1254 # the evaluation of each WHEN expression from left to right.
1255 #
1256 # Note: This test case tests the "evaluated just once" part of the above
1257 # statement. Tests associated with the next two statements test that the
1258 # comparisons take place.
1259 #
1260 foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
1261 set ::varlist [list]
1262 do_execsql_test e_expr-22.1.1 {
1263 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
1264 } {C}
1265 do_test e_expr-22.1.2 { set ::varlist } {a}
1266
1267 # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
1268 # evaluation of the THEN expression that corresponds to the first WHEN
1269 # expression for which the comparison is true.
1270 #
1271 do_execsql_test e_expr-22.2.1 {
1272 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1273 } {B}
1274 do_execsql_test e_expr-22.2.2 {
1275 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1276 } {A}
1277
1278 # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
1279 # evaluate to a value equal to the base expression, the result of
1280 # evaluating the ELSE expression, if any.
1281 #
1282 do_execsql_test e_expr-22.3.1 {
1283 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
1284 } {D}
1285
1286 # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
1287 # the WHEN expressions produce a result equal to the base expression,
1288 # the overall result is NULL.
1289 #
1290 do_execsql_test e_expr-22.4.1 {
1291 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1292 } {{}}
1293 db nullvalue null
1294 do_execsql_test e_expr-22.4.2 {
1295 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1296 } {null}
1297 db nullvalue {}
1298
1299 # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
1300 # WHEN expression, the same collating sequence, affinity, and
1301 # NULL-handling rules apply as if the base expression and WHEN
1302 # expression are respectively the left- and right-hand operands of an =
1303 # operator.
1304 #
1305 proc rev {str} {
1306 set ret ""
1307 set chars [split $str]
1308 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
1309 append ret [lindex $chars $i]
1310 }
1311 set ret
1312 }
1313 proc reverse {lhs rhs} {
1314 string compare [rev $lhs] [rev $rhs]
1315 }
1316 db collate reverse reverse
1317 do_execsql_test e_expr-23.1.1 {
1318 CREATE TABLE t1(
1319 a TEXT COLLATE NOCASE,
1320 b COLLATE REVERSE,
1321 c INTEGER,
1322 d BLOB
1323 );
1324 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
1325 } {}
1326 do_execsql_test e_expr-23.1.2 {
1327 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
1328 } {B}
1329 do_execsql_test e_expr-23.1.3 {
1330 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
1331 } {B}
1332 do_execsql_test e_expr-23.1.4 {
1333 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
1334 } {B}
1335 do_execsql_test e_expr-23.1.5 {
1336 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
1337 } {B}
1338 do_execsql_test e_expr-23.1.6 {
1339 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
1340 } {B}
1341 do_execsql_test e_expr-23.1.7 {
1342 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
1343 } {A}
1344 do_execsql_test e_expr-23.1.8 {
1345 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
1346 } {B}
1347 do_execsql_test e_expr-23.1.9 {
1348 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
1349 } {B}
1350
1351 # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
1352 # result of the CASE is always the result of evaluating the ELSE
1353 # expression if it exists, or NULL if it does not.
1354 #
1355 do_execsql_test e_expr-24.1.1 {
1356 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
1357 } {{}}
1358 do_execsql_test e_expr-24.1.2 {
1359 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
1360 } {C}
1361
1362 # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
1363 # or short-circuit, evaluation.
1364 #
1365 set varlist [list]
1366 foreach {a b c} {0 1 0} break
1367 do_execsql_test e_expr-25.1.1 {
1368 SELECT CASE WHEN var('a') THEN 'A'
1369 WHEN var('b') THEN 'B'
1370 WHEN var('c') THEN 'C'
1371 END
1372 } {B}
1373 do_test e_expr-25.1.2 { set ::varlist } {a b}
1374 set varlist [list]
1375 do_execsql_test e_expr-25.1.3 {
1376 SELECT CASE '0' WHEN var('a') THEN 'A'
1377 WHEN var('b') THEN 'B'
1378 WHEN var('c') THEN 'C'
1379 END
1380 } {A}
1381 do_test e_expr-25.1.4 { set ::varlist } {a}
1382
1383 # EVIDENCE-OF: R-34773-62253 The only difference between the following
1384 # two CASE expressions is that the x expression is evaluated exactly
1385 # once in the first example but might be evaluated multiple times in the
1386 # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
1387 # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
1388 #
1389 proc ceval {x} {
1390 incr ::evalcount
1391 return $x
1392 }
1393 db func ceval ceval
1394 set ::evalcount 0
1395
1396 do_execsql_test e_expr-26.1.1 {
1397 CREATE TABLE t2(x, w1, r1, w2, r2, r3);
1398 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
1399 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
1400 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
1401 } {}
1402 do_execsql_test e_expr-26.1.2 {
1403 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1404 } {R1 R2 R3}
1405 do_execsql_test e_expr-26.1.3 {
1406 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
1407 } {R1 R2 R3}
1408
1409 do_execsql_test e_expr-26.1.4 {
1410 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1411 } {R1 R2 R3}
1412 do_test e_expr-26.1.5 { set ::evalcount } {3}
1413 set ::evalcount 0
1414 do_execsql_test e_expr-26.1.6 {
1415 SELECT CASE
1416 WHEN ceval(x)=w1 THEN r1
1417 WHEN ceval(x)=w2 THEN r2
1418 ELSE r3 END
1419 FROM t2
1420 } {R1 R2 R3}
1421 do_test e_expr-26.1.6 { set ::evalcount } {5}
1422
1423
1424 #-------------------------------------------------------------------------
1425 # Test statements related to CAST expressions.
1426 #
1427 # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
1428 # conversion that takes place when a column affinity is applied to a
1429 # value except that with the CAST operator the conversion always takes
1430 # place even if the conversion lossy and irreversible, whereas column
1431 # affinity only changes the data type of a value if the change is
1432 # lossless and reversible.
1433 #
1434 do_execsql_test e_expr-27.1.1 {
1435 CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
1436 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
1437 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
1438 } {blob UVU text 1.23abc real 4.5}
1439 do_execsql_test e_expr-27.1.2 {
1440 SELECT
1441 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
1442 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
1443 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
1444 } {text UVU real 1.23 integer 4}
1445
1446 # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
1447 # result of the CAST expression is also NULL.
1448 #
1449 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
1450 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {}
1451 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {}
1452 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {}
1453
1454 # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
1455 # is determined by applying the rules for determining column affinity to
1456 # the type-name.
1457 #
1458 # The R-29283-15561 requirement above is demonstrated by all of the
1459 # subsequent e_expr-26 tests.
1460 #
1461 # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
1462 # affinity causes the value to be converted into a BLOB.
1463 #
1464 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc
1465 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
1466 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi
1467
1468 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
1469 # the value to TEXT in the encoding of the database connection, then
1470 # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
1471 #
1472 do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
1473 do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536'
1474 do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738'
1475 rename db db2
1476 sqlite3 db :memory:
1477 ifcapable {utf16} {
1478 db eval { PRAGMA encoding = 'utf-16le' }
1479 do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
1480 do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600'
1481 do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800'
1482 }
1483 db close
1484 sqlite3 db :memory:
1485 db eval { PRAGMA encoding = 'utf-16be' }
1486 ifcapable {utf16} {
1487 do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
1488 do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036'
1489 do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038'
1490 }
1491 db close
1492 rename db2 db
1493
1494 # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
1495 # of bytes that make up the BLOB is interpreted as text encoded using
1496 # the database encoding.
1497 #
1498 do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
1499 do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
1500 rename db db2
1501 sqlite3 db :memory:
1502 db eval { PRAGMA encoding = 'utf-16le' }
1503 ifcapable {utf16} {
1504 do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
1505 do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
1506 }
1507 db close
1508 rename db2 db
1509
1510 # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
1511 # renders the value as if via sqlite3_snprintf() except that the
1512 # resulting TEXT uses the encoding of the database connection.
1513 #
1514 do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1
1515 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45
1516 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45
1517 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8
1518 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0
1519 do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
1520 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0
1521 do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0
1522
1523 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
1524 # value is first converted to TEXT.
1525 #
1526 do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
1527 do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
1528 do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
1529 do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
1530 rename db db2
1531 sqlite3 db :memory:
1532 ifcapable {utf16} {
1533 db eval { PRAGMA encoding = 'utf-16le' }
1534 do_expr_test e_expr-29.1.5 {
1535 CAST (X'31002E0032003300' AS REAL) } real 1.23
1536 do_expr_test e_expr-29.1.6 {
1537 CAST (X'3200330030002E003000' AS REAL) } real 230.0
1538 do_expr_test e_expr-29.1.7 {
1539 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
1540 do_expr_test e_expr-29.1.8 {
1541 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
1542 }
1543 db close
1544 rename db2 db
1545
1546 # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
1547 # longest possible prefix of the value that can be interpreted as a real
1548 # number is extracted from the TEXT value and the remainder ignored.
1549 #
1550 do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
1551 do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
1552 do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
1553 do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
1554
1555 # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
1556 # ignored when converging from TEXT to REAL.
1557 #
1558 do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
1559 do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45
1560 do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212
1561 do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
1562
1563 # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
1564 # interpreted as a real number, the result of the conversion is 0.0.
1565 #
1566 do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
1567 do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
1568 do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
1569
1570 # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
1571 # value is first converted to TEXT.
1572 #
1573 do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
1574 do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
1575 do_expr_test e_expr-30.1.3 {
1576 CAST(X'31303030303030' AS INTEGER)
1577 } integer 1000000
1578 do_expr_test e_expr-30.1.4 {
1579 CAST(X'2D31313235383939393036383432363234' AS INTEGER)
1580 } integer -1125899906842624
1581
1582 rename db db2
1583 sqlite3 db :memory:
1584 ifcapable {utf16} {
1585 execsql { PRAGMA encoding = 'utf-16be' }
1586 do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
1587 do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
1588 do_expr_test e_expr-30.1.7 {
1589 CAST(X'0031003000300030003000300030' AS INTEGER)
1590 } integer 1000000
1591 do_expr_test e_expr-30.1.8 {
1592 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER)
1593 } integer -1125899906842624
1594 }
1595 db close
1596 rename db2 db
1597
1598 # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
1599 # longest possible prefix of the value that can be interpreted as an
1600 # integer number is extracted from the TEXT value and the remainder
1601 # ignored.
1602 #
1603 do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
1604 do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
1605 do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
1606 do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
1607
1608 # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
1609 # converting from TEXT to INTEGER are ignored.
1610 #
1611 do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123
1612 do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523
1613 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
1614 do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1
1615
1616 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
1617 # interpreted as an integer number, the result of the conversion is 0.
1618 #
1619 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
1620 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
1621 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
1622
1623 # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
1624 # integers only &mdash; conversion of hexadecimal integers stops at
1625 # the "x" in the "0x" prefix of the hexadecimal integer string and thus
1626 # result of the CAST is always zero.
1627 do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
1628 do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
1629
1630 # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
1631 # results in the integer between the REAL value and zero that is closest
1632 # to the REAL value.
1633 #
1634 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
1635 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
1636 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
1637 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
1638
1639 # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
1640 # possible signed integer (+9223372036854775807) then the result is the
1641 # greatest possible signed integer and if the REAL is less than the
1642 # least possible signed integer (-9223372036854775808) then the result
1643 # is the least possible signed integer.
1644 #
1645 do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
1646 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
1647 do_expr_test e_expr-31.2.3 {
1648 CAST(-9223372036854775809.0 AS INT)
1649 } integer -9223372036854775808
1650 do_expr_test e_expr-31.2.4 {
1651 CAST(9223372036854775809.0 AS INT)
1652 } integer 9223372036854775807
1653
1654
1655 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
1656 # yields either an INTEGER or a REAL result.
1657 #
1658 # EVIDENCE-OF: R-48945-04866 If the input text looks like an integer
1659 # (there is no decimal point nor exponent) and the value is small enough
1660 # to fit in a 64-bit signed integer, then the result will be INTEGER.
1661 #
1662 # EVIDENCE-OF: R-47045-23194 Input text that looks like floating point
1663 # (there is a decimal point and/or an exponent) and the text describes a
1664 # value that can be losslessly converted back and forth between IEEE 754
1665 # 64-bit float and a 51-bit signed integer, then the result is INTEGER.
1666 #
1667 do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45
1668 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45
1669 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2
1670 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
1671 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
1672 do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000
1673 do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000
1674 do_test e_expr-32.1.8 {
1675 set expr {CAST( '9.223372036e15' AS NUMERIC)}
1676 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
1677 list $type $value
1678 } {real 9.22337e+15}
1679 do_test e_expr-32.1.9 {
1680 set expr {CAST('-9.223372036e15' AS NUMERIC)}
1681 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
1682 list $type $value
1683 } {real -9.22337e+15}
1684
1685 # EVIDENCE-OF: R-50300-26941 Any text input that describes a value
1686 # outside the range of a 64-bit signed integer yields a REAL result.
1687 #
1688 do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \
1689 integer 9223372036854775807
1690 do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \
1691 real 9.22337203685478e+18
1692 do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \
1693 integer -9223372036854775808
1694 do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \
1695 real -9.22337203685478e+18
1696
1697 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
1698 # is a no-op, even if a real value could be losslessly converted to an
1699 # integer.
1700 #
1701 do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
1702 do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
1703
1704 do_expr_test e_expr-32.2.3 {
1705 CAST(-9223372036854775808 AS NUMERIC)
1706 } integer -9223372036854775808
1707 do_expr_test e_expr-32.2.4 {
1708 CAST(9223372036854775807 AS NUMERIC)
1709 } integer 9223372036854775807
1710 do_expr_test e_expr-32.2.5 {
1711 CAST('9223372036854775807 ' AS NUMERIC)
1712 } integer 9223372036854775807
1713 do_expr_test e_expr-32.2.6 {
1714 CAST(' 9223372036854775807 ' AS NUMERIC)
1715 } integer 9223372036854775807
1716 do_expr_test e_expr-32.2.7 {
1717 CAST(' ' AS NUMERIC)
1718 } integer 0
1719 do_execsql_test e_expr-32.2.8 {
1720 WITH t1(x) AS (VALUES
1721 ('9000000000000000001'),
1722 ('9000000000000000001x'),
1723 ('9000000000000000001 '),
1724 (' 9000000000000000001 '),
1725 (' 9000000000000000001'),
1726 (' 9000000000000000001.'),
1727 ('9223372036854775807'),
1728 ('9223372036854775807 '),
1729 (' 9223372036854775807 '),
1730 ('9223372036854775808'),
1731 (' 9223372036854775808 '),
1732 ('9223372036854775807.0'),
1733 ('9223372036854775807e+0'),
1734 ('-5.0'),
1735 ('-5e+0'))
1736 SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
1737 } [list \
1738 integer 9000000000000000001 \
1739 integer 9000000000000000001 \
1740 integer 9000000000000000001 \
1741 integer 9000000000000000001 \
1742 integer 9000000000000000001 \
1743 real 9.0e+18 \
1744 integer 9223372036854775807 \
1745 integer 9223372036854775807 \
1746 integer 9223372036854775807 \
1747 real 9.2233720368547758e+18 \
1748 real 9.2233720368547758e+18 \
1749 real 9.2233720368547758e+18 \
1750 real 9.2233720368547758e+18 \
1751 integer -5 \
1752 integer -5 \
1753 ]
1754
1755 # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
1756 # non-BLOB value into a BLOB and the result from casting any BLOB value
1757 # into a non-BLOB value may be different depending on whether the
1758 # database encoding is UTF-8, UTF-16be, or UTF-16le.
1759 #
1760 ifcapable {utf16} {
1761 sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
1762 sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
1763 sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
1764 foreach {tn castexpr differs} {
1765 1 { CAST(123 AS BLOB) } 1
1766 2 { CAST('' AS BLOB) } 0
1767 3 { CAST('abcd' AS BLOB) } 1
1768
1769 4 { CAST(X'abcd' AS TEXT) } 1
1770 5 { CAST(X'' AS TEXT) } 0
1771 } {
1772 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
1773 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
1774 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
1775
1776 if {$differs} {
1777 set res [expr {$r1!=$r2 && $r2!=$r3}]
1778 } else {
1779 set res [expr {$r1==$r2 && $r2==$r3}]
1780 }
1781
1782 do_test e_expr-33.1.$tn {set res} 1
1783 }
1784 db1 close
1785 db2 close
1786 db3 close
1787 }
1788
1789 #-------------------------------------------------------------------------
1790 # Test statements related to the EXISTS and NOT EXISTS operators.
1791 #
1792 catch { db close }
1793 forcedelete test.db
1794 sqlite3 db test.db
1795
1796 do_execsql_test e_expr-34.1 {
1797 CREATE TABLE t1(a, b);
1798 INSERT INTO t1 VALUES(1, 2);
1799 INSERT INTO t1 VALUES(NULL, 2);
1800 INSERT INTO t1 VALUES(1, NULL);
1801 INSERT INTO t1 VALUES(NULL, NULL);
1802 } {}
1803
1804 # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
1805 # of the integer values 0 and 1.
1806 #
1807 # This statement is not tested by itself. Instead, all e_expr-34.* tests
1808 # following this point explicitly test that specific invocations of EXISTS
1809 # return either integer 0 or integer 1.
1810 #
1811
1812 # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
1813 # as the right-hand operand of the EXISTS operator would return one or
1814 # more rows, then the EXISTS operator evaluates to 1.
1815 #
1816 foreach {tn expr} {
1817 1 { EXISTS ( SELECT a FROM t1 ) }
1818 2 { EXISTS ( SELECT b FROM t1 ) }
1819 3 { EXISTS ( SELECT 24 ) }
1820 4 { EXISTS ( SELECT NULL ) }
1821 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
1822 } {
1823 do_expr_test e_expr-34.2.$tn $expr integer 1
1824 }
1825
1826 # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
1827 # rows at all, then the EXISTS operator evaluates to 0.
1828 #
1829 foreach {tn expr} {
1830 1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
1831 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
1832 3 { EXISTS ( SELECT 24 WHERE 0) }
1833 4 { EXISTS ( SELECT NULL WHERE 1=2) }
1834 } {
1835 do_expr_test e_expr-34.3.$tn $expr integer 0
1836 }
1837
1838 # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
1839 # by the SELECT statement (if any) and the specific values returned have
1840 # no effect on the results of the EXISTS operator.
1841 #
1842 foreach {tn expr res} {
1843 1 { EXISTS ( SELECT * FROM t1 ) } 1
1844 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1
1845 3 { EXISTS ( SELECT 24, 25 ) } 1
1846 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1
1847 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1
1848
1849 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0
1850 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0
1851 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0
1852 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0
1853 } {
1854 do_expr_test e_expr-34.4.$tn $expr integer $res
1855 }
1856
1857 # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
1858 # are not handled any differently from rows without NULL values.
1859 #
1860 foreach {tn e1 e2} {
1861 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) }
1862 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) }
1863 } {
1864 set res [db one "SELECT $e1"]
1865 do_expr_test e_expr-34.5.${tn}a $e1 integer $res
1866 do_expr_test e_expr-34.5.${tn}b $e2 integer $res
1867 }
1868
1869 #-------------------------------------------------------------------------
1870 # Test statements related to scalar sub-queries.
1871 #
1872
1873 catch { db close }
1874 forcedelete test.db
1875 sqlite3 db test.db
1876 do_test e_expr-35.0 {
1877 execsql {
1878 CREATE TABLE t2(a, b);
1879 INSERT INTO t2 VALUES('one', 'two');
1880 INSERT INTO t2 VALUES('three', NULL);
1881 INSERT INTO t2 VALUES(4, 5.0);
1882 }
1883 } {}
1884
1885 # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
1886 # is a subquery.
1887 #
1888 # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
1889 # aggregate and compound SELECT queries (queries with keywords like
1890 # UNION or EXCEPT) are allowed as scalar subqueries.
1891 #
1892 do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35
1893 do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
1894
1895 do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
1896 do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
1897
1898 do_expr_test e_expr-35.1.5 {
1899 (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
1900 } null {}
1901 do_expr_test e_expr-35.1.6 {
1902 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
1903 } integer 4
1904
1905 # EVIDENCE-OF: R-43101-20178 A subquery that returns two or more columns
1906 # is a row value subquery and can only be used as an operand of a
1907 # comparison operator or as the value in an UPDATE SET clause whose
1908 # column name list has the same size.
1909 #
1910 # The following block tests that errors are returned in a bunch of cases
1911 # where a subquery returns more than one column.
1912 #
1913 set M {/1 {sub-select returns [23] columns - expected 1}/}
1914 foreach {tn sql} {
1915 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
1916 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
1917 3 { SELECT (SELECT 1, 2) }
1918 4 { SELECT (SELECT NULL, NULL, NULL) }
1919 5 { SELECT (SELECT * FROM t2) }
1920 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
1921 } {
1922 do_catchsql_test e_expr-35.2.$tn $sql $M
1923 }
1924
1925 # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the
1926 # first row of the result from the enclosed SELECT statement.
1927 #
1928 do_execsql_test e_expr-36.3.1 {
1929 CREATE TABLE t4(x, y);
1930 INSERT INTO t4 VALUES(1, 'one');
1931 INSERT INTO t4 VALUES(2, 'two');
1932 INSERT INTO t4 VALUES(3, 'three');
1933 } {}
1934
1935 foreach {tn expr restype resval} {
1936 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1
1937 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1
1938 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3
1939 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2
1940 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two
1941
1942 7 { ( SELECT sum(x) FROM t4 ) } integer 6
1943 8 { ( SELECT string_agg(y,'') FROM t4 ) } text onetwothree
1944 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2
1945
1946 } {
1947 do_expr_test e_expr-36.3.$tn $expr $restype $resval
1948 }
1949
1950 # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
1951 # if the enclosed SELECT statement returns no rows.
1952 #
1953 foreach {tn expr} {
1954 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) }
1955 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) }
1956 } {
1957 do_expr_test e_expr-36.4.$tn $expr null {}
1958 }
1959
1960 # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
1961 # 'english' and '0' are all considered to be false.
1962 #
1963 do_execsql_test e_expr-37.1 {
1964 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END, iif(NULL,'true','false');
1965 } {false false}
1966 do_execsql_test e_expr-37.2 {
1967 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END, iif(0.0,'true','false');
1968 } {false false}
1969 do_execsql_test e_expr-37.3 {
1970 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END, iif(0,'true','false');
1971 } {false false}
1972 do_execsql_test e_expr-37.4 {
1973 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END, iif('engligh','true','false');
1974 } {false false}
1975 do_execsql_test e_expr-37.5 {
1976 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END, iif('0','true','false');
1977 } {false false}
1978
1979 # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
1980 # considered to be true.
1981 #
1982 do_execsql_test e_expr-37.6a {
1983 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END, iif(1,'true','false');
1984 } {true true}
1985 do_execsql_test e_expr-37.6b {
1986 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END, if(1,'true');
1987 } {true true}
1988 do_execsql_test e_expr-37.7 {
1989 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END, iif(1.0,'true','false');
1990 } {true true}
1991 do_execsql_test e_expr-37.8 {
1992 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END, iif(0.1,'true','false');
1993 } {true true}
1994 do_execsql_test e_expr-37.9 {
1995 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END, iif(-0.1,'true','false');
1996 } {true true}
1997 do_execsql_test e_expr-37.10 {
1998 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END, iif('1engl','true','false');
1999 } {true true}
2000
2001
2002 finish_test