AryaWu/sqlite
0
1# 2012 October 242#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the built-in INSTR() functions.13#14# EVIDENCE-OF: R-27549-59611 The instr(X,Y) function finds the first15# occurrence of string Y within string X and returns the number of prior16# characters plus 1, or 0 if Y is nowhere found within X.17#18 19 20set testdir [file dirname $argv0]21source $testdir/tester.tcl22 23# Create a table to work with.24#25do_test instr-1.1 {26 db eval {SELECT instr('abcdefg','a');}27} {1}28do_test instr-1.2 {29 db eval {SELECT instr('abcdefg','b');}30} {2}31do_test instr-1.3 {32 db eval {SELECT instr('abcdefg','c');}33} {3}34do_test instr-1.4 {35 db eval {SELECT instr('abcdefg','d');}36} {4}37do_test instr-1.5 {38 db eval {SELECT instr('abcdefg','e');}39} {5}40do_test instr-1.6 {41 db eval {SELECT instr('abcdefg','f');}42} {6}43do_test instr-1.7 {44 db eval {SELECT instr('abcdefg','g');}45} {7}46do_test instr-1.8 {47 db eval {SELECT instr('abcdefg','h');}48} {0}49do_test instr-1.9 {50 db eval {SELECT instr('abcdefg','abcdefg');}51} {1}52do_test instr-1.10 {53 db eval {SELECT instr('abcdefg','abcdefgh');}54} {0}55do_test instr-1.11 {56 db eval {SELECT instr('abcdefg','bcdefg');}57} {2}58do_test instr-1.12 {59 db eval {SELECT instr('abcdefg','bcdefgh');}60} {0}61do_test instr-1.13 {62 db eval {SELECT instr('abcdefg','cdefg');}63} {3}64do_test instr-1.14 {65 db eval {SELECT instr('abcdefg','cdefgh');}66} {0}67do_test instr-1.15 {68 db eval {SELECT instr('abcdefg','defg');}69} {4}70do_test instr-1.16 {71 db eval {SELECT instr('abcdefg','defgh');}72} {0}73do_test instr-1.17 {74 db eval {SELECT instr('abcdefg','efg');}75} {5}76do_test instr-1.18 {77 db eval {SELECT instr('abcdefg','efgh');}78} {0}79do_test instr-1.19 {80 db eval {SELECT instr('abcdefg','fg');}81} {6}82do_test instr-1.20 {83 db eval {SELECT instr('abcdefg','fgh');}84} {0}85do_test instr-1.21 {86 db eval {SELECT coalesce(instr('abcdefg',NULL),'nil');}87} {nil}88do_test instr-1.22 {89 db eval {SELECT coalesce(instr(NULL,'x'),'nil');}90} {nil}91do_test instr-1.23 {92 db eval {SELECT instr(12345,34);}93} {3}94do_test instr-1.24 {95 db eval {SELECT instr(123456.78,34);}96} {3}97do_test instr-1.25 {98 db eval {SELECT instr(123456.78,x'3334');}99} {3}100do_test instr-1.26 {101 db eval {SELECT instr('äbcdefg','efg');}102} {5}103do_test instr-1.27 {104 db eval {SELECT instr('€xyzzy','xyz');}105} {2}106do_test instr-1.28 {107 db eval {SELECT instr('abc€xyzzy','xyz');}108} {5}109do_test instr-1.29 {110 db eval {SELECT instr('abc€xyzzy','€xyz');}111} {4}112do_test instr-1.30 {113 db eval {SELECT instr('abc€xyzzy','c€xyz');}114} {3}115do_test instr-1.31 {116 db eval {SELECT instr(x'0102030405',x'01');}117} {1}118do_test instr-1.32 {119 db eval {SELECT instr(x'0102030405',x'02');}120} {2}121do_test instr-1.33 {122 db eval {SELECT instr(x'0102030405',x'03');}123} {3}124do_test instr-1.34 {125 db eval {SELECT instr(x'0102030405',x'04');}126} {4}127do_test instr-1.35 {128 db eval {SELECT instr(x'0102030405',x'05');}129} {5}130do_test instr-1.36 {131 db eval {SELECT instr(x'0102030405',x'06');}132} {0}133do_test instr-1.37 {134 db eval {SELECT instr(x'0102030405',x'0102030405');}135} {1}136do_test instr-1.38 {137 db eval {SELECT instr(x'0102030405',x'02030405');}138} {2}139do_test instr-1.39 {140 db eval {SELECT instr(x'0102030405',x'030405');}141} {3}142do_test instr-1.40 {143 db eval {SELECT instr(x'0102030405',x'0405');}144} {4}145do_test instr-1.41 {146 db eval {SELECT instr(x'0102030405',x'0506');}147} {0}148do_test instr-1.42 {149 db eval {SELECT instr(x'0102030405',x'');}150} {1}151do_test instr-1.43 {152 db eval {SELECT instr(x'',x'');}153} {1}154do_test instr-1.44 {155 db eval {SELECT instr('','');}156} {1}157do_test instr-1.45 {158 db eval {SELECT instr('abcdefg','');}159} {1}160unset -nocomplain longstr161set longstr abcdefghijklmonpqrstuvwxyz162append longstr $longstr163append longstr $longstr164append longstr $longstr165append longstr $longstr166append longstr $longstr167append longstr $longstr168append longstr $longstr169append longstr $longstr170append longstr $longstr171append longstr $longstr172append longstr $longstr173append longstr $longstr174# puts [string length $longstr]175append longstr Xabcde176do_test instr-1.46 {177 db eval {SELECT instr($longstr,'X');}178} {106497}179do_test instr-1.47 {180 db eval {SELECT instr($longstr,'Y');}181} {0}182do_test instr-1.48 {183 db eval {SELECT instr($longstr,'Xa');}184} {106497}185do_test instr-1.49 {186 db eval {SELECT instr($longstr,'zXa');}187} {106496}188set longstr [string map {a ä} $longstr]189do_test instr-1.50 {190 db eval {SELECT instr($longstr,'X');}191} {106497}192do_test instr-1.51 {193 db eval {SELECT instr($longstr,'Y');}194} {0}195do_test instr-1.52 {196 db eval {SELECT instr($longstr,'Xä');}197} {106497}198do_test instr-1.53 {199 db eval {SELECT instr($longstr,'zXä');}200} {106496}201do_test instr-1.54 {202 db eval {SELECT instr(x'78c3a4e282ac79','x');}203} {1}204do_test instr-1.55 {205 db eval {SELECT instr(x'78c3a4e282ac79','y');}206} {4}207 208# EVIDENCE-OF: R-46421-32541 Or, if X and Y are both BLOBs, then209# instr(X,Y) returns one more than the number bytes prior to the first210# occurrence of Y, or 0 if Y does not occur anywhere within X.211#212do_test instr-1.56.1 {213 db eval {SELECT instr(x'78c3a4e282ac79',x'79');}214} {7}215do_test instr-1.56.2 {216 db eval {SELECT instr(x'78c3a4e282ac79',x'7a');}217} {0}218do_test instr-1.56.3 {219 db eval {SELECT instr(x'78c3a4e282ac79',x'78');}220} {1}221do_test instr-1.56.3 {222 db eval {SELECT instr(x'78c3a4e282ac79',x'a4');}223} {3}224 225# EVIDENCE-OF: R-17329-35644 If both arguments X and Y to instr(X,Y) are226# non-NULL and are not BLOBs then both are interpreted as strings.227#228do_test instr-1.57.1 {229 db eval {SELECT instr('xä€y',x'79');}230} {4}231do_test instr-1.57.2 {232 db eval {SELECT instr('xä€y',x'a4');}233} {0}234do_test instr-1.57.3 {235 db eval {SELECT instr(x'78c3a4e282ac79','y');}236} {4}237 238# EVIDENCE-OF: R-14708-27487 If either X or Y are NULL in instr(X,Y)239# then the result is NULL.240#241do_execsql_test instr-1.60 {242 SELECT coalesce(instr(NULL,'abc'), 999);243} {999}244do_execsql_test instr-1.61 {245 SELECT coalesce(instr('abc',NULL), 999);246} {999}247do_execsql_test instr-1.62 {248 SELECT coalesce(instr(NULL,NULL), 999);249} {999}250 251do_execsql_test instr-1.63 {252 SELECT instr(X'', 'abc')253} 0254do_execsql_test instr-1.64 {255 CREATE TABLE x1(a, b);256 INSERT INTO x1 VALUES(X'', 'abc');257 SELECT instr(a, b) FROM x1;258} 0259 260# 2019-09-16 ticket https://sqlite.org/src/info/587791f92620090e261#262do_execsql_test instr-2.0 {263 DROP TABLE IF EXISTS t0;264 CREATE TABLE t0(c0 PRIMARY KEY, c1);265 INSERT INTO t0(c0) VALUES (x'bb'), (0);266 SELECT COUNT(*) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1;267} {1}268do_execsql_test instr-2.1 {269 SELECT quote(c0) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1;270} {X'BB'}271do_execsql_test instr-2.2 {272 DROP TABLE IF EXISTS t1;273 CREATE TABLE t1(x);274 INSERT INTO t1(x) VALUES('text'),(x'bb');275 SELECT quote(x) FROM t1 WHERE instr(x'aabb',x);276} {X'BB'}277do_execsql_test instr-2.3 {278 SELECT quote(x) FROM t1 WHERE x>'zzz' AND instr(x'aabb',x);279} {X'BB'}280 281finish_test282