wasCSharpSQLite – Blame information for rev 1

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