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