000001 # 2012 October 24
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing the built-in INSTR() functions.
000013 #
000014 # EVIDENCE-OF: R-27549-59611 The instr(X,Y) function finds the first
000015 # occurrence of string Y within string X and returns the number of prior
000016 # characters plus 1, or 0 if Y is nowhere found within X.
000017 #
000018
000019
000020 set testdir [file dirname $argv0]
000021 source $testdir/tester.tcl
000022
000023 # Create a table to work with.
000024 #
000025 do_test instr-1.1 {
000026 db eval {SELECT instr('abcdefg','a');}
000027 } {1}
000028 do_test instr-1.2 {
000029 db eval {SELECT instr('abcdefg','b');}
000030 } {2}
000031 do_test instr-1.3 {
000032 db eval {SELECT instr('abcdefg','c');}
000033 } {3}
000034 do_test instr-1.4 {
000035 db eval {SELECT instr('abcdefg','d');}
000036 } {4}
000037 do_test instr-1.5 {
000038 db eval {SELECT instr('abcdefg','e');}
000039 } {5}
000040 do_test instr-1.6 {
000041 db eval {SELECT instr('abcdefg','f');}
000042 } {6}
000043 do_test instr-1.7 {
000044 db eval {SELECT instr('abcdefg','g');}
000045 } {7}
000046 do_test instr-1.8 {
000047 db eval {SELECT instr('abcdefg','h');}
000048 } {0}
000049 do_test instr-1.9 {
000050 db eval {SELECT instr('abcdefg','abcdefg');}
000051 } {1}
000052 do_test instr-1.10 {
000053 db eval {SELECT instr('abcdefg','abcdefgh');}
000054 } {0}
000055 do_test instr-1.11 {
000056 db eval {SELECT instr('abcdefg','bcdefg');}
000057 } {2}
000058 do_test instr-1.12 {
000059 db eval {SELECT instr('abcdefg','bcdefgh');}
000060 } {0}
000061 do_test instr-1.13 {
000062 db eval {SELECT instr('abcdefg','cdefg');}
000063 } {3}
000064 do_test instr-1.14 {
000065 db eval {SELECT instr('abcdefg','cdefgh');}
000066 } {0}
000067 do_test instr-1.15 {
000068 db eval {SELECT instr('abcdefg','defg');}
000069 } {4}
000070 do_test instr-1.16 {
000071 db eval {SELECT instr('abcdefg','defgh');}
000072 } {0}
000073 do_test instr-1.17 {
000074 db eval {SELECT instr('abcdefg','efg');}
000075 } {5}
000076 do_test instr-1.18 {
000077 db eval {SELECT instr('abcdefg','efgh');}
000078 } {0}
000079 do_test instr-1.19 {
000080 db eval {SELECT instr('abcdefg','fg');}
000081 } {6}
000082 do_test instr-1.20 {
000083 db eval {SELECT instr('abcdefg','fgh');}
000084 } {0}
000085 do_test instr-1.21 {
000086 db eval {SELECT coalesce(instr('abcdefg',NULL),'nil');}
000087 } {nil}
000088 do_test instr-1.22 {
000089 db eval {SELECT coalesce(instr(NULL,'x'),'nil');}
000090 } {nil}
000091 do_test instr-1.23 {
000092 db eval {SELECT instr(12345,34);}
000093 } {3}
000094 do_test instr-1.24 {
000095 db eval {SELECT instr(123456.78,34);}
000096 } {3}
000097 do_test instr-1.25 {
000098 db eval {SELECT instr(123456.78,x'3334');}
000099 } {3}
000100 do_test instr-1.26 {
000101 db eval {SELECT instr('äbcdefg','efg');}
000102 } {5}
000103 do_test instr-1.27 {
000104 db eval {SELECT instr('€xyzzy','xyz');}
000105 } {2}
000106 do_test instr-1.28 {
000107 db eval {SELECT instr('abc€xyzzy','xyz');}
000108 } {5}
000109 do_test instr-1.29 {
000110 db eval {SELECT instr('abc€xyzzy','€xyz');}
000111 } {4}
000112 do_test instr-1.30 {
000113 db eval {SELECT instr('abc€xyzzy','c€xyz');}
000114 } {3}
000115 do_test instr-1.31 {
000116 db eval {SELECT instr(x'0102030405',x'01');}
000117 } {1}
000118 do_test instr-1.32 {
000119 db eval {SELECT instr(x'0102030405',x'02');}
000120 } {2}
000121 do_test instr-1.33 {
000122 db eval {SELECT instr(x'0102030405',x'03');}
000123 } {3}
000124 do_test instr-1.34 {
000125 db eval {SELECT instr(x'0102030405',x'04');}
000126 } {4}
000127 do_test instr-1.35 {
000128 db eval {SELECT instr(x'0102030405',x'05');}
000129 } {5}
000130 do_test instr-1.36 {
000131 db eval {SELECT instr(x'0102030405',x'06');}
000132 } {0}
000133 do_test instr-1.37 {
000134 db eval {SELECT instr(x'0102030405',x'0102030405');}
000135 } {1}
000136 do_test instr-1.38 {
000137 db eval {SELECT instr(x'0102030405',x'02030405');}
000138 } {2}
000139 do_test instr-1.39 {
000140 db eval {SELECT instr(x'0102030405',x'030405');}
000141 } {3}
000142 do_test instr-1.40 {
000143 db eval {SELECT instr(x'0102030405',x'0405');}
000144 } {4}
000145 do_test instr-1.41 {
000146 db eval {SELECT instr(x'0102030405',x'0506');}
000147 } {0}
000148 do_test instr-1.42 {
000149 db eval {SELECT instr(x'0102030405',x'');}
000150 } {1}
000151 do_test instr-1.43 {
000152 db eval {SELECT instr(x'',x'');}
000153 } {1}
000154 do_test instr-1.44 {
000155 db eval {SELECT instr('','');}
000156 } {1}
000157 do_test instr-1.45 {
000158 db eval {SELECT instr('abcdefg','');}
000159 } {1}
000160 unset -nocomplain longstr
000161 set longstr abcdefghijklmonpqrstuvwxyz
000162 append longstr $longstr
000163 append longstr $longstr
000164 append longstr $longstr
000165 append longstr $longstr
000166 append longstr $longstr
000167 append longstr $longstr
000168 append longstr $longstr
000169 append longstr $longstr
000170 append longstr $longstr
000171 append longstr $longstr
000172 append longstr $longstr
000173 append longstr $longstr
000174 # puts [string length $longstr]
000175 append longstr Xabcde
000176 do_test instr-1.46 {
000177 db eval {SELECT instr($longstr,'X');}
000178 } {106497}
000179 do_test instr-1.47 {
000180 db eval {SELECT instr($longstr,'Y');}
000181 } {0}
000182 do_test instr-1.48 {
000183 db eval {SELECT instr($longstr,'Xa');}
000184 } {106497}
000185 do_test instr-1.49 {
000186 db eval {SELECT instr($longstr,'zXa');}
000187 } {106496}
000188 set longstr [string map {a ä} $longstr]
000189 do_test instr-1.50 {
000190 db eval {SELECT instr($longstr,'X');}
000191 } {106497}
000192 do_test instr-1.51 {
000193 db eval {SELECT instr($longstr,'Y');}
000194 } {0}
000195 do_test instr-1.52 {
000196 db eval {SELECT instr($longstr,'Xä');}
000197 } {106497}
000198 do_test instr-1.53 {
000199 db eval {SELECT instr($longstr,'zXä');}
000200 } {106496}
000201 do_test instr-1.54 {
000202 db eval {SELECT instr(x'78c3a4e282ac79','x');}
000203 } {1}
000204 do_test instr-1.55 {
000205 db eval {SELECT instr(x'78c3a4e282ac79','y');}
000206 } {4}
000207
000208 # EVIDENCE-OF: R-46421-32541 Or, if X and Y are both BLOBs, then
000209 # instr(X,Y) returns one more than the number bytes prior to the first
000210 # occurrence of Y, or 0 if Y does not occur anywhere within X.
000211 #
000212 do_test instr-1.56.1 {
000213 db eval {SELECT instr(x'78c3a4e282ac79',x'79');}
000214 } {7}
000215 do_test instr-1.56.2 {
000216 db eval {SELECT instr(x'78c3a4e282ac79',x'7a');}
000217 } {0}
000218 do_test instr-1.56.3 {
000219 db eval {SELECT instr(x'78c3a4e282ac79',x'78');}
000220 } {1}
000221 do_test instr-1.56.3 {
000222 db eval {SELECT instr(x'78c3a4e282ac79',x'a4');}
000223 } {3}
000224
000225 # EVIDENCE-OF: R-17329-35644 If both arguments X and Y to instr(X,Y) are
000226 # non-NULL and are not BLOBs then both are interpreted as strings.
000227 #
000228 do_test instr-1.57.1 {
000229 db eval {SELECT instr('xä€y',x'79');}
000230 } {4}
000231 do_test instr-1.57.2 {
000232 db eval {SELECT instr('xä€y',x'a4');}
000233 } {0}
000234 do_test instr-1.57.3 {
000235 db eval {SELECT instr(x'78c3a4e282ac79','y');}
000236 } {4}
000237
000238 # EVIDENCE-OF: R-14708-27487 If either X or Y are NULL in instr(X,Y)
000239 # then the result is NULL.
000240 #
000241 do_execsql_test instr-1.60 {
000242 SELECT coalesce(instr(NULL,'abc'), 999);
000243 } {999}
000244 do_execsql_test instr-1.61 {
000245 SELECT coalesce(instr('abc',NULL), 999);
000246 } {999}
000247 do_execsql_test instr-1.62 {
000248 SELECT coalesce(instr(NULL,NULL), 999);
000249 } {999}
000250
000251 do_execsql_test instr-1.63 {
000252 SELECT instr(X'', 'abc')
000253 } 0
000254 do_execsql_test instr-1.64 {
000255 CREATE TABLE x1(a, b);
000256 INSERT INTO x1 VALUES(X'', 'abc');
000257 SELECT instr(a, b) FROM x1;
000258 } 0
000259
000260 # 2019-09-16 ticket https://www.sqlite.org/src/info/587791f92620090e
000261 #
000262 do_execsql_test instr-2.0 {
000263 DROP TABLE IF EXISTS t0;
000264 CREATE TABLE t0(c0 PRIMARY KEY, c1);
000265 INSERT INTO t0(c0) VALUES (x'bb'), (0);
000266 SELECT COUNT(*) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1;
000267 } {1}
000268 do_execsql_test instr-2.1 {
000269 SELECT quote(c0) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1;
000270 } {X'BB'}
000271 do_execsql_test instr-2.2 {
000272 DROP TABLE IF EXISTS t1;
000273 CREATE TABLE t1(x);
000274 INSERT INTO t1(x) VALUES('text'),(x'bb');
000275 SELECT quote(x) FROM t1 WHERE instr(x'aabb',x);
000276 } {X'BB'}
000277 do_execsql_test instr-2.3 {
000278 SELECT quote(x) FROM t1 WHERE x>'zzz' AND instr(x'aabb',x);
000279 } {X'BB'}
000280
000281 finish_test