1# 2009 November 11
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# This file implements regression tests for SQLite library.  The
12# focus of this file is testing built-in functions.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18# Test plan:
19#
20#   func2-1.*: substr implementation (ascii)
21#   func2-2.*: substr implementation (utf8)
22#   func2-3.*: substr implementation (blob)
23#
24
25proc bin_to_hex {blob} {
26  set bytes {}
27  binary scan $blob \c* bytes
28  set bytes2 [list]
29  foreach b $bytes {lappend bytes2 [format %02X [expr $b & 0xFF]]}
30  join $bytes2 {}
31}
32
33#----------------------------------------------------------------------------
34# Test cases func2-1.*: substr implementation (ascii)
35#
36
37do_test func2-1.1 {
38  execsql {SELECT 'Supercalifragilisticexpialidocious'}
39} {Supercalifragilisticexpialidocious}
40
41# substr(x,y), substr(x,y,z)
42do_test func2-1.2.1 {
43  catchsql {SELECT SUBSTR()}
44} {1 {wrong number of arguments to function SUBSTR()}}
45do_test func2-1.2.2 {
46  catchsql {SELECT SUBSTR('Supercalifragilisticexpialidocious')}
47} {1 {wrong number of arguments to function SUBSTR()}}
48do_test func2-1.2.3 {
49  catchsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 1,1,1)}
50} {1 {wrong number of arguments to function SUBSTR()}}
51
52# p1 is 1-indexed
53do_test func2-1.3 {
54  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0)}
55} {Supercalifragilisticexpialidocious}
56do_test func2-1.4 {
57  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 1)}
58} {Supercalifragilisticexpialidocious}
59do_test func2-1.5 {
60  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2)}
61} {upercalifragilisticexpialidocious}
62do_test func2-1.6 {
63  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 30)}
64} {cious}
65do_test func2-1.7 {
66  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 34)}
67} {s}
68do_test func2-1.8 {
69  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 35)}
70} {{}}
71do_test func2-1.9 {
72  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 36)}
73} {{}}
74
75# if p1<0, start from right
76do_test func2-1.10 {
77  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -0)}
78} {Supercalifragilisticexpialidocious}
79do_test func2-1.11 {
80  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -1)}
81} {s}
82do_test func2-1.12 {
83  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -2)}
84} {us}
85do_test func2-1.13 {
86  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -30)}
87} {rcalifragilisticexpialidocious}
88do_test func2-1.14 {
89  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -34)}
90} {Supercalifragilisticexpialidocious}
91do_test func2-1.15 {
92  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -35)}
93} {Supercalifragilisticexpialidocious}
94do_test func2-1.16 {
95  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -36)}
96} {Supercalifragilisticexpialidocious}
97
98# p1 is 1-indexed, p2 length to return
99do_test func2-1.17.1 {
100  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, 1)}
101} {{}}
102do_test func2-1.17.2 {
103  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, 2)}
104} {S}
105do_test func2-1.18 {
106  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 1, 1)}
107} {S}
108do_test func2-1.19.0 {
109  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, 0)}
110} {{}}
111do_test func2-1.19.1 {
112  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, 1)}
113} {u}
114do_test func2-1.19.2 {
115  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, 2)}
116} {up}
117do_test func2-1.20 {
118  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, 1)}
119} {c}
120do_test func2-1.21 {
121  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 34, 1)}
122} {s}
123do_test func2-1.22 {
124  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 35, 1)}
125} {{}}
126do_test func2-1.23 {
127  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, 1)}
128} {{}}
129
130# if p1<0, start from right, p2 length to return
131do_test func2-1.24 {
132  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -0, 1)}
133} {{}}
134do_test func2-1.25.0 {
135  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 0)}
136} {{}}
137do_test func2-1.25.1 {
138  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 1)}
139} {s}
140do_test func2-1.25.2 {
141  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 2)}
142} {s}
143do_test func2-1.26 {
144  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -2, 1)}
145} {u}
146do_test func2-1.27 {
147  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -30, 1)}
148} {r}
149do_test func2-1.28.0 {
150  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 0)}
151} {{}}
152do_test func2-1.28.1 {
153  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 1)}
154} {S}
155do_test func2-1.28.2 {
156  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 2)}
157} {Su}
158do_test func2-1.29.1 {
159  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -35, 1)}
160} {{}}
161do_test func2-1.29.2 {
162  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -35, 2)}
163} {S}
164do_test func2-1.30.0 {
165  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 0)}
166} {{}}
167do_test func2-1.30.1 {
168  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 1)}
169} {{}}
170do_test func2-1.30.2 {
171  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 2)}
172} {{}}
173do_test func2-1.30.3 {
174  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 3)}
175} {S}
176
177# p1 is 1-indexed, p2 length to return, p2<0 return p2 chars before p1
178do_test func2-1.31.0 {
179  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, 0)}
180} {{}}
181do_test func2-1.31.1 {
182  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -1)}
183} {{}}
184do_test func2-1.31.2 {
185  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -2)}
186} {{}}
187do_test func2-1.32.0 {
188  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 1, 0)}
189} {{}}
190do_test func2-1.32.1 {
191  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 1, -1)}
192} {{}}
193do_test func2-1.33.0 {
194  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, 0)}
195} {{}}
196do_test func2-1.33.1 {
197  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -1)}
198} {S}
199do_test func2-1.33.2 {
200  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -2)}
201} {S}
202do_test func2-1.34.0 {
203  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, 0)}
204} {{}}
205do_test func2-1.34.1 {
206  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -1)}
207} {u}
208do_test func2-1.34.2 {
209  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -2)}
210} {Su}
211do_test func2-1.35.1 {
212  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -1)}
213} {o}
214do_test func2-1.35.2 {
215  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -2)}
216} {do}
217do_test func2-1.36 {
218  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 34, -1)}
219} {u}
220do_test func2-1.37 {
221  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 35, -1)}
222} {s}
223do_test func2-1.38.0 {
224  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, 0)}
225} {{}}
226do_test func2-1.38.1 {
227  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -1)}
228} {{}}
229do_test func2-1.38.2 {
230  execsql {SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -2)}
231} {s}
232
233
234#----------------------------------------------------------------------------
235# Test cases func2-2.*: substr implementation (utf8)
236#
237
238# Only do the following tests if TCL has UTF-8 capabilities
239#
240if {"\u1234"!="u1234"} {
241
242do_test func2-2.1.1 {
243  execsql "SELECT 'hi\u1234ho'"
244} "hi\u1234ho"
245
246# substr(x,y), substr(x,y,z)
247do_test func2-2.1.2 {
248  catchsql "SELECT SUBSTR()"
249} {1 {wrong number of arguments to function SUBSTR()}}
250do_test func2-2.1.3 {
251  catchsql "SELECT SUBSTR('hi\u1234ho')"
252} {1 {wrong number of arguments to function SUBSTR()}}
253do_test func2-2.1.4 {
254  catchsql "SELECT SUBSTR('hi\u1234ho', 1,1,1)"
255} {1 {wrong number of arguments to function SUBSTR()}}
256
257do_test func2-2.2.0 {
258  execsql "SELECT SUBSTR('hi\u1234ho', 0, 0)"
259} {{}}
260do_test func2-2.2.1 {
261  execsql "SELECT SUBSTR('hi\u1234ho', 0, 1)"
262} {{}}
263do_test func2-2.2.2 {
264  execsql "SELECT SUBSTR('hi\u1234ho', 0, 2)"
265} "h"
266do_test func2-2.2.3 {
267  execsql "SELECT SUBSTR('hi\u1234ho', 0, 3)"
268} "hi"
269do_test func2-2.2.4 {
270  execsql "SELECT SUBSTR('hi\u1234ho', 0, 4)"
271} "hi\u1234"
272do_test func2-2.2.5 {
273  execsql "SELECT SUBSTR('hi\u1234ho', 0, 5)"
274} "hi\u1234h"
275do_test func2-2.2.6 {
276  execsql "SELECT SUBSTR('hi\u1234ho', 0, 6)"
277} "hi\u1234ho"
278
279do_test func2-2.3.0 {
280  execsql "SELECT SUBSTR('hi\u1234ho', 1, 0)"
281} {{}}
282do_test func2-2.3.1 {
283  execsql "SELECT SUBSTR('hi\u1234ho', 1, 1)"
284} "h"
285do_test func2-2.3.2 {
286  execsql "SELECT SUBSTR('hi\u1234ho', 1, 2)"
287} "hi"
288do_test func2-2.3.3 {
289  execsql "SELECT SUBSTR('hi\u1234ho', 1, 3)"
290} "hi\u1234"
291do_test func2-2.3.4 {
292  execsql "SELECT SUBSTR('hi\u1234ho', 1, 4)"
293} "hi\u1234h"
294do_test func2-2.3.5 {
295  execsql "SELECT SUBSTR('hi\u1234ho', 1, 5)"
296} "hi\u1234ho"
297do_test func2-2.3.6 {
298  execsql "SELECT SUBSTR('hi\u1234ho', 1, 6)"
299} "hi\u1234ho"
300
301do_test func2-2.4.0 {
302  execsql "SELECT SUBSTR('hi\u1234ho', 3, 0)"
303} {{}}
304do_test func2-2.4.1 {
305  execsql "SELECT SUBSTR('hi\u1234ho', 3, 1)"
306} "\u1234"
307do_test func2-2.4.2 {
308  execsql "SELECT SUBSTR('hi\u1234ho', 3, 2)"
309} "\u1234h"
310
311do_test func2-2.5.0 {
312  execsql "SELECT SUBSTR('\u1234', 0, 0)"
313} {{}}
314do_test func2-2.5.1 {
315  execsql "SELECT SUBSTR('\u1234', 0, 1)"
316} {{}}
317do_test func2-2.5.2 {
318  execsql "SELECT SUBSTR('\u1234', 0, 2)"
319} "\u1234"
320do_test func2-2.5.3 {
321  execsql "SELECT SUBSTR('\u1234', 0, 3)"
322} "\u1234"
323
324do_test func2-2.6.0 {
325  execsql "SELECT SUBSTR('\u1234', 1, 0)"
326} {{}}
327do_test func2-2.6.1 {
328  execsql "SELECT SUBSTR('\u1234', 1, 1)"
329} "\u1234"
330do_test func2-2.6.2 {
331  execsql "SELECT SUBSTR('\u1234', 1, 2)"
332} "\u1234"
333do_test func2-2.6.3 {
334  execsql "SELECT SUBSTR('\u1234', 1, 3)"
335} "\u1234"
336
337do_test func2-2.7.0 {
338  execsql "SELECT SUBSTR('\u1234', 2, 0)"
339} {{}}
340do_test func2-2.7.1 {
341  execsql "SELECT SUBSTR('\u1234', 2, 1)"
342} {{}}
343do_test func2-2.7.2 {
344  execsql "SELECT SUBSTR('\u1234', 2, 2)"
345} {{}}
346
347do_test func2-2.8.0 {
348  execsql "SELECT SUBSTR('\u1234', -1, 0)"
349} {{}}
350do_test func2-2.8.1 {
351  execsql "SELECT SUBSTR('\u1234', -1, 1)"
352} "\u1234"
353do_test func2-2.8.2 {
354  execsql "SELECT SUBSTR('\u1234', -1, 2)"
355} "\u1234"
356do_test func2-2.8.3 {
357  execsql "SELECT SUBSTR('\u1234', -1, 3)"
358} "\u1234"
359
360} ;# End \u1234!=u1234
361
362#----------------------------------------------------------------------------
363# Test cases func2-3.*: substr implementation (blob)
364#
365
366ifcapable {!bloblit} {
367  finish_test
368  return
369}
370
371do_test func2-3.1.1 {
372  set blob [execsql "SELECT x'1234'"]
373  bin_to_hex [lindex $blob 0]
374} "1234"
375
376# substr(x,y), substr(x,y,z)
377do_test func2-3.1.2 {
378  catchsql {SELECT SUBSTR()}
379} {1 {wrong number of arguments to function SUBSTR()}}
380do_test func2-3.1.3 {
381  catchsql {SELECT SUBSTR(x'1234')}
382} {1 {wrong number of arguments to function SUBSTR()}}
383do_test func2-3.1.4 {
384  catchsql {SELECT SUBSTR(x'1234', 1,1,1)}
385} {1 {wrong number of arguments to function SUBSTR()}}
386
387do_test func2-3.2.0 {
388  set blob [execsql "SELECT SUBSTR(x'1234', 0, 0)"]
389  bin_to_hex [lindex $blob 0]
390} {}
391do_test func2-3.2.1 {
392  set blob [execsql "SELECT SUBSTR(x'1234', 0, 1)"]
393  bin_to_hex [lindex $blob 0]
394} {}
395do_test func2-3.2.2 {
396  set blob [execsql "SELECT SUBSTR(x'1234', 0, 2)"]
397  bin_to_hex [lindex $blob 0]
398} "12"
399do_test func2-3.2.3 {
400  set blob [execsql "SELECT SUBSTR(x'1234', 0, 3)"]
401  bin_to_hex [lindex $blob 0]
402} "1234"
403
404do_test func2-3.3.0 {
405  set blob [execsql "SELECT SUBSTR(x'1234', 1, 0)"]
406  bin_to_hex [lindex $blob 0]
407} {}
408do_test func2-3.3.1 {
409  set blob [execsql "SELECT SUBSTR(x'1234', 1, 1)"]
410  bin_to_hex [lindex $blob 0]
411} "12"
412do_test func2-3.3.2 {
413  set blob [execsql "SELECT SUBSTR(x'1234', 1, 2)"]
414  bin_to_hex [lindex $blob 0]
415} "1234"
416do_test func2-3.3.3 {
417  set blob [execsql "SELECT SUBSTR(x'1234', 1, 3)"]
418  bin_to_hex [lindex $blob 0]
419} "1234"
420
421do_test func2-3.4.0 {
422  set blob [execsql "SELECT SUBSTR(x'1234', -1, 0)"]
423  bin_to_hex [lindex $blob 0]
424} {}
425do_test func2-3.4.1 {
426  set blob [execsql "SELECT SUBSTR(x'1234', -1, 1)"]
427  bin_to_hex [lindex $blob 0]
428} "34"
429do_test func2-3.4.2 {
430  set blob [execsql "SELECT SUBSTR(x'1234', -1, 2)"]
431  bin_to_hex [lindex $blob 0]
432} "34"
433do_test func2-3.4.3 {
434  set blob [execsql "SELECT SUBSTR(x'1234', -1, 3)"]
435  bin_to_hex [lindex $blob 0]
436} "34"
437
438do_test func2-3.5.0 {
439  set blob [execsql "SELECT SUBSTR(x'1234', -2, 0)"]
440  bin_to_hex [lindex $blob 0]
441} {}
442do_test func2-3.5.1 {
443  set blob [execsql "SELECT SUBSTR(x'1234', -2, 1)"]
444  bin_to_hex [lindex $blob 0]
445} "12"
446do_test func2-3.5.2 {
447  set blob [execsql "SELECT SUBSTR(x'1234', -2, 2)"]
448  bin_to_hex [lindex $blob 0]
449} "1234"
450do_test func2-3.5.3 {
451  set blob [execsql "SELECT SUBSTR(x'1234', -2, 3)"]
452  bin_to_hex [lindex $blob 0]
453} "1234"
454
455do_test func2-3.6.0 {
456  set blob [execsql "SELECT SUBSTR(x'1234', -1, 0)"]
457  bin_to_hex [lindex $blob 0]
458} {}
459do_test func2-3.6.1 {
460  set blob [execsql "SELECT SUBSTR(x'1234', -1, -1)"]
461  bin_to_hex [lindex $blob 0]
462} "12"
463do_test func2-3.6.2 {
464  set blob [execsql "SELECT SUBSTR(x'1234', -1, -2)"]
465  bin_to_hex [lindex $blob 0]
466} "12"
467do_test func2-3.6.3 {
468  set blob [execsql "SELECT SUBSTR(x'1234', -1, -3)"]
469  bin_to_hex [lindex $blob 0]
470} "12"
471
472do_test func2-3.7.0 {
473  set blob [execsql "SELECT SUBSTR(x'1234', -2, 0)"]
474  bin_to_hex [lindex $blob 0]
475} {}
476do_test func2-3.7.1 {
477  set blob [execsql "SELECT SUBSTR(x'1234', -2, -1)"]
478  bin_to_hex [lindex $blob 0]
479} {}
480do_test func2-3.7.2 {
481  set blob [execsql "SELECT SUBSTR(x'1234', -2, -2)"]
482  bin_to_hex [lindex $blob 0]
483} {}
484
485do_test func2-3.8.0 {
486  set blob [execsql "SELECT SUBSTR(x'1234', 1, 0)"]
487  bin_to_hex [lindex $blob 0]
488} {}
489do_test func2-3.8.1 {
490  set blob [execsql "SELECT SUBSTR(x'1234', 1, -1)"]
491  bin_to_hex [lindex $blob 0]
492} {}
493do_test func2-3.8.2 {
494  set blob [execsql "SELECT SUBSTR(x'1234', 1, -2)"]
495  bin_to_hex [lindex $blob 0]
496} {}
497
498do_test func2-3.9.0 {
499  set blob [execsql "SELECT SUBSTR(x'1234', 2, 0)"]
500  bin_to_hex [lindex $blob 0]
501} {}
502do_test func2-3.9.1 {
503  set blob [execsql "SELECT SUBSTR(x'1234', 2, -1)"]
504  bin_to_hex [lindex $blob 0]
505} "12"
506do_test func2-3.9.2 {
507  set blob [execsql "SELECT SUBSTR(x'1234', 2, -2)"]
508  bin_to_hex [lindex $blob 0]
509} "12"
510
511finish_test
512