3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
12 # This file implements tests to verify that the "testable statements" in
13 # the lang_expr.html document are correct.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 source $testdir/malloc_common.tcl
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]
31 proc do_qexpr_test {tn expr value} {
32 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
35 # Set up three global variables:
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.
40 # ::opprec An array mapping from SQL operator to a numeric
41 # precedence value. Operators that group more tightly
42 # have lower numeric precedences.
44 # ::oplist A list of all SQL operators supported by SQLite.
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
54 set ::opname($op) $opn
63 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
68 set ::opprec($op) $prec
74 # Hook in definitions of MATCH and REGEX. The following implementations
75 # cause MATCH and REGEX to behave similarly to the == operator.
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.
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
92 # EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
96 unset -nocomplain untested
99 set untested($op1,$op2) 1
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.
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}
139 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
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.
154 do_execsql_test e_expr-1.2.1 {
155 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1)
157 do_execsql_test e_expr-1.2.2 {
158 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2)
161 # Showing that LIKE and == have the same precedence
163 do_execsql_test e_expr-1.2.3 {
164 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1)
166 do_execsql_test e_expr-1.2.4 {
167 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1)
170 # Showing that < groups more tightly than == (< has higher precedence).
172 do_execsql_test e_expr-1.2.5 {
173 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1)
175 do_execsql_test e_expr-1.6 {
176 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2)
179 #-------------------------------------------------------------------------
180 # Check that the four unary prefix operators mentioned in the
181 # documentation exist.
183 # EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
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.
194 # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
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
200 foreach {tn literal type} {
208 set sql " SELECT quote( + $literal ), typeof( + $literal) "
209 do_execsql_test e_expr-3.$tn $sql [list $literal $type]
212 #-------------------------------------------------------------------------
213 # Check that both = and == are both acceptable as the "equals" operator.
214 # Similarly, either != or <> work as the not-equals operator.
216 # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
218 # EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
221 foreach {tn literal different} {
222 1 'helloworld' '12345'
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 {} {}}
238 #-------------------------------------------------------------------------
239 # Test the || operator.
241 # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
242 # together the two strings of its operands.
245 1 'helloworld' '12345'
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}"]
254 #-------------------------------------------------------------------------
255 # Test the % operator.
257 # EVIDENCE-OF: R-04223-04352 The operator % outputs the integer value of
258 # its left operand modulo its right operand.
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}
264 do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0}
266 #-------------------------------------------------------------------------
267 # Test that the results of all binary operators are either numeric or
268 # NULL, except for the || operator, which may evaluate to either a text
271 # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
272 # a numeric value or NULL, except for the || concatenation operator
273 # which always evaluates to either NULL or a text value.
276 1 'abc' 2 'hexadecimal' 3 ''
278 7 123.4 8 0.0 9 -123.4
279 10 X'ABCDEF' 11 X'' 12 X'0000'
283 foreach {n1 rhs} $literals {
284 foreach {n2 lhs} $literals {
286 set t [db one " SELECT typeof($lhs $op $rhs) "]
287 do_test e_expr-7.$opname($op).$n1.$n2 {
289 ($op=="||" && ($t == "text" || $t == "null"))
290 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
297 #-------------------------------------------------------------------------
298 # Test the IS and IS NOT operators.
300 # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
301 # != except when one or both of the operands are NULL.
303 # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
304 # then the IS operator evaluates to 1 (true) and the IS NOT operator
305 # evaluates to 0 (false).
307 # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
308 # not, then the IS operator evaluates to 0 (false) and the IS NOT
309 # operator is 1 (true).
311 # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
312 # expression to evaluate to NULL.
314 do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1}
315 do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0}
316 do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0}
317 do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1}
318 do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}}
319 do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}}
320 do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}}
321 do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1}
322 do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0}
323 do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
324 do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
325 do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
326 do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}}
327 do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}}
328 do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}}
329 do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0}
331 foreach {n1 rhs} $literals {
332 foreach {n2 lhs} $literals {
333 if {$rhs!="NULL" && $lhs!="NULL"} {
334 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
336 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
337 [expr {$lhs!="NULL" || $rhs!="NULL"}]
340 set test e_expr-8.2.$n1.$n2
341 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
342 do_execsql_test $test.2 "
343 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
348 #-------------------------------------------------------------------------
349 # Run some tests on the COLLATE "unary postfix operator".
351 # This collation sequence reverses both arguments before using
352 # [string compare] to compare them. For example, when comparing the
353 # strings 'one' and 'four', return the result of:
355 # string compare eno ruof
357 proc reverse_str {zStr} {
359 foreach c [split $zStr {}] { set out "${c}${out}" }
362 proc reverse_collate {zLeft zRight} {
363 string compare [reverse_str $zLeft] [reverse_str $zRight]
365 db collate reverse reverse_collate
367 # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
368 # operator that assigns a collating sequence to an expression.
370 # EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher
371 # precedence (binds more tightly) than any binary operator and any unary
372 # prefix operator except "~".
374 do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0
375 do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1
376 do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0
377 do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1
379 do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1
380 do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0
381 do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1
382 do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0
384 do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1
385 do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0
386 do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1
387 do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
388 do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1
389 do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
391 do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0
392 do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1
393 do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0
394 do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1
395 do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0
396 do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
398 do_execsql_test e_expr-9.22 {
399 SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase
401 do_execsql_test e_expr-9.23 {
402 SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase
405 # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
406 # operator overrides the collating sequence determined by the COLLATE
407 # clause in a table column definition.
409 do_execsql_test e_expr-9.24 {
410 CREATE TABLE t24(a COLLATE NOCASE, b);
411 INSERT INTO t24 VALUES('aaa', 1);
412 INSERT INTO t24 VALUES('bbb', 2);
413 INSERT INTO t24 VALUES('ccc', 3);
415 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
416 do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
417 do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
418 do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
420 #-------------------------------------------------------------------------
421 # Test statements related to literal values.
423 # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
424 # point numbers, strings, BLOBs, or NULLs.
426 do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer}
427 do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real}
428 do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text}
429 do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
430 do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null}
432 # "Scientific notation is supported for point literal values."
434 do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real}
435 do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real}
436 do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034}
437 do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0}
439 # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
440 # the string in single quotes (').
442 # EVIDENCE-OF: R-07100-06606 A single quote within the string can be
443 # encoded by putting two single quotes in a row - as in Pascal.
445 do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}}
446 do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
447 do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't}
448 do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
450 # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
451 # containing hexadecimal data and preceded by a single "x" or "X"
454 # EVIDENCE-OF: R-19836-11244 Example: X'53514C697465'
456 do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
457 do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
458 do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
459 do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
460 do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob
462 # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
465 do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}}
466 do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
468 #-------------------------------------------------------------------------
469 # Test statements related to bound parameters
472 proc parameter_test {tn sql params result} {
473 set stmt [sqlite3_prepare_v2 db $sql -1]
475 foreach {number name} $params {
476 set nm [sqlite3_bind_parameter_name $stmt $number]
477 do_test $tn.name.$number [list set {} $nm] $name
478 sqlite3_bind_int $stmt $number [expr -1 * $number]
484 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
485 lappend res [sqlite3_column_text $stmt $i]
488 set rc [sqlite3_finalize $stmt]
489 do_test $tn.rc [list set {} $rc] SQLITE_OK
490 do_test $tn.res [list set {} $res] $result
493 # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
494 # holds a spot for the NNN-th parameter. NNN must be between 1 and
495 # SQLITE_MAX_VARIABLE_NUMBER.
497 set mvn $SQLITE_MAX_VARIABLE_NUMBER
498 parameter_test e_expr-11.1 "
499 SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
500 " "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4"
502 set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
503 foreach {tn param_number} [list \
505 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
506 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
507 5 12345678903456789034567890234567890 \
512 10 9223372036854775808 \
513 11 9223372036854775809 \
514 12 18446744073709551616 \
515 13 18446744073709551617 \
517 do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
520 # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
521 # number creates a parameter with a number one greater than the largest
522 # parameter number already assigned.
524 # EVIDENCE-OF: R-42938-07030 If this means the parameter number is
525 # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
527 parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1
528 parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2}
529 parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6}
530 parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5}
531 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
534 parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
535 1 {} 456 ?456 4 ?4 457 {}
537 foreach {tn sql} [list \
538 1 "SELECT ?$mvn, ?" \
539 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \
540 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \
542 do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
545 # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
546 # holds a spot for a named parameter with the name :AAAA.
548 # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
549 # and any UTF characters with codepoints larger than 127 (non-ASCII
552 parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1
553 parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1
554 parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1
555 parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1
556 parameter_test e_expr-11.2.5 "
557 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
558 " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
559 parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
561 # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
562 # except that the name of the parameter created is @AAAA.
564 parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1
565 parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1
566 parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1
567 parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1
568 parameter_test e_expr-11.3.5 "
569 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
570 " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
571 parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
573 # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
574 # name also holds a spot for a named parameter with the name $AAAA.
576 # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
577 # include one or more occurrences of "::" and a suffix enclosed in
578 # "(...)" containing any text at all.
580 # Note: Looks like an identifier cannot consist entirely of "::"
581 # characters or just a suffix. Also, the other named variable characters
582 # (: and @) work the same way internally. Why not just document it that way?
584 parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1
585 parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1
586 parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1
587 parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1
588 parameter_test e_expr-11.4.5 "
589 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
590 " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
591 parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
593 parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
594 parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
595 parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
597 # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
598 # number assigned is one greater than the largest parameter number
601 # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
602 # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
605 parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}
606 parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}
607 parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
608 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
609 } {-1 -8 -9 -10 -2 -11}
610 foreach {tn sql} [list \
611 1 "SELECT ?$mvn, \$::a" \
612 2 "SELECT ?$mvn, ?4, @a1" \
613 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \
615 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
618 # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
619 # using sqlite3_bind() are treated as NULL.
621 do_test e_expr-11.7.1 {
622 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
625 list [sqlite3_column_type $stmt 0] \
626 [sqlite3_column_type $stmt 1] \
627 [sqlite3_column_type $stmt 2] \
628 [sqlite3_column_type $stmt 3]
629 } {NULL NULL NULL NULL}
630 do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
632 #-------------------------------------------------------------------------
633 # "Test" the syntax diagrams in lang_expr.html.
635 # -- syntax diagram signed-number
637 do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
638 do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
639 do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
640 do_execsql_test e_expr-12.1.4 {
641 SELECT 1.4, +1.4, -1.4
643 do_execsql_test e_expr-12.1.5 {
644 SELECT 1.5e+5, +1.5e+5, -1.5e+5
645 } {150000.0 150000.0 -150000.0}
646 do_execsql_test e_expr-12.1.6 {
647 SELECT 0.0001, +0.0001, -0.0001
648 } {0.0001 0.0001 -0.0001}
650 # -- syntax diagram literal-value
652 set sqlite_current_time 1
653 do_execsql_test e_expr-12.2.1 {SELECT 123} {123}
654 do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}
655 do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}
656 do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}
657 do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}
658 do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}
659 do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}
660 do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
661 set sqlite_current_time 0
663 # -- syntax diagram expr
667 ATTACH 'test.db2' AS dbname;
668 CREATE TABLE dbname.tblname(cname);
671 proc glob {args} {return 1}
672 db function glob glob
673 db function match glob
674 db function regexp glob
695 17 dbname.tblname.cname
721 41 "EXPR1 IS NOT EXPR2"
726 45 "count(DISTINCT EXPR)"
727 46 "substr(EXPR, 10, 20)"
732 49 "CAST ( EXPR AS integer )"
733 50 "CAST ( EXPR AS 'abcd' )"
734 51 "CAST ( EXPR AS 'ab$ $cd' )"
736 52 "EXPR COLLATE nocase"
737 53 "EXPR COLLATE binary"
739 54 "EXPR1 LIKE EXPR2"
740 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
741 56 "EXPR1 GLOB EXPR2"
742 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
743 58 "EXPR1 REGEXP EXPR2"
744 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
745 60 "EXPR1 MATCH EXPR2"
746 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
747 62 "EXPR1 NOT LIKE EXPR2"
748 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
749 64 "EXPR1 NOT GLOB EXPR2"
750 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
751 66 "EXPR1 NOT REGEXP EXPR2"
752 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
753 68 "EXPR1 NOT MATCH EXPR2"
754 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
761 74 "EXPR1 IS NOT EXPR2"
763 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
764 76 "EXPR BETWEEN EXPR1 AND EXPR2"
766 77 "EXPR NOT IN (SELECT cname FROM tblname)"
768 79 "EXPR NOT IN (1, 2, 3)"
769 80 "EXPR NOT IN tblname"
770 81 "EXPR NOT IN dbname.tblname"
771 82 "EXPR IN (SELECT cname FROM tblname)"
773 84 "EXPR IN (1, 2, 3)"
775 86 "EXPR IN dbname.tblname"
777 87 "EXISTS (SELECT cname FROM tblname)"
778 88 "NOT EXISTS (SELECT cname FROM tblname)"
780 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
781 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
782 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
783 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
784 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
785 94 "CASE WHEN EXPR1 THEN EXPR2 END"
786 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
787 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
790 # If the expression string being parsed contains "EXPR2", then replace
791 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it
792 # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
794 set elist [list $expr]
795 if {[string match *EXPR2* $expr]} {
797 foreach {e1 e2} { cname "34+22" } {
798 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
801 if {[string match *EXPR* $expr]} {
804 foreach e { cname "34+22" } {
805 lappend elist2 [string map [list EXPR $e] $el]
814 do_test e_expr-12.3.$tn.$x {
815 set rc [catch { execsql "SELECT $e FROM tblname" } msg]
820 # -- syntax diagram raise-function
822 foreach {tn raiseexpr} {
824 2 "RAISE(ROLLBACK, 'error message')"
825 3 "RAISE(ABORT, 'error message')"
826 4 "RAISE(FAIL, 'error message')"
828 do_execsql_test e_expr-12.4.$tn "
829 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
835 #-------------------------------------------------------------------------
836 # Test the statements related to the BETWEEN operator.
838 # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
839 # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
840 # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
841 # only evaluated once.
844 proc x {} { incr ::xcount ; return [expr $::x] }
845 foreach {tn x expr res nEval} {
846 1 10 "x() >= 5 AND x() <= 15" 1 2
847 2 10 "x() BETWEEN 5 AND 15" 1 1
849 3 5 "x() >= 5 AND x() <= 5" 1 2
850 4 5 "x() BETWEEN 5 AND 5" 1 1
852 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2
853 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1
855 do_test e_expr-13.1.$tn {
857 set a [execsql "SELECT $expr"]
862 # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
863 # the same as the precedence as operators == and != and LIKE and groups
866 # Therefore, BETWEEN groups more tightly than operator "AND", but less
869 do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1
870 do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1
871 do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0
872 do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1
873 do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1
874 do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0
876 do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1
877 do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1
878 do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0
879 do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1
880 do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1
881 do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0
883 do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1
884 do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1
885 do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0
886 do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1
887 do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1
888 do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0
890 do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0
891 do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
892 do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
893 do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0
894 do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
895 do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
897 do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1
898 do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
899 do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
900 do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0
901 do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0
902 do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1
904 #-------------------------------------------------------------------------
905 # Test the statements related to the LIKE and GLOB operators.
907 # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
910 # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
911 # operator contains the pattern and the left hand operand contains the
912 # string to match against the pattern.
914 do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
915 do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
917 # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
918 # matches any sequence of zero or more characters in the string.
920 do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1
921 do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1
922 do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
924 # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
925 # matches any single character in the string.
927 do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0
928 do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1
929 do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
931 # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
932 # lower/upper case equivalent (i.e. case-insensitive matching).
934 do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
935 do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
936 do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0
938 # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
939 # for ASCII characters by default.
941 # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
942 # default for unicode characters that are beyond the ASCII range.
944 # EVIDENCE-OF: R-44381-11669 the expression
945 # 'a' LIKE 'A' is TRUE but
946 # 'æ' LIKE 'Æ' is FALSE.
948 # The restriction to ASCII characters does not apply if the ICU
949 # library is compiled in. When ICU is enabled SQLite does not act
950 # as it does "by default".
952 do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1
954 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
957 # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
958 # then the expression following the ESCAPE keyword must evaluate to a
959 # string consisting of a single character.
961 do_catchsql_test e_expr-14.6.1 {
962 SELECT 'A' LIKE 'a' ESCAPE '12'
963 } {1 {ESCAPE expression must be a single character}}
964 do_catchsql_test e_expr-14.6.2 {
965 SELECT 'A' LIKE 'a' ESCAPE ''
966 } {1 {ESCAPE expression must be a single character}}
967 do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1}
968 do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
970 # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
971 # pattern to include literal percent or underscore characters.
973 # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
974 # symbol (%), underscore (_), or a second instance of the escape
975 # character itself matches a literal percent symbol, underscore, or a
976 # single escape character, respectively.
978 do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1
979 do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0
980 do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0
981 do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
982 do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
984 do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1
985 do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0
986 do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0
987 do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
988 do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
990 do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1
991 do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0
992 do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0
993 do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
995 # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
996 # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
998 proc likefunc {args} {
999 eval lappend ::likeargs $args
1002 db func like -argcount 2 likefunc
1003 db func like -argcount 3 likefunc
1004 set ::likeargs [list]
1005 do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
1006 do_test e_expr-15.1.2 { set likeargs } {def abc}
1007 set ::likeargs [list]
1008 do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
1009 do_test e_expr-15.1.4 { set likeargs } {def abc X}
1013 # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
1014 # sensitive using the case_sensitive_like pragma.
1016 do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1017 do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
1018 do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}
1019 do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
1020 do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0
1021 do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1022 do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
1023 do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
1024 do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
1025 do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
1026 do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
1027 do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
1029 # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
1030 # uses the Unix file globbing syntax for its wildcards.
1032 # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
1034 do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
1035 do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
1036 do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
1037 do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
1039 do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
1040 do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
1041 do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
1043 # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
1044 # NOT keyword to invert the sense of the test.
1046 do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
1047 do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
1048 do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
1049 do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
1050 do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
1053 do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
1054 do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
1055 do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
1056 do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
1059 # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
1060 # calling the function glob(Y,X) and can be modified by overriding that
1062 proc globfunc {args} {
1063 eval lappend ::globargs $args
1066 db func glob -argcount 2 globfunc
1067 set ::globargs [list]
1068 do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
1069 do_test e_expr-17.3.2 { set globargs } {def abc}
1070 set ::globargs [list]
1071 do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
1072 do_test e_expr-17.3.4 { set globargs } {Y X}
1075 # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
1076 # default and so use of the REGEXP operator will normally result in an
1079 # There is a regexp function if ICU is enabled though.
1082 do_catchsql_test e_expr-18.1.1 {
1083 SELECT regexp('abc', 'def')
1084 } {1 {no such function: regexp}}
1085 do_catchsql_test e_expr-18.1.2 {
1086 SELECT 'abc' REGEXP 'def'
1087 } {1 {no such function: REGEXP}}
1090 # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
1091 # the regexp() user function.
1093 # EVIDENCE-OF: R-65524-61849 If an application-defined SQL function
1094 # named "regexp" is added at run-time, then the "X REGEXP Y" operator
1095 # will be implemented as a call to "regexp(Y,X)".
1097 proc regexpfunc {args} {
1098 eval lappend ::regexpargs $args
1101 db func regexp -argcount 2 regexpfunc
1102 set ::regexpargs [list]
1103 do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
1104 do_test e_expr-18.2.2 { set regexpargs } {def abc}
1105 set ::regexpargs [list]
1106 do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
1107 do_test e_expr-18.2.4 { set regexpargs } {Y X}
1110 # EVIDENCE-OF: R-42037-37826 The default match() function implementation
1111 # raises an exception and is not really useful for anything.
1113 do_catchsql_test e_expr-19.1.1 {
1114 SELECT 'abc' MATCH 'def'
1115 } {1 {unable to use function MATCH in the requested context}}
1116 do_catchsql_test e_expr-19.1.2 {
1117 SELECT match('abc', 'def')
1118 } {1 {unable to use function MATCH in the requested context}}
1120 # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
1121 # the match() application-defined function.
1123 # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
1124 # function with more helpful logic.
1126 proc matchfunc {args} {
1127 eval lappend ::matchargs $args
1130 db func match -argcount 2 matchfunc
1131 set ::matchargs [list]
1132 do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
1133 do_test e_expr-19.2.2 { set matchargs } {def abc}
1134 set ::matchargs [list]
1135 do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
1136 do_test e_expr-19.2.4 { set matchargs } {Y X}
1139 #-------------------------------------------------------------------------
1140 # Test cases for the testable statements related to the CASE expression.
1142 # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
1143 # expression: those with a base expression and those without.
1145 do_execsql_test e_expr-20.1 {
1146 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1148 do_execsql_test e_expr-20.2 {
1149 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
1153 lappend ::varlist $nm
1154 return [set "::$nm"]
1158 # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
1159 # WHEN expression is evaluated and the result treated as a boolean,
1160 # starting with the leftmost and continuing to the right.
1162 foreach {a b c} {0 0 0} break
1164 do_execsql_test e_expr-21.1.1 {
1165 SELECT CASE WHEN var('a') THEN 'A'
1166 WHEN var('b') THEN 'B'
1167 WHEN var('c') THEN 'C' END
1169 do_test e_expr-21.1.2 { set varlist } {a b c}
1171 do_execsql_test e_expr-21.1.3 {
1172 SELECT CASE WHEN var('c') THEN 'C'
1173 WHEN var('b') THEN 'B'
1174 WHEN var('a') THEN 'A'
1178 do_test e_expr-21.1.4 { set varlist } {c b a}
1180 # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
1181 # evaluation of the THEN expression that corresponds to the first WHEN
1182 # expression that evaluates to true.
1184 foreach {a b c} {0 1 0} break
1185 do_execsql_test e_expr-21.2.1 {
1186 SELECT CASE WHEN var('a') THEN 'A'
1187 WHEN var('b') THEN 'B'
1188 WHEN var('c') THEN 'C'
1192 foreach {a b c} {0 1 1} break
1193 do_execsql_test e_expr-21.2.2 {
1194 SELECT CASE WHEN var('a') THEN 'A'
1195 WHEN var('b') THEN 'B'
1196 WHEN var('c') THEN 'C'
1200 foreach {a b c} {0 0 1} break
1201 do_execsql_test e_expr-21.2.3 {
1202 SELECT CASE WHEN var('a') THEN 'A'
1203 WHEN var('b') THEN 'B'
1204 WHEN var('c') THEN 'C'
1209 # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
1210 # evaluate to true, the result of evaluating the ELSE expression, if
1213 foreach {a b c} {0 0 0} break
1214 do_execsql_test e_expr-21.3.1 {
1215 SELECT CASE WHEN var('a') THEN 'A'
1216 WHEN var('b') THEN 'B'
1217 WHEN var('c') THEN 'C'
1222 # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
1223 # the WHEN expressions are true, then the overall result is NULL.
1226 do_execsql_test e_expr-21.3.2 {
1227 SELECT CASE WHEN var('a') THEN 'A'
1228 WHEN var('b') THEN 'B'
1229 WHEN var('c') THEN 'C'
1234 # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
1235 # evaluating WHEN terms.
1237 do_execsql_test e_expr-21.4.1 {
1238 SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
1240 do_execsql_test e_expr-21.4.2 {
1241 SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
1244 # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
1245 # expression is evaluated just once and the result is compared against
1246 # the evaluation of each WHEN expression from left to right.
1248 # Note: This test case tests the "evaluated just once" part of the above
1249 # statement. Tests associated with the next two statements test that the
1250 # comparisons take place.
1252 foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
1253 set ::varlist [list]
1254 do_execsql_test e_expr-22.1.1 {
1255 SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
1257 do_test e_expr-22.1.2 { set ::varlist } {a}
1259 # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
1260 # evaluation of the THEN expression that corresponds to the first WHEN
1261 # expression for which the comparison is true.
1263 do_execsql_test e_expr-22.2.1 {
1264 SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1266 do_execsql_test e_expr-22.2.2 {
1267 SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1270 # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
1271 # evaluate to a value equal to the base expression, the result of
1272 # evaluating the ELSE expression, if any.
1274 do_execsql_test e_expr-22.3.1 {
1275 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
1278 # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
1279 # the WHEN expressions produce a result equal to the base expression,
1280 # the overall result is NULL.
1282 do_execsql_test e_expr-22.4.1 {
1283 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1286 do_execsql_test e_expr-22.4.2 {
1287 SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
1291 # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
1292 # WHEN expression, the same collating sequence, affinity, and
1293 # NULL-handling rules apply as if the base expression and WHEN
1294 # expression are respectively the left- and right-hand operands of an =
1299 set chars [split $str]
1300 for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
1301 append ret [lindex $chars $i]
1305 proc reverse {lhs rhs} {
1306 string compare [rev $lhs] [rev $rhs]
1308 db collate reverse reverse
1309 do_execsql_test e_expr-23.1.1 {
1311 a TEXT COLLATE NOCASE,
1316 INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
1318 do_execsql_test e_expr-23.1.2 {
1319 SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
1321 do_execsql_test e_expr-23.1.3 {
1322 SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
1324 do_execsql_test e_expr-23.1.4 {
1325 SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
1327 do_execsql_test e_expr-23.1.5 {
1328 SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
1330 do_execsql_test e_expr-23.1.6 {
1331 SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
1333 do_execsql_test e_expr-23.1.7 {
1334 SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
1336 do_execsql_test e_expr-23.1.8 {
1337 SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
1339 do_execsql_test e_expr-23.1.9 {
1340 SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
1343 # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
1344 # result of the CASE is always the result of evaluating the ELSE
1345 # expression if it exists, or NULL if it does not.
1347 do_execsql_test e_expr-24.1.1 {
1348 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
1350 do_execsql_test e_expr-24.1.2 {
1351 SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
1354 # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
1355 # or short-circuit, evaluation.
1358 foreach {a b c} {0 1 0} break
1359 do_execsql_test e_expr-25.1.1 {
1360 SELECT CASE WHEN var('a') THEN 'A'
1361 WHEN var('b') THEN 'B'
1362 WHEN var('c') THEN 'C'
1365 do_test e_expr-25.1.2 { set ::varlist } {a b}
1367 do_execsql_test e_expr-25.1.3 {
1368 SELECT CASE '0' WHEN var('a') THEN 'A'
1369 WHEN var('b') THEN 'B'
1370 WHEN var('c') THEN 'C'
1373 do_test e_expr-25.1.4 { set ::varlist } {a}
1375 # EVIDENCE-OF: R-34773-62253 The only difference between the following
1376 # two CASE expressions is that the x expression is evaluated exactly
1377 # once in the first example but might be evaluated multiple times in the
1378 # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
1379 # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
1388 do_execsql_test e_expr-26.1.1 {
1389 CREATE TABLE t2(x, w1, r1, w2, r2, r3);
1390 INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
1391 INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
1392 INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
1394 do_execsql_test e_expr-26.1.2 {
1395 SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1397 do_execsql_test e_expr-26.1.3 {
1398 SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
1401 do_execsql_test e_expr-26.1.4 {
1402 SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
1404 do_test e_expr-26.1.5 { set ::evalcount } {3}
1406 do_execsql_test e_expr-26.1.6 {
1408 WHEN ceval(x)=w1 THEN r1
1409 WHEN ceval(x)=w2 THEN r2
1413 do_test e_expr-26.1.6 { set ::evalcount } {5}
1416 #-------------------------------------------------------------------------
1417 # Test statements related to CAST expressions.
1419 # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
1420 # conversion that takes place when a column affinity is applied to a
1421 # value except that with the CAST operator the conversion always takes
1422 # place even if the conversion lossy and irreversible, whereas column
1423 # affinity only changes the data type of a value if the change is
1424 # lossless and reversible.
1426 do_execsql_test e_expr-27.1.1 {
1427 CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
1428 INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
1429 SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
1430 } {blob UVU text 1.23abc real 4.5}
1431 do_execsql_test e_expr-27.1.2 {
1433 typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
1434 typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
1435 typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
1436 } {text UVU real 1.23 integer 4}
1438 # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
1439 # result of the CAST expression is also NULL.
1441 do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
1442 do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {}
1443 do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {}
1444 do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {}
1446 # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
1447 # is determined by applying the rules for determining column affinity to
1450 # The R-29283-15561 requirement above is demonstrated by all of the
1451 # subsequent e_expr-26 tests.
1453 # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
1454 # affinity causes the value to be converted into a BLOB.
1456 do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc
1457 do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
1458 do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi
1460 # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
1461 # the value to TEXT in the encoding of the database connection, then
1462 # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
1464 do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
1465 do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536'
1466 do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738'
1470 db eval { PRAGMA encoding = 'utf-16le' }
1471 do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
1472 do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600'
1473 do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800'
1477 db eval { PRAGMA encoding = 'utf-16be' }
1479 do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
1480 do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036'
1481 do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038'
1486 # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
1487 # of bytes that make up the BLOB is interpreted as text encoded using
1488 # the database encoding.
1490 do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
1491 do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
1494 db eval { PRAGMA encoding = 'utf-16le' }
1496 do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
1497 do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
1502 # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
1503 # renders the value as if via sqlite3_snprintf() except that the
1504 # resulting TEXT uses the encoding of the database connection.
1506 do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1
1507 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45
1508 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45
1509 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8
1510 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0
1511 do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
1512 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0
1513 do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0
1515 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
1516 # value is first converted to TEXT.
1518 do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
1519 do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
1520 do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
1521 do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
1525 db eval { PRAGMA encoding = 'utf-16le' }
1526 do_expr_test e_expr-29.1.5 {
1527 CAST (X'31002E0032003300' AS REAL) } real 1.23
1528 do_expr_test e_expr-29.1.6 {
1529 CAST (X'3200330030002E003000' AS REAL) } real 230.0
1530 do_expr_test e_expr-29.1.7 {
1531 CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
1532 do_expr_test e_expr-29.1.8 {
1533 CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
1538 # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
1539 # longest possible prefix of the value that can be interpreted as a real
1540 # number is extracted from the TEXT value and the remainder ignored.
1542 do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
1543 do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
1544 do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
1545 do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
1547 # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
1548 # ignored when converging from TEXT to REAL.
1550 do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
1551 do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45
1552 do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212
1553 do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
1555 # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
1556 # interpreted as a real number, the result of the conversion is 0.0.
1558 do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
1559 do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
1560 do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
1562 # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
1563 # value is first converted to TEXT.
1565 do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
1566 do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
1567 do_expr_test e_expr-30.1.3 {
1568 CAST(X'31303030303030' AS INTEGER)
1570 do_expr_test e_expr-30.1.4 {
1571 CAST(X'2D31313235383939393036383432363234' AS INTEGER)
1572 } integer -1125899906842624
1577 execsql { PRAGMA encoding = 'utf-16be' }
1578 do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
1579 do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
1580 do_expr_test e_expr-30.1.7 {
1581 CAST(X'0031003000300030003000300030' AS INTEGER)
1583 do_expr_test e_expr-30.1.8 {
1584 CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER)
1585 } integer -1125899906842624
1590 # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
1591 # longest possible prefix of the value that can be interpreted as an
1592 # integer number is extracted from the TEXT value and the remainder
1595 do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
1596 do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
1597 do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
1598 do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
1600 # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
1601 # converting from TEXT to INTEGER are ignored.
1603 do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123
1604 do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523
1605 do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
1606 do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1
1608 # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
1609 # interpreted as an integer number, the result of the conversion is 0.
1611 do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
1612 do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
1613 do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
1615 # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
1616 # integers only — conversion of hexadecimal integers stops at
1617 # the "x" in the "0x" prefix of the hexadecimal integer string and thus
1618 # result of the CAST is always zero.
1619 do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
1620 do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
1622 # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
1623 # results in the integer between the REAL value and zero that is closest
1624 # to the REAL value.
1626 do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
1627 do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
1628 do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
1629 do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
1631 # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
1632 # possible signed integer (+9223372036854775807) then the result is the
1633 # greatest possible signed integer and if the REAL is less than the
1634 # least possible signed integer (-9223372036854775808) then the result
1635 # is the least possible signed integer.
1637 do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
1638 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
1639 do_expr_test e_expr-31.2.3 {
1640 CAST(-9223372036854775809.0 AS INT)
1641 } integer -9223372036854775808
1642 do_expr_test e_expr-31.2.4 {
1643 CAST(9223372036854775809.0 AS INT)
1644 } integer 9223372036854775807
1647 # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
1648 # yields either an INTEGER or a REAL result.
1650 # EVIDENCE-OF: R-48945-04866 If the input text looks like an integer
1651 # (there is no decimal point nor exponent) and the value is small enough
1652 # to fit in a 64-bit signed integer, then the result will be INTEGER.
1654 # EVIDENCE-OF: R-47045-23194 Input text that looks like floating point
1655 # (there is a decimal point and/or an exponent) and the text describes a
1656 # value that can be losslessly converted back and forth between IEEE 754
1657 # 64-bit float and a 51-bit signed integer, then the result is INTEGER.
1659 do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45
1660 do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45
1661 do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2
1662 do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
1663 do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
1664 do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000
1665 do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000
1666 do_test e_expr-32.1.8 {
1667 set expr {CAST( '9.223372036e15' AS NUMERIC)}
1668 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
1670 } {real 9.22337e+15}
1671 do_test e_expr-32.1.9 {
1672 set expr {CAST('-9.223372036e15' AS NUMERIC)}
1673 db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break;
1675 } {real -9.22337e+15}
1677 # EVIDENCE-OF: R-50300-26941 Any text input that describes a value
1678 # outside the range of a 64-bit signed integer yields a REAL result.
1680 do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \
1681 integer 9223372036854775807
1682 do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \
1683 real 9.22337203685478e+18
1684 do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \
1685 integer -9223372036854775808
1686 do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \
1687 real -9.22337203685478e+18
1689 # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
1690 # is a no-op, even if a real value could be losslessly converted to an
1693 do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
1694 do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
1696 do_expr_test e_expr-32.2.3 {
1697 CAST(-9223372036854775808 AS NUMERIC)
1698 } integer -9223372036854775808
1699 do_expr_test e_expr-32.2.4 {
1700 CAST(9223372036854775807 AS NUMERIC)
1701 } integer 9223372036854775807
1702 do_expr_test e_expr-32.2.5 {
1703 CAST('9223372036854775807 ' AS NUMERIC)
1704 } integer 9223372036854775807
1705 do_expr_test e_expr-32.2.6 {
1706 CAST(' 9223372036854775807 ' AS NUMERIC)
1707 } integer 9223372036854775807
1708 do_expr_test e_expr-32.2.7 {
1709 CAST(' ' AS NUMERIC)
1711 do_execsql_test e_expr-32.2.8 {
1712 WITH t1(x) AS (VALUES
1713 ('9000000000000000001'),
1714 ('9000000000000000001x'),
1715 ('9000000000000000001 '),
1716 (' 9000000000000000001 '),
1717 (' 9000000000000000001'),
1718 (' 9000000000000000001.'),
1719 ('9223372036854775807'),
1720 ('9223372036854775807 '),
1721 (' 9223372036854775807 '),
1722 ('9223372036854775808'),
1723 (' 9223372036854775808 '),
1724 ('9223372036854775807.0'),
1725 ('9223372036854775807e+0'),
1728 SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
1730 integer 9000000000000000001 \
1731 integer 9000000000000000001 \
1732 integer 9000000000000000001 \
1733 integer 9000000000000000001 \
1734 integer 9000000000000000001 \
1736 integer 9223372036854775807 \
1737 integer 9223372036854775807 \
1738 integer 9223372036854775807 \
1739 real 9.22337203685478e+18 \
1740 real 9.22337203685478e+18 \
1741 real 9.22337203685478e+18 \
1742 real 9.22337203685478e+18 \
1747 # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
1748 # non-BLOB value into a BLOB and the result from casting any BLOB value
1749 # into a non-BLOB value may be different depending on whether the
1750 # database encoding is UTF-8, UTF-16be, or UTF-16le.
1753 sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
1754 sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
1755 sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
1756 foreach {tn castexpr differs} {
1757 1 { CAST(123 AS BLOB) } 1
1758 2 { CAST('' AS BLOB) } 0
1759 3 { CAST('abcd' AS BLOB) } 1
1761 4 { CAST(X'abcd' AS TEXT) } 1
1762 5 { CAST(X'' AS TEXT) } 0
1764 set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
1765 set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
1766 set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
1769 set res [expr {$r1!=$r2 && $r2!=$r3}]
1771 set res [expr {$r1==$r2 && $r2==$r3}]
1774 do_test e_expr-33.1.$tn {set res} 1
1781 #-------------------------------------------------------------------------
1782 # Test statements related to the EXISTS and NOT EXISTS operators.
1788 do_execsql_test e_expr-34.1 {
1789 CREATE TABLE t1(a, b);
1790 INSERT INTO t1 VALUES(1, 2);
1791 INSERT INTO t1 VALUES(NULL, 2);
1792 INSERT INTO t1 VALUES(1, NULL);
1793 INSERT INTO t1 VALUES(NULL, NULL);
1796 # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
1797 # of the integer values 0 and 1.
1799 # This statement is not tested by itself. Instead, all e_expr-34.* tests
1800 # following this point explicitly test that specific invocations of EXISTS
1801 # return either integer 0 or integer 1.
1804 # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
1805 # as the right-hand operand of the EXISTS operator would return one or
1806 # more rows, then the EXISTS operator evaluates to 1.
1809 1 { EXISTS ( SELECT a FROM t1 ) }
1810 2 { EXISTS ( SELECT b FROM t1 ) }
1811 3 { EXISTS ( SELECT 24 ) }
1812 4 { EXISTS ( SELECT NULL ) }
1813 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
1815 do_expr_test e_expr-34.2.$tn $expr integer 1
1818 # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
1819 # rows at all, then the EXISTS operator evaluates to 0.
1822 1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
1823 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
1824 3 { EXISTS ( SELECT 24 WHERE 0) }
1825 4 { EXISTS ( SELECT NULL WHERE 1=2) }
1827 do_expr_test e_expr-34.3.$tn $expr integer 0
1830 # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
1831 # by the SELECT statement (if any) and the specific values returned have
1832 # no effect on the results of the EXISTS operator.
1834 foreach {tn expr res} {
1835 1 { EXISTS ( SELECT * FROM t1 ) } 1
1836 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1
1837 3 { EXISTS ( SELECT 24, 25 ) } 1
1838 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1
1839 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1
1841 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0
1842 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0
1843 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0
1844 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0
1846 do_expr_test e_expr-34.4.$tn $expr integer $res
1849 # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
1850 # are not handled any differently from rows without NULL values.
1852 foreach {tn e1 e2} {
1853 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) }
1854 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) }
1856 set res [db one "SELECT $e1"]
1857 do_expr_test e_expr-34.5.${tn}a $e1 integer $res
1858 do_expr_test e_expr-34.5.${tn}b $e2 integer $res
1861 #-------------------------------------------------------------------------
1862 # Test statements related to scalar sub-queries.
1868 do_test e_expr-35.0 {
1870 CREATE TABLE t2(a, b);
1871 INSERT INTO t2 VALUES('one', 'two');
1872 INSERT INTO t2 VALUES('three', NULL);
1873 INSERT INTO t2 VALUES(4, 5.0);
1877 # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
1880 # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
1881 # aggregate and compound SELECT queries (queries with keywords like
1882 # UNION or EXCEPT) are allowed as scalar subqueries.
1884 do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35
1885 do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
1887 do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
1888 do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
1890 do_expr_test e_expr-35.1.5 {
1891 (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
1893 do_expr_test e_expr-35.1.6 {
1894 (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
1897 # EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns
1898 # is a row value subquery and can only be used as the operand of a
1899 # comparison operator.
1901 # The following block tests that errors are returned in a bunch of cases
1902 # where a subquery returns more than one column.
1904 set M {/1 {sub-select returns [23] columns - expected 1}/}
1906 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
1907 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
1908 3 { SELECT (SELECT 1, 2) }
1909 4 { SELECT (SELECT NULL, NULL, NULL) }
1910 5 { SELECT (SELECT * FROM t2) }
1911 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
1913 do_catchsql_test e_expr-35.2.$tn $sql $M
1916 # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the
1917 # first row of the result from the enclosed SELECT statement.
1919 do_execsql_test e_expr-36.3.1 {
1920 CREATE TABLE t4(x, y);
1921 INSERT INTO t4 VALUES(1, 'one');
1922 INSERT INTO t4 VALUES(2, 'two');
1923 INSERT INTO t4 VALUES(3, 'three');
1926 foreach {tn expr restype resval} {
1927 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1
1928 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1
1929 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3
1930 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2
1931 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two
1933 7 { ( SELECT sum(x) FROM t4 ) } integer 6
1934 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree
1935 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2
1938 do_expr_test e_expr-36.3.$tn $expr $restype $resval
1941 # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
1942 # if the enclosed SELECT statement returns no rows.
1945 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) }
1946 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) }
1948 do_expr_test e_expr-36.4.$tn $expr null {}
1951 # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
1952 # 'english' and '0' are all considered to be false.
1954 do_execsql_test e_expr-37.1 {
1955 SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END;
1957 do_execsql_test e_expr-37.2 {
1958 SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END;
1960 do_execsql_test e_expr-37.3 {
1961 SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END;
1963 do_execsql_test e_expr-37.4 {
1964 SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END;
1966 do_execsql_test e_expr-37.5 {
1967 SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END;
1970 # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
1971 # considered to be true.
1973 do_execsql_test e_expr-37.6 {
1974 SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END;
1976 do_execsql_test e_expr-37.7 {
1977 SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END;
1979 do_execsql_test e_expr-37.8 {
1980 SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END;
1982 do_execsql_test e_expr-37.9 {
1983 SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END;
1985 do_execsql_test e_expr-37.10 {
1986 SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END;