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