| blob | 1e7685352e69049ef7ea9d020be3a0214e750c60 |
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
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 }
27 proc do_qexpr_test {tn expr value} {
28 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
29 }
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 }
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
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 #
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
111 14 5 2 1
112 15 1 4 1
113 16 -1 0 1
114 17 0 1 -1
116 } {
117 set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
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)"
129 set a2 [db one $sql2]
130 set a3 [db one $sql3]
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 }
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)
144 do_test e_expr-1.1 { array names untested } {}
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}
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}
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}
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}
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 }
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
231 " {1 1 0 0 {} {} 0 0 1 1 {} {}}
232 }
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"]
247 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
248 }
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}
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 {
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
289 }}
290 }
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}
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 }
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
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
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
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
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
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
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}
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}
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}
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}
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
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}
463 #-------------------------------------------------------------------------
464 # Test statements related to bound parameters
465 #
467 proc parameter_test {tn sql params result} {
468 set stmt [sqlite3_prepare_v2 db $sql -1]
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 }
476 sqlite3_step $stmt
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 }
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 }
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"
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 }
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 }
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 parameter_test e_expr-11.2.5 "
552 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
553 " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
554 parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
556 # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
557 # except that the name of the parameter created is @AAAA.
558 #
559 parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1
560 parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1
561 parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1
562 parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1
563 parameter_test e_expr-11.3.5 "
564 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
565 " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
566 parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
568 # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
569 # name also holds a spot for a named parameter with the name $AAAA.
570 #
571 # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
572 # include one or more occurrences of "::" and a suffix enclosed in
573 # "(...)" containing any text at all.
574 #
575 # Note: Looks like an identifier cannot consist entirely of "::"
576 # characters or just a suffix. Also, the other named variable characters
577 # (: and @) work the same way internally. Why not just document it that way?
578 #
579 parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1
580 parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1
581 parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1
582 parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1
583 parameter_test e_expr-11.4.5 "
584 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
585 " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
586 parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
588 parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
589 parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
590 parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
592 # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
593 # number assigned is one greater than the largest parameter number
594 # already assigned.
595 #
596 # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
597 # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
598 # error.
599 #
600 parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}
601 parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}
602 parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
603 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
604 } {-1 -8 -9 -10 -2 -11}
605 foreach {tn sql} [list \
606 1 "SELECT ?$mvn, \$::a" \
607 2 "SELECT ?$mvn, ?4, @a1" \
608 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \
609 ] {
610 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
611 }
613 # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
614 # using sqlite3_bind() are treated as NULL.
615 #
616 do_test e_expr-11.7.1 {
617 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
618 sqlite3_step $stmt
620 list [sqlite3_column_type $stmt 0] \
621 [sqlite3_column_type $stmt 1] \
622 [sqlite3_column_type $stmt 2] \
623 [sqlite3_column_type $stmt 3]
624 } {NULL NULL NULL NULL}
625 do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
627 #-------------------------------------------------------------------------
628 # "Test" the syntax diagrams in lang_expr.html.
629 #
630 # EVIDENCE-OF: R-62067-43884 -- syntax diagram signed-number
631 #
632 do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
633 do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
634 do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
635 do_execsql_test e_expr-12.1.4 {
636 SELECT 1.4, +1.4, -1.4
637 } {1.4 1.4 -1.4}
638 do_execsql_test e_expr-12.1.5 {
639 SELECT 1.5e+5, +1.5e+5, -1.5e+5
640 } {150000.0 150000.0 -150000.0}
641 do_execsql_test e_expr-12.1.6 {
642 SELECT 0.0001, +0.0001, -0.0001
643 } {0.0001 0.0001 -0.0001}
645 # EVIDENCE-OF: R-21258-25489 -- syntax diagram literal-value
646 #
647 set sqlite_current_time 1
648 do_execsql_test e_expr-12.2.1 {SELECT 123} {123}
649 do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}
650 do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}
651 do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}
652 do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}
653 do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}
654 do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}
655 do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
656 set sqlite_current_time 0
658 # EVIDENCE-OF: R-57598-59332 -- syntax diagram expr
659 #
660 file delete -force test.db2
661 execsql {
662 ATTACH 'test.db2' AS dbname;
663 CREATE TABLE dbname.tblname(cname);
664 }
666 proc glob {args} {return 1}
667 db function glob glob
668 db function match glob
669 db function regexp glob
671 foreach {tn expr} {
672 1 123
673 2 123.4e05
674 3 'abcde'
675 4 X'414243'
676 5 NULL
677 6 CURRENT_TIME
678 7 CURRENT_DATE
679 8 CURRENT_TIMESTAMP
681 9 ?
682 10 ?123
683 11 @hello
684 12 :world
685 13 $tcl
686 14 $tcl(array)
688 15 cname
689 16 tblname.cname
690 17 dbname.tblname.cname
692 18 "+ EXPR"
693 19 "- EXPR"
694 20 "NOT EXPR"
695 21 "~ EXPR"
697 22 "EXPR1 || EXPR2"
698 23 "EXPR1 * EXPR2"
699 24 "EXPR1 / EXPR2"
700 25 "EXPR1 % EXPR2"
701 26 "EXPR1 + EXPR2"
702 27 "EXPR1 - EXPR2"
703 28 "EXPR1 << EXPR2"
704 29 "EXPR1 >> EXPR2"
705 30 "EXPR1 & EXPR2"
706 31 "EXPR1 | EXPR2"
707 32 "EXPR1 < EXPR2"
708 33 "EXPR1 <= EXPR2"
709 34 "EXPR1 > EXPR2"
710 35 "EXPR1 >= EXPR2"
711 36 "EXPR1 = EXPR2"
712 37 "EXPR1 == EXPR2"
713 38 "EXPR1 != EXPR2"
714 39 "EXPR1 <> EXPR2"
715 40 "EXPR1 IS EXPR2"
716 41 "EXPR1 IS NOT EXPR2"
717 42 "EXPR1 AND EXPR2"
718 43 "EXPR1 OR EXPR2"
720 44 "count(*)"
721 45 "count(DISTINCT EXPR)"
722 46 "substr(EXPR, 10, 20)"
723 47 "changes()"
725 48 "( EXPR )"
727 49 "CAST ( EXPR AS integer )"
728 50 "CAST ( EXPR AS 'abcd' )"
729 51 "CAST ( EXPR AS 'ab$ $cd' )"
731 52 "EXPR COLLATE nocase"
732 53 "EXPR COLLATE binary"
734 54 "EXPR1 LIKE EXPR2"
735 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
736 56 "EXPR1 GLOB EXPR2"
737 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
738 58 "EXPR1 REGEXP EXPR2"
739 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
740 60 "EXPR1 MATCH EXPR2"
741 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
742 62 "EXPR1 NOT LIKE EXPR2"
743 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
744 64 "EXPR1 NOT GLOB EXPR2"
745 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
746 66 "EXPR1 NOT REGEXP EXPR2"
747 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
748 68 "EXPR1 NOT MATCH EXPR2"
749 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
751 70 "EXPR ISNULL"
752 71 "EXPR NOTNULL"
753 72 "EXPR NOT NULL"
755 73 "EXPR1 IS EXPR2"
756 74 "EXPR1 IS NOT EXPR2"
758 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
759 76 "EXPR BETWEEN EXPR1 AND EXPR2"
761 77 "EXPR NOT IN (SELECT cname FROM tblname)"
762 78 "EXPR NOT IN (1)"
763 79 "EXPR NOT IN (1, 2, 3)"
764 80 "EXPR NOT IN tblname"
765 81 "EXPR NOT IN dbname.tblname"
766 82 "EXPR IN (SELECT cname FROM tblname)"
767 83 "EXPR IN (1)"
768 84 "EXPR IN (1, 2, 3)"
769 85 "EXPR IN tblname"
770 86 "EXPR IN dbname.tblname"
772 87 "EXISTS (SELECT cname FROM tblname)"
773 88 "NOT EXISTS (SELECT cname FROM tblname)"
775 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
776 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
777 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
778 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
779 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
780 94 "CASE WHEN EXPR1 THEN EXPR2 END"
781 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
782 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
783 } {
785 # If the expression string being parsed contains "EXPR2", then replace
786 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it
787 # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
788 #
789 set elist [list $expr]
790 if {[string match *EXPR2* $expr]} {
791 set elist [list]
792 foreach {e1 e2} { cname "34+22" } {
793 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
794 }
795 }
796 if {[string match *EXPR* $expr]} {
797 set elist2 [list]
798 foreach el $elist {
799 foreach e { cname "34+22" } {
800 lappend elist2 [string map [list EXPR $e] $el]
801 }
802 }
803 set elist $elist2
804 }
806 set x 0
807 foreach e $elist {
808 incr x
809 do_test e_expr-12.3.$tn.$x {
810 set rc [catch { execsql "SELECT $e FROM tblname" } msg]
811 } {0}
812 }
813 }
815 # EVIDENCE-OF: R-49462-56079 -- syntax diagram raise-function
816 #
817 foreach {tn raiseexpr} {
818 1 "RAISE(IGNORE)"
819 2 "RAISE(ROLLBACK, 'error message')"
820 3 "RAISE(ABORT, 'error message')"
821 4 "RAISE(FAIL, 'error message')"
822 } {
823 do_execsql_test e_expr-12.4.$tn "
824 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
825 SELECT $raiseexpr ;
826 END;
827 " {}
828 }
830 #-------------------------------------------------------------------------
831 # Test the statements related to the BETWEEN operator.
832 #
833 # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
834 # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
835 # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
836 # only evaluated once.
837 #
838 db func x x
839 proc x {} { incr ::xcount ; return [expr $::x] }
840 foreach {tn x expr res nEval} {
841 1 10 "x() >= 5 AND x() <= 15" 1 2
842 2 10 "x() BETWEEN 5 AND 15" 1 1
844 3 5 "x() >= 5 AND x() <= 5" 1 2
845 4 5 "x() BETWEEN 5 AND 5" 1 1
846 } {
847 do_test e_expr-13.1.$tn {
848 set ::xcount 0
849 set a [execsql "SELECT $expr"]
850 list $::xcount $a
851 } [list $nEval $res]
852 }
854 # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
855 # the same as the precedence as operators == and != and LIKE and groups
856 # left to right.
857 #
858 # Therefore, BETWEEN groups more tightly than operator "AND", but less
859 # so than "<".
860 #
861 do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1
862 do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1
863 do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0
864 do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1
865 do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1
866 do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0
868 do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1
869 do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1
870 do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0
871 do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1
872 do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1
873 do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0
875 do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1
876 do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1
877 do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0
878 do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1
879 do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1
880 do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0
882 do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0
883 do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
884 do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
885 do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0
886 do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
887 do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
889 do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1
890 do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
891 do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
892 do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0
893 do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0
894 do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1
896 #-------------------------------------------------------------------------
897 # Test the statements related to the LIKE and GLOB operators.
898 #
899 # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
900 # comparison.
901 #
902 # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
903 # operator contains the pattern and the left hand operand contains the
904 # string to match against the pattern.
905 #
906 do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
907 do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
909 # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
910 # matches any sequence of zero or more characters in the string.
911 #
912 do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1
913 do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1
914 do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
916 # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
917 # matches any single character in the string.
918 #
919 do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0
920 do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1
921 do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
923 # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
924 # lower/upper case equivalent (i.e. case-insensitive matching).
925 #
926 do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
927 do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
928 do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0
930 # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
931 # for ASCII characters by default.
932 #
933 # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
934 # default for unicode characters that are beyond the ASCII range.
935 #
936 # EVIDENCE-OF: R-44381-11669 the expression
937 # 'a' LIKE 'A' is TRUE but
938 # 'æ' LIKE 'Æ' is FALSE.
939 #
940 # The restriction to ASCII characters does not apply if the ICU
941 # library is compiled in. When ICU is enabled SQLite does not act
942 # as it does "by default".
943 #
944 do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1
945 ifcapable !icu {
946 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
947 }
949 # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
950 # then the expression following the ESCAPE keyword must evaluate to a
951 # string consisting of a single character.
952 #
953 do_catchsql_test e_expr-14.6.1 {
954 SELECT 'A' LIKE 'a' ESCAPE '12'
955 } {1 {ESCAPE expression must be a single character}}
956 do_catchsql_test e_expr-14.6.2 {
957 SELECT 'A' LIKE 'a' ESCAPE ''
958 } {1 {ESCAPE expression must be a single character}}
959 do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1}
960 do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
962 # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
963 # pattern to include literal percent or underscore characters.
964 #
965 # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
966 # symbol (%), underscore (_), or a second instance of the escape
967 # character itself matches a literal percent symbol, underscore, or a
968 # single escape character, respectively.
969 #
970 do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1
971 do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0
972 do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0
973 do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
974 do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
976 do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1
977 do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0
978 do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0
979 do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
980 do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
982 do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1
983 do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0
984 do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0
985 do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
987 # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
988 # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
989 #
990 proc likefunc {args} {
991 eval lappend ::likeargs $args
992 return 1
993 }
994 db func like -argcount 2 likefunc
995 db func like -argcount 3 likefunc
996 set ::likeargs [list]
997 do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
998 do_test e_expr-15.1.2 { set likeargs } {def abc}
999 set ::likeargs [list]
1000 do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
1001 do_test e_expr-15.1.4 { set likeargs } {def abc X}
1002 db close
1003 sqlite3 db test.db
1005 # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
1006 # sensitive using the case_sensitive_like pragma.
1007 #
1008 do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1009 do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}
1010 do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
1011 do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1012 do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
1013 do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1014 do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1016 # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
1017 # uses the Unix file globbing syntax for its wildcards.
1018 #
1019 # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
1020 #
1021 do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
1022 do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
1023 do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
1024 do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
1026 do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
1027 do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
1028 do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
1030 # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
1031 # NOT keyword to invert the sense of the test.
1032 #
1033 do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
1034 do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
1035 do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
1036 do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
1037 do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
1039 db nullvalue null
1040 do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
1041 do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
1042 do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
1043 do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
1044 db nullvalue {}
1046 # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
1047 # calling the function glob(Y,X) and can be modified by overriding that
1048 # function.
1049 proc globfunc {args} {
1050 eval lappend ::globargs $args
1051 return 1
1052 }
1053 db func glob -argcount 2 globfunc
1054 set ::globargs [list]
1055 do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
1056 do_test e_expr-17.3.2 { set globargs } {def abc}
1057 set ::globargs [list]
1058 do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
1059 do_test e_expr-17.3.4 { set globargs } {Y X}
1060 sqlite3 db test.db
1062 # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
1063 # default and so use of the REGEXP operator will normally result in an
1064 # error message.
1065 #
1066 # There is a regexp function if ICU is enabled though.
1067 #
1068 ifcapable !icu {
1069 do_catchsql_test e_expr-18.1.1 {
1070 SELECT regexp('abc', 'def')
1071 } {1 {no such function: regexp}}
1072 do_catchsql_test e_expr-18.1.2 {
1073 SELECT 'abc' REGEXP 'def'
1074 } {1 {no such function: REGEXP}}
1075 }
1077 # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
1078 # the regexp() user function.
1079 #
1080 # EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
1081 # "regexp" is added at run-time, that function will be called in order
1082 # to implement the REGEXP operator.
1083 #
1084 proc regexpfunc {args} {
1085 eval lappend ::regexpargs $args
1086 return 1
1087 }
1088 db func regexp -argcount 2 regexpfunc
1089 set ::regexpargs [list]
1090 do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
1091 do_test e_expr-18.2.2 { set regexpargs } {def abc}
1092 set ::regexpargs [list]
1093 do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
1094 do_test e_expr-18.2.4 { set regexpargs } {Y X}
1095 sqlite3 db test.db
1097 # EVIDENCE-OF: R-42037-37826 The default match() function implementation
1098 # raises an exception and is not really useful for anything.
1099 #
1100 do_catchsql_test e_expr-19.1.1 {
1101 SELECT 'abc' MATCH 'def'
1102 } {1 {unable to use function MATCH in the requested context}}
1103 do_catchsql_test e_expr-19.1.2 {
1104 SELECT match('abc', 'def')
1105 } {1 {unable to use function MATCH in the requested context}}
1107 # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
1108 # the match() application-defined function.
1109 #
1110 # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
1111 # function with more helpful logic.
1112 #
1113 proc matchfunc {args} {
1114 eval lappend ::matchargs $args
1115 return 1
1116 }
1117 db func match -argcount 2 matchfunc
1118 set ::matchargs [list]
1119 do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
1120 do_test e_expr-19.2.2 { set matchargs } {def abc}
1121 set ::matchargs [list]
1122 do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
1123 do_test e_expr-19.2.4 { set matchargs } {Y X}
1124 sqlite3 db test.db
1126 #-------------------------------------------------------------------------
1127 # Test cases for the testable statements related to the CASE expression.
1128 #
1129 # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
1130 # expression: those with a base expression and those without.
1131 #
1132 do_execsql_test e_expr-20.1 {
1133 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1134 } {true}
1135 do_execsql_test e_expr-20.2 {
1136 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1137 } {false}
1139 proc var {nm} {
1140 lappend ::varlist $nm
1141 return [set "::$nm"]
1142 }
1143 db func var var
1145 # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
1146 # WHEN expression is evaluated and the result treated as a boolean,
1147 # starting with the leftmost and continuing to the right.
1148 #
1149 foreach {a b c} {0 0 0} break
1150 set varlist [list]
1151 do_execsql_test e_expr-21.1.1 {
1152 SELECT CASE WHEN var('a') THEN 'A'
1153 WHEN var('b') THEN 'B'
1154 WHEN var('c') THEN 'C' END
1155 } {{}}
1156 do_test e_expr-21.1.2 { set varlist } {a b c}
1157 set varlist [list]
1158 do_execsql_test e_expr-21.1.3 {
1159 SELECT CASE WHEN var('c') THEN 'C'
1160 WHEN var('b') THEN 'B'
1161 WHEN var('a') THEN 'A'
1162 ELSE 'no result'
1163 END
1164 } {{no result}}
1165 do_test e_expr-21.1.4 { set varlist } {c b a}
1167 # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
1168 # evaluation of the THEN expression that corresponds to the first WHEN
1169 # expression that evaluates to true.
1170 #
1171 foreach {a b c} {0 1 0} break
1172 do_execsql_test e_expr-21.2.1 {
1173 SELECT CASE WHEN var('a') THEN 'A'
1174 WHEN var('b') THEN 'B'
1175 WHEN var('c') THEN 'C'
1176 ELSE 'no result'
1177 END
1178 } {B}
1179 foreach {a b c} {0 1 1} break
1180 do_execsql_test e_expr-21.2.2 {
1181 SELECT CASE WHEN var('a') THEN 'A'
1182 WHEN var('b') THEN 'B'
1183 WHEN var('c') THEN 'C'
1184 ELSE 'no result'
1185 END
1186 } {B}
1187 foreach {a b c} {0 0 1} break
1188 do_execsql_test e_expr-21.2.3 {
1189 SELECT CASE WHEN var('a') THEN 'A'
1190 WHEN var('b') THEN 'B'
1191 WHEN var('c') THEN 'C'
1192 ELSE 'no result'
1193 END
1194 } {C}
1196 # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
1197 # evaluate to true, the result of evaluating the ELSE expression, if
1198 # any.
1199 #
1200 foreach {a b c} {0 0 0} break
1201 do_execsql_test e_expr-21.3.1 {
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 } {{no result}}
1209 # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
1210 # the WHEN expressions are true, then the overall result is NULL.
1211 #
1212 db nullvalue null
1213 do_execsql_test e_expr-21.3.2 {
1214 SELECT CASE WHEN var('a') THEN 'A'
1215 WHEN var('b') THEN 'B'
1216 WHEN var('c') THEN 'C'
1217 END
1218 } {null}
1219 db nullvalue {}
1221 # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
1222 # evaluating WHEN terms.
1223 #
1224 do_execsql_test e_expr-21.4.1 {
1225 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
1226 } {B}
1227 do_execsql_test e_expr-21.4.2 {
1228 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
1229 } {C}
1231 # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
1232 # expression is evaluated just once and the result is compared against
1233 # the evaluation of each WHEN expression from left to right.
1234 #
1235 # Note: This test case tests the "evaluated just once" part of the above
1236 # statement. Tests associated with the next two statements test that the
1237 # comparisons take place.
1238 #
1239 foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
1240 set ::varlist [list]
1241 do_execsql_test e_expr-22.1.1 {
1242 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
1243 } {C}
1244 do_test e_expr-22.1.2 { set ::varlist } {a}
1246 # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
1247 # evaluation of the THEN expression that corresponds to the first WHEN
1248 # expression for which the comparison is true.
1249 #
1250 do_execsql_test e_expr-22.2.1 {
1251 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1252 } {B}
1253 do_execsql_test e_expr-22.2.2 {
1254 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1255 } {A}
1257 # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
1258 # evaluate to a value equal to the base expression, the result of
1259 # evaluating the ELSE expression, if any.
1260 #
1261 do_execsql_test e_expr-22.3.1 {
1262 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
1263 } {D}
1265 # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
1266 # the WHEN expressions produce a result equal to the base expression,
1267 # the overall result is NULL.
1268 #
1269 do_execsql_test e_expr-22.4.1 {
1270 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1271 } {{}}
1272 db nullvalue null
1273 do_execsql_test e_expr-22.4.2 {
1274 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1275 } {null}
1276 db nullvalue {}
1278 # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
1279 # WHEN expression, the same collating sequence, affinity, and
1280 # NULL-handling rules apply as if the base expression and WHEN
1281 # expression are respectively the left- and right-hand operands of an =
1282 # operator.
1283 #
1284 proc rev {str} {
1285 set ret ""
1286 set chars [split $str]
1287 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
1288 append ret [lindex $chars $i]
1289 }
1290 set ret
1291 }
1292 proc reverse {lhs rhs} {
1293 string compare [rev $lhs] [ref $rhs]
1294 }
1295 db collate reverse reverse
1296 do_execsql_test e_expr-23.1.1 {
1297 CREATE TABLE t1(
1298 a TEXT COLLATE NOCASE,
1299 b COLLATE REVERSE,
1300 c INTEGER,
1301 d BLOB
1302 );
1303 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
1304 } {}
1305 do_execsql_test e_expr-23.1.2 {
1306 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
1307 } {B}
1308 do_execsql_test e_expr-23.1.3 {
1309 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
1310 } {B}
1311 do_execsql_test e_expr-23.1.4 {
1312 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
1313 } {B}
1314 do_execsql_test e_expr-23.1.5 {
1315 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
1316 } {A}
1317 do_execsql_test e_expr-23.1.6 {
1318 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
1319 } {B}
1320 do_execsql_test e_expr-23.1.7 {
1321 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
1322 } {A}
1323 do_execsql_test e_expr-23.1.8 {
1324 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
1325 } {B}
1326 do_execsql_test e_expr-23.1.9 {
1327 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
1328 } {B}
1330 # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
1331 # result of the CASE is always the result of evaluating the ELSE
1332 # expression if it exists, or NULL if it does not.
1333 #
1334 do_execsql_test e_expr-24.1.1 {
1335 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
1336 } {{}}
1337 do_execsql_test e_expr-24.1.2 {
1338 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
1339 } {C}
1341 # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
1342 # or short-circuit, evaluation.
1343 #
1344 set varlist [list]
1345 foreach {a b c} {0 1 0} break
1346 do_execsql_test e_expr-25.1.1 {
1347 SELECT CASE WHEN var('a') THEN 'A'
1348 WHEN var('b') THEN 'B'
1349 WHEN var('c') THEN 'C'
1350 END
1351 } {B}
1352 do_test e_expr-25.1.2 { set ::varlist } {a b}
1353 set varlist [list]
1354 do_execsql_test e_expr-25.1.3 {
1355 SELECT CASE '0' WHEN var('a') THEN 'A'
1356 WHEN var('b') THEN 'B'
1357 WHEN var('c') THEN 'C'
1358 END
1359 } {A}
1360 do_test e_expr-25.1.4 { set ::varlist } {a}
1362 # EVIDENCE-OF: R-34773-62253 The only difference between the following
1363 # two CASE expressions is that the x expression is evaluated exactly
1364 # once in the first example but might be evaluated multiple times in the
1365 # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
1366 # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
1367 #
1368 proc ceval {x} {
1369 incr ::evalcount
1370 return $x
1371 }
1372 db func ceval ceval
1373 set ::evalcount 0
1375 do_execsql_test e_expr-26.1.1 {
1376 CREATE TABLE t2(x, w1, r1, w2, r2, r3);
1377 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
1378 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
1379 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
1380 } {}
1381 do_execsql_test e_expr-26.1.2 {
1382 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1383 } {R1 R2 R3}
1384 do_execsql_test e_expr-26.1.3 {
1385 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
1386 } {R1 R2 R3}
1388 do_execsql_test e_expr-26.1.4 {
1389 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1390 } {R1 R2 R3}
1391 do_test e_expr-26.1.5 { set ::evalcount } {3}
1392 set ::evalcount 0
1393 do_execsql_test e_expr-26.1.6 {
1394 SELECT CASE
1395 WHEN ceval(x)=w1 THEN r1
1396 WHEN ceval(x)=w2 THEN r2
1397 ELSE r3 END
1398 FROM t2
1399 } {R1 R2 R3}
1400 do_test e_expr-26.1.6 { set ::evalcount } {5}
1403 #-------------------------------------------------------------------------
1404 # Test statements related to CAST expressions.
1405 #
1406 # EVIDENCE-OF: R-65079-31758 Application of a CAST expression is
1407 # different to application of a column affinity, as with a CAST
1408 # expression the storage class conversion is forced even if it is lossy
1409 # and irrreversible.
1410 #
1411 do_execsql_test e_expr-27.1.1 {
1412 CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
1413 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
1414 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
1415 } {blob UVU text 1.23abc real 4.5}
1416 do_execsql_test e_expr-27.1.2 {
1417 SELECT
1418 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
1419 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
1420 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
1421 } {text UVU real 1.23 integer 4}
1423 # EVIDENCE-OF: R-27225-65050 If the value of <expr> is NULL, then
1424 # the result of the CAST expression is also NULL.
1425 #
1426 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
1427 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {}
1428 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {}
1429 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {}
1431 # EVIDENCE-OF: R-31076-23575 Casting a value to a <type-name> with
1432 # no affinity causes the value to be converted into a BLOB.
1433 #
1434 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc
1435 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
1436 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi
1438 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
1439 # the value to TEXT in the encoding of the database connection, then
1440 # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
1441 #
1442 do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
1443 do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536'
1444 do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738'
1445 rename db db2
1446 sqlite3 db :memory:
1447 ifcapable {utf16} {
1448 db eval { PRAGMA encoding = 'utf-16le' }
1449 do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
1450 do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600'
1451 do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800'
1452 }
1453 db close
1454 sqlite3 db :memory:
1455 db eval { PRAGMA encoding = 'utf-16be' }
1456 ifcapable {utf16} {
1457 do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
1458 do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036'
1459 do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038'
1460 }
1461 db close
1462 rename db2 db
1464 # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
1465 # of bytes that make up the BLOB is interpreted as text encoded using
1466 # the database encoding.
1467 #
1468 do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
1469 do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
1470 rename db db2
1471 sqlite3 db :memory:
1472 db eval { PRAGMA encoding = 'utf-16le' }
1473 ifcapable {utf16} {
1474 do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
1475 do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
1476 }
1477 db close
1478 rename db2 db
1480 # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
1481 # renders the value as if via sqlite3_snprintf() except that the
1482 # resulting TEXT uses the encoding of the database connection.
1483 #
1484 do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1
1485 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45
1486 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45
1487 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8
1488 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0
1489 do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
1490 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0
1491 do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0
1493 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
1494 # value is first converted to TEXT.
1495 #
1496 do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
1497 do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
1498 do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
1499 do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
1500 rename db db2
1501 sqlite3 db :memory:
1502 ifcapable {utf16} {
1503 db eval { PRAGMA encoding = 'utf-16le' }
1504 do_expr_test e_expr-29.1.5 {
1505 CAST (X'31002E0032003300' AS REAL) } real 1.23
1506 do_expr_test e_expr-29.1.6 {
1507 CAST (X'3200330030002E003000' AS REAL) } real 230.0
1508 do_expr_test e_expr-29.1.7 {
1509 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
1510 do_expr_test e_expr-29.1.8 {
1511 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
1512 }
1513 db close
1514 rename db2 db
1516 # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
1517 # longest possible prefix of the value that can be interpreted as a real
1518 # number is extracted from the TEXT value and the remainder ignored.
1519 #
1520 do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
1521 do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
1522 do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
1523 do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
1525 # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
1526 # ignored when converging from TEXT to REAL.
1527 #
1528 do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
1529 do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45
1530 do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212
1531 do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
1533 # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
1534 # interpreted as a real number, the result of the conversion is 0.0.
1535 #
1536 do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
1537 do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
1538 do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
1540 # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
1541 # value is first converted to TEXT.
1542 #
1543 do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
1544 do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
1545 do_expr_test e_expr-30.1.3 {
1546 CAST(X'31303030303030' AS INTEGER)
1547 } integer 1000000
1548 do_expr_test e_expr-30.1.4 {
1549 CAST(X'2D31313235383939393036383432363234' AS INTEGER)
1550 } integer -1125899906842624
1552 rename db db2
1553 sqlite3 db :memory:
1554 ifcapable {utf16} {
1555 execsql { PRAGMA encoding = 'utf-16be' }
1556 do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
1557 do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
1558 do_expr_test e_expr-30.1.7 {
1559 CAST(X'0031003000300030003000300030' AS INTEGER)
1560 } integer 1000000
1561 do_expr_test e_expr-30.1.8 {
1562 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER)
1563 } integer -1125899906842624
1564 }
1565 db close
1566 rename db2 db
1568 # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
1569 # longest possible prefix of the value that can be interpreted as an
1570 # integer number is extracted from the TEXT value and the remainder
1571 # ignored.
1572 #
1573 do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
1574 do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
1575 do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
1576 do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
1578 # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
1579 # converting from TEXT to INTEGER are ignored.
1580 #
1581 do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123
1582 do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523
1583 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
1584 do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1
1586 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
1587 # interpreted as an integer number, the result of the conversion is 0.
1588 #
1589 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
1590 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
1591 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
1593 # EVIDENCE-OF: R-00741-38776 A cast of a REAL value into an INTEGER will
1594 # truncate the fractional part of the REAL.
1595 #
1596 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
1597 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
1598 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
1599 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
1601 # EVIDENCE-OF: R-06126-36021 If an REAL is too large to be represented
1602 # as an INTEGER then the result of the cast is the largest negative
1603 # integer: -9223372036854775808.
1604 #
1605 do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
1606 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
1607 do_expr_test e_expr-31.2.3 {
1608 CAST(-9223372036854775809.0 AS INT)
1609 } integer -9223372036854775808
1610 do_expr_test e_expr-31.2.4 {
1611 CAST(9223372036854775809.0 AS INT)
1612 } integer -9223372036854775808
1615 # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
1616 # first does a forced conversion into REAL but then further converts the
1617 # result into INTEGER if and only if the conversion from REAL to INTEGER
1618 # is lossless and reversible.
1619 #
1620 do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45
1621 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45
1622 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2
1623 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
1624 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
1626 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
1627 # is a no-op, even if a real value could be losslessly converted to an
1628 # integer.
1629 #
1630 do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
1631 do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
1633 do_expr_test e_expr-32.2.3 {
1634 CAST(-9223372036854775808 AS NUMERIC)
1635 } integer -9223372036854775808
1636 do_expr_test e_expr-32.2.4 {
1637 CAST(9223372036854775807 AS NUMERIC)
1638 } integer 9223372036854775807
1640 # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
1641 # non-BLOB value into a BLOB and the result from casting any BLOB value
1642 # into a non-BLOB value may be different depending on whether the
1643 # database encoding is UTF-8, UTF-16be, or UTF-16le.
1644 #
1645 ifcapable {utf16} {
1646 sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
1647 sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
1648 sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
1649 foreach {tn castexpr differs} {
1650 1 { CAST(123 AS BLOB) } 1
1651 2 { CAST('' AS BLOB) } 0
1652 3 { CAST('abcd' AS BLOB) } 1
1654 4 { CAST(X'abcd' AS TEXT) } 1
1655 5 { CAST(X'' AS TEXT) } 0
1656 } {
1657 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
1658 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
1659 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
1661 if {$differs} {
1662 set res [expr {$r1!=$r2 && $r2!=$r3}]
1663 } else {
1664 set res [expr {$r1==$r2 && $r2==$r3}]
1665 }
1667 do_test e_expr-33.1.$tn {set res} 1
1668 }
1669 db1 close
1670 db2 close
1671 db3 close
1672 }
1674 #-------------------------------------------------------------------------
1675 # Test statements related to the EXISTS and NOT EXISTS operators.
1676 #
1677 catch { db close }
1678 file delete -force test.db
1679 sqlite3 db test.db
1681 do_execsql_test e_expr-34.1 {
1682 CREATE TABLE t1(a, b);
1683 INSERT INTO t1 VALUES(1, 2);
1684 INSERT INTO t1 VALUES(NULL, 2);
1685 INSERT INTO t1 VALUES(1, NULL);
1686 INSERT INTO t1 VALUES(NULL, NULL);
1687 } {}
1689 # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
1690 # of the integer values 0 and 1.
1691 #
1692 # This statement is not tested by itself. Instead, all e_expr-34.* tests
1693 # following this point explicitly test that specific invocations of EXISTS
1694 # return either integer 0 or integer 1.
1695 #
1697 # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
1698 # as the right-hand operand of the EXISTS operator would return one or
1699 # more rows, then the EXISTS operator evaluates to 1.
1700 #
1701 foreach {tn expr} {
1702 1 { EXISTS ( SELECT a FROM t1 ) }
1703 2 { EXISTS ( SELECT b FROM t1 ) }
1704 3 { EXISTS ( SELECT 24 ) }
1705 4 { EXISTS ( SELECT NULL ) }
1706 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
1707 } {
1708 do_expr_test e_expr-34.2.$tn $expr integer 1
1709 }
1711 # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
1712 # rows at all, then the EXISTS operator evaluates to 0.
1713 #
1714 foreach {tn expr} {
1715 1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
1716 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
1717 3 { EXISTS ( SELECT 24 WHERE 0) }
1718 4 { EXISTS ( SELECT NULL WHERE 1=2) }
1719 } {
1720 do_expr_test e_expr-34.3.$tn $expr integer 0
1721 }
1723 # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
1724 # by the SELECT statement (if any) and the specific values returned have
1725 # no effect on the results of the EXISTS operator.
1726 #
1727 foreach {tn expr res} {
1728 1 { EXISTS ( SELECT * FROM t1 ) } 1
1729 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1
1730 3 { EXISTS ( SELECT 24, 25 ) } 1
1731 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1
1732 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1
1734 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0
1735 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0
1736 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0
1737 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0
1738 } {
1739 do_expr_test e_expr-34.4.$tn $expr integer $res
1740 }
1742 # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
1743 # are not handled any differently from rows without NULL values.
1744 #
1745 foreach {tn e1 e2} {
1746 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) }
1747 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) }
1748 } {
1749 set res [db one "SELECT $e1"]
1750 do_expr_test e_expr-34.5.${tn}a $e1 integer $res
1751 do_expr_test e_expr-34.5.${tn}b $e2 integer $res
1752 }
1754 #-------------------------------------------------------------------------
1755 # Test statements related to scalar sub-queries.
1756 #
1758 catch { db close }
1759 file delete -force test.db
1760 sqlite3 db test.db
1761 do_test e_expr-35.0 {
1762 execsql {
1763 CREATE TABLE t2(a, b);
1764 INSERT INTO t2 VALUES('one', 'two');
1765 INSERT INTO t2 VALUES('three', NULL);
1766 INSERT INTO t2 VALUES(4, 5.0);
1767 }
1768 } {}
1770 # EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
1771 # may appear as a scalar quantity.
1772 #
1773 # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
1774 # aggregate and compound SELECT queries (queries with keywords like
1775 # UNION or EXCEPT) are allowed as scalar subqueries.
1776 #
1777 do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35
1778 do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
1780 do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
1781 do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
1783 do_expr_test e_expr-35.1.5 {
1784 (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
1785 } null {}
1786 do_expr_test e_expr-35.1.6 {
1787 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
1788 } integer 4
1790 # EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
1791 # return a result set with a single column.
1792 #
1793 # The following block tests that errors are returned in a bunch of cases
1794 # where a subquery returns more than one column.
1795 #
1796 set M {only a single result allowed for a SELECT that is part of an expression}
1797 foreach {tn sql} {
1798 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
1799 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
1800 3 { SELECT (SELECT 1, 2) }
1801 4 { SELECT (SELECT NULL, NULL, NULL) }
1802 5 { SELECT (SELECT * FROM t2) }
1803 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
1804 } {
1805 do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
1806 }
1808 # EVIDENCE-OF: R-35764-28041 The result of the expression is the value
1809 # of the only column in the first row returned by the SELECT statement.
1810 #
1811 # EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
1812 # row, all rows after the first are ignored.
1813 #
1814 do_execsql_test e_expr-36.3.1 {
1815 CREATE TABLE t4(x, y);
1816 INSERT INTO t4 VALUES(1, 'one');
1817 INSERT INTO t4 VALUES(2, 'two');
1818 INSERT INTO t4 VALUES(3, 'three');
1819 } {}
1821 foreach {tn expr restype resval} {
1822 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1
1823 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1
1824 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3
1825 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2
1826 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two
1828 7 { ( SELECT sum(x) FROM t4 ) } integer 6
1829 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree
1830 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2
1832 } {
1833 do_expr_test e_expr-36.3.$tn $expr $restype $resval
1834 }
1836 # EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
1837 # value of the expression is NULL.
1838 #
1839 foreach {tn expr} {
1840 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) }
1841 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) }
1842 } {
1843 do_expr_test e_expr-36.4.$tn $expr null {}
1844 }
1847 finish_test