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  finish_test