# 2012 October 24 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the built-in INSTR() functions. # # EVIDENCE-OF: R-27549-59611 The instr(X,Y) function finds the first # occurrence of string Y within string X and returns the number of prior # characters plus 1, or 0 if Y is nowhere found within X. # set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test instr-1.1 { db eval {SELECT instr('abcdefg','a');} } {1} do_test instr-1.2 { db eval {SELECT instr('abcdefg','b');} } {2} do_test instr-1.3 { db eval {SELECT instr('abcdefg','c');} } {3} do_test instr-1.4 { db eval {SELECT instr('abcdefg','d');} } {4} do_test instr-1.5 { db eval {SELECT instr('abcdefg','e');} } {5} do_test instr-1.6 { db eval {SELECT instr('abcdefg','f');} } {6} do_test instr-1.7 { db eval {SELECT instr('abcdefg','g');} } {7} do_test instr-1.8 { db eval {SELECT instr('abcdefg','h');} } {0} do_test instr-1.9 { db eval {SELECT instr('abcdefg','abcdefg');} } {1} do_test instr-1.10 { db eval {SELECT instr('abcdefg','abcdefgh');} } {0} do_test instr-1.11 { db eval {SELECT instr('abcdefg','bcdefg');} } {2} do_test instr-1.12 { db eval {SELECT instr('abcdefg','bcdefgh');} } {0} do_test instr-1.13 { db eval {SELECT instr('abcdefg','cdefg');} } {3} do_test instr-1.14 { db eval {SELECT instr('abcdefg','cdefgh');} } {0} do_test instr-1.15 { db eval {SELECT instr('abcdefg','defg');} } {4} do_test instr-1.16 { db eval {SELECT instr('abcdefg','defgh');} } {0} do_test instr-1.17 { db eval {SELECT instr('abcdefg','efg');} } {5} do_test instr-1.18 { db eval {SELECT instr('abcdefg','efgh');} } {0} do_test instr-1.19 { db eval {SELECT instr('abcdefg','fg');} } {6} do_test instr-1.20 { db eval {SELECT instr('abcdefg','fgh');} } {0} do_test instr-1.21 { db eval {SELECT coalesce(instr('abcdefg',NULL),'nil');} } {nil} do_test instr-1.22 { db eval {SELECT coalesce(instr(NULL,'x'),'nil');} } {nil} do_test instr-1.23 { db eval {SELECT instr(12345,34);} } {3} do_test instr-1.24 { db eval {SELECT instr(123456.78,34);} } {3} do_test instr-1.25 { db eval {SELECT instr(123456.78,x'3334');} } {3} do_test instr-1.26 { db eval {SELECT instr('äbcdefg','efg');} } {5} do_test instr-1.27 { db eval {SELECT instr('€xyzzy','xyz');} } {2} do_test instr-1.28 { db eval {SELECT instr('abc€xyzzy','xyz');} } {5} do_test instr-1.29 { db eval {SELECT instr('abc€xyzzy','€xyz');} } {4} do_test instr-1.30 { db eval {SELECT instr('abc€xyzzy','c€xyz');} } {3} do_test instr-1.31 { db eval {SELECT instr(x'0102030405',x'01');} } {1} do_test instr-1.32 { db eval {SELECT instr(x'0102030405',x'02');} } {2} do_test instr-1.33 { db eval {SELECT instr(x'0102030405',x'03');} } {3} do_test instr-1.34 { db eval {SELECT instr(x'0102030405',x'04');} } {4} do_test instr-1.35 { db eval {SELECT instr(x'0102030405',x'05');} } {5} do_test instr-1.36 { db eval {SELECT instr(x'0102030405',x'06');} } {0} do_test instr-1.37 { db eval {SELECT instr(x'0102030405',x'0102030405');} } {1} do_test instr-1.38 { db eval {SELECT instr(x'0102030405',x'02030405');} } {2} do_test instr-1.39 { db eval {SELECT instr(x'0102030405',x'030405');} } {3} do_test instr-1.40 { db eval {SELECT instr(x'0102030405',x'0405');} } {4} do_test instr-1.41 { db eval {SELECT instr(x'0102030405',x'0506');} } {0} do_test instr-1.42 { db eval {SELECT instr(x'0102030405',x'');} } {1} do_test instr-1.43 { db eval {SELECT instr(x'',x'');} } {1} do_test instr-1.44 { db eval {SELECT instr('','');} } {1} do_test instr-1.45 { db eval {SELECT instr('abcdefg','');} } {1} unset -nocomplain longstr set longstr abcdefghijklmonpqrstuvwxyz append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr append longstr $longstr # puts [string length $longstr] append longstr Xabcde do_test instr-1.46 { db eval {SELECT instr($longstr,'X');} } {106497} do_test instr-1.47 { db eval {SELECT instr($longstr,'Y');} } {0} do_test instr-1.48 { db eval {SELECT instr($longstr,'Xa');} } {106497} do_test instr-1.49 { db eval {SELECT instr($longstr,'zXa');} } {106496} set longstr [string map {a ä} $longstr] do_test instr-1.50 { db eval {SELECT instr($longstr,'X');} } {106497} do_test instr-1.51 { db eval {SELECT instr($longstr,'Y');} } {0} do_test instr-1.52 { db eval {SELECT instr($longstr,'Xä');} } {106497} do_test instr-1.53 { db eval {SELECT instr($longstr,'zXä');} } {106496} do_test instr-1.54 { db eval {SELECT instr(x'78c3a4e282ac79','x');} } {1} do_test instr-1.55 { db eval {SELECT instr(x'78c3a4e282ac79','y');} } {4} # EVIDENCE-OF: R-46421-32541 Or, if X and Y are both BLOBs, then # instr(X,Y) returns one more than the number bytes prior to the first # occurrence of Y, or 0 if Y does not occur anywhere within X. # do_test instr-1.56.1 { db eval {SELECT instr(x'78c3a4e282ac79',x'79');} } {7} do_test instr-1.56.2 { db eval {SELECT instr(x'78c3a4e282ac79',x'7a');} } {0} do_test instr-1.56.3 { db eval {SELECT instr(x'78c3a4e282ac79',x'78');} } {1} do_test instr-1.56.3 { db eval {SELECT instr(x'78c3a4e282ac79',x'a4');} } {3} # EVIDENCE-OF: R-17329-35644 If both arguments X and Y to instr(X,Y) are # non-NULL and are not BLOBs then both are interpreted as strings. # do_test instr-1.57.1 { db eval {SELECT instr('xä€y',x'79');} } {4} do_test instr-1.57.2 { db eval {SELECT instr('xä€y',x'a4');} } {0} do_test instr-1.57.3 { db eval {SELECT instr(x'78c3a4e282ac79','y');} } {4} # EVIDENCE-OF: R-14708-27487 If either X or Y are NULL in instr(X,Y) # then the result is NULL. # do_execsql_test instr-1.60 { SELECT coalesce(instr(NULL,'abc'), 999); } {999} do_execsql_test instr-1.61 { SELECT coalesce(instr('abc',NULL), 999); } {999} do_execsql_test instr-1.62 { SELECT coalesce(instr(NULL,NULL), 999); } {999} do_execsql_test instr-1.63 { SELECT instr(X'', 'abc') } 0 do_execsql_test instr-1.64 { CREATE TABLE x1(a, b); INSERT INTO x1 VALUES(X'', 'abc'); SELECT instr(a, b) FROM x1; } 0 # 2019-09-16 ticket https://www.sqlite.org/src/info/587791f92620090e # do_execsql_test instr-2.0 { DROP TABLE IF EXISTS t0; CREATE TABLE t0(c0 PRIMARY KEY, c1); INSERT INTO t0(c0) VALUES (x'bb'), (0); SELECT COUNT(*) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1; } {1} do_execsql_test instr-2.1 { SELECT quote(c0) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1; } {X'BB'} do_execsql_test instr-2.2 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x); INSERT INTO t1(x) VALUES('text'),(x'bb'); SELECT quote(x) FROM t1 WHERE instr(x'aabb',x); } {X'BB'} do_execsql_test instr-2.3 { SELECT quote(x) FROM t1 WHERE x>'zzz' AND instr(x'aabb',x); } {X'BB'} finish_test