gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/regexp1.test (about) 1 # 2012 December 31 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 # 12 # This file implements test for the REGEXP operator in test_regexp.c. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 do_test regexp1-1.1 { 19 load_static_extension db regexp 20 db eval { 21 CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT); 22 INSERT INTO t1 VALUES(1, 'For since by man came death,'); 23 INSERT INTO t1 VALUES(2, 'by man came also the resurrection of the dead.'); 24 INSERT INTO t1 VALUES(3, 'For as in Adam all die,'); 25 INSERT INTO t1 VALUES(4, 'even so in Christ shall all be made alive.'); 26 27 SELECT x FROM t1 WHERE y REGEXP '^For ' ORDER BY x; 28 } 29 } {1 3} 30 31 do_execsql_test regexp1-1.1.2 { 32 SELECT regexpi('abc','ABC'); 33 } {1} 34 do_execsql_test regexp1-1.1.3 { 35 SELECT regexpi('ABC','ABC'); 36 } {1} 37 do_execsql_test regexp1-1.1.4 { 38 SELECT regexpi('ABC','abc'); 39 } {1} 40 do_execsql_test regexp1-1.1.5 { 41 SELECT regexpi('ABC.','ABC'); 42 } {0} 43 44 do_execsql_test regexp1-1.2 { 45 SELECT x FROM t1 WHERE y REGEXP 'by|in' ORDER BY x; 46 } {1 2 3 4} 47 do_execsql_test regexp1-1.3.1 { 48 SELECT x FROM t1 WHERE y REGEXP 'by|Christ' ORDER BY x; 49 } {1 2 4} 50 do_execsql_test regexp1-1.3.2 { 51 SELECT x FROM t1 WHERE regexp('by|christ',y) ORDER BY x; 52 } {1 2} 53 do_execsql_test regexp1-1.3.3 { 54 SELECT x FROM t1 WHERE regexpi('by|christ',y) ORDER BY x; 55 } {1 2 4} 56 do_execsql_test regexp1-1.3.4 { 57 SELECT x FROM t1 WHERE regexpi('BY|CHRIST',y) ORDER BY x; 58 } {1 2 4} 59 do_execsql_test regexp1-1.4 { 60 SELECT x FROM t1 WHERE y REGEXP 'shal+ al+' ORDER BY x; 61 } {4} 62 do_execsql_test regexp1-1.5.1 { 63 SELECT x FROM t1 WHERE y REGEXP 'shall x*y*z*all' ORDER BY x; 64 } {4} 65 do_execsql_test regexp1-1.5.2 { 66 SELECT x FROM t1 WHERE regexp('shall x*y*z*all',y) ORDER BY x; 67 } {4} 68 do_execsql_test regexp1-1.5.3 { 69 SELECT x FROM t1 WHERE regexp('SHALL x*y*z*all',y) ORDER BY x; 70 } {} 71 do_execsql_test regexp1-1.5.4 { 72 SELECT x FROM t1 WHERE regexpi('SHALL x*y*z*all',y) ORDER BY x; 73 } {4} 74 do_execsql_test regexp1-1.6 { 75 SELECT x FROM t1 WHERE y REGEXP 'shallx?y? ?z?all' ORDER BY x; 76 } {4} 77 do_execsql_test regexp1-1.7 { 78 SELECT x FROM t1 WHERE y REGEXP 'r{2}' ORDER BY x; 79 } {2} 80 do_execsql_test regexp1-1.8 { 81 SELECT x FROM t1 WHERE y REGEXP 'r{3}' ORDER BY x; 82 } {} 83 do_execsql_test regexp1-1.9 { 84 SELECT x FROM t1 WHERE y REGEXP 'r{1}' ORDER BY x; 85 } {1 2 3 4} 86 do_execsql_test regexp1-1.10 { 87 SELECT x FROM t1 WHERE y REGEXP 'ur{2,10}e' ORDER BY x; 88 } {2} 89 do_execsql_test regexp1-1.11 { 90 SELECT x FROM t1 WHERE y REGEXP '[Aa]dam' ORDER BY x; 91 } {3} 92 do_execsql_test regexp1-1.12 { 93 SELECT x FROM t1 WHERE y REGEXP '[^Aa]dam' ORDER BY x; 94 } {} 95 do_execsql_test regexp1-1.13 { 96 SELECT x FROM t1 WHERE y REGEXP '[^b-zB-Z]dam' ORDER BY x; 97 } {3} 98 do_execsql_test regexp1-1.14 { 99 SELECT x FROM t1 WHERE y REGEXP 'alive' ORDER BY x; 100 } {4} 101 do_execsql_test regexp1-1.15 { 102 SELECT x FROM t1 WHERE y REGEXP '^alive' ORDER BY x; 103 } {} 104 do_execsql_test regexp1-1.16 { 105 SELECT x FROM t1 WHERE y REGEXP 'alive$' ORDER BY x; 106 } {} 107 do_execsql_test regexp1-1.17 { 108 SELECT x FROM t1 WHERE y REGEXP 'alive.$' ORDER BY x; 109 } {4} 110 do_execsql_test regexp1-1.18 { 111 SELECT x FROM t1 WHERE y REGEXP 'alive\.$' ORDER BY x; 112 } {4} 113 do_execsql_test regexp1-1.19 { 114 SELECT x FROM t1 WHERE y REGEXP 'ma[nd]' ORDER BY x; 115 } {1 2 4} 116 do_execsql_test regexp1-1.20 { 117 SELECT x FROM t1 WHERE y REGEXP '\bma[nd]' ORDER BY x; 118 } {1 2 4} 119 do_execsql_test regexp1-1.21 { 120 SELECT x FROM t1 WHERE y REGEXP 'ma[nd]\b' ORDER BY x; 121 } {1 2} 122 do_execsql_test regexp1-1.22 { 123 SELECT x FROM t1 WHERE y REGEXP 'ma\w' ORDER BY x; 124 } {1 2 4} 125 do_execsql_test regexp1-1.23 { 126 SELECT x FROM t1 WHERE y REGEXP 'ma\W' ORDER BY x; 127 } {} 128 do_execsql_test regexp1-1.24 { 129 SELECT x FROM t1 WHERE y REGEXP '\sma\w' ORDER BY x; 130 } {1 2 4} 131 do_execsql_test regexp1-1.25 { 132 SELECT x FROM t1 WHERE y REGEXP '\Sma\w' ORDER BY x; 133 } {} 134 do_execsql_test regexp1-1.26 { 135 SELECT x FROM t1 WHERE y REGEXP 'alive\S$' ORDER BY x; 136 } {4} 137 do_execsql_test regexp1-1.27 { 138 SELECT x FROM t1 WHERE y REGEXP 139 '\b(unto|us|son|given|his|name|called|' || 140 'wonderful|councelor|mighty|god|everlasting|father|' || 141 'prince|peace|alive)\b'; 142 } {4} 143 144 do_execsql_test regexp1-2.1 { 145 SELECT 'aaaabbbbcccc' REGEXP 'ab*c', 146 'aaaacccc' REGEXP 'ab*c'; 147 } {1 1} 148 do_execsql_test regexp1-2.2 { 149 SELECT 'aaaabbbbcccc' REGEXP 'ab+c', 150 'aaaacccc' REGEXP 'ab+c'; 151 } {1 0} 152 do_execsql_test regexp1-2.3 { 153 SELECT 'aaaabbbbcccc' REGEXP 'ab?c', 154 'aaaacccc' REGEXP 'ab?c'; 155 } {0 1} 156 do_execsql_test regexp1-2.4 { 157 SELECT 'aaaabbbbbbcccc' REGEXP 'ab{3,5}c', 158 'aaaabbbbbcccc' REGEXP 'ab{3,5}c', 159 'aaaabbbbcccc' REGEXP 'ab{3,5}c', 160 'aaaabbbcccc' REGEXP 'ab{3,5}c', 161 'aaaabbcccc' REGEXP 'ab{3,5}c', 162 'aaaabcccc' REGEXP 'ab{3,5}c' 163 } {0 1 1 1 0 0} 164 do_execsql_test regexp1-2.5 { 165 SELECT 'aaaabbbbcccc' REGEXP 'a(a|b|c)+c', 166 'aaaabbbbcccc' REGEXP '^a(a|b|c){11}c$', 167 'aaaabbbbcccc' REGEXP '^a(a|b|c){10}c$', 168 'aaaabbbbcccc' REGEXP '^a(a|b|c){9}c$' 169 } {1 0 1 0} 170 do_execsql_test regexp1-2.6 { 171 SELECT 'aaaabbbbcccc' REGEXP '^a(a|bb|c)+c$', 172 'aaaabbbbcccc' REGEXP '^a(a|bbb|c)+c$', 173 'aaaabbbbcccc' REGEXP '^a(a|bbbb|c)+c$' 174 } {1 0 1} 175 do_execsql_test regexp1-2.7 { 176 SELECT 'aaaabbbbcccc' REGEXP '^a([ac]+|bb){3}c$', 177 'aaaabbbbcccc' REGEXP '^a([ac]+|bb){4}c$', 178 'aaaabbbbcccc' REGEXP '^a([ac]+|bb){5}c$' 179 } {0 1 1} 180 181 do_execsql_test regexp1-2.8 { 182 SELECT 'abc*def+ghi.jkl[mno]pqr' REGEXP 'c.d', 183 'abc*def+ghi.jkl[mno]pqr' REGEXP 'c\*d', 184 'abc*def+ghi.jkl[mno]pqr' REGEXP 'f\+g', 185 'abc*def+ghi.jkl[mno]pqr' REGEXP 'i\.j', 186 'abc*def+ghi.jkl[mno]pqr' REGEXP 'l\[mno\]p' 187 } {1 1 1 1 1} 188 189 do_test regexp1-2.9 { 190 set v1 "abc\ndef" 191 db eval {SELECT $v1 REGEXP '^abc\ndef$'} 192 } {1} 193 do_test regexp1-2.10 { 194 set v1 "abc\adef" 195 db eval {SELECT $v1 REGEXP '^abc\adef$'} 196 } {1} 197 do_test regexp1-2.11 { 198 set v1 "abc\tdef" 199 db eval {SELECT $v1 REGEXP '^abc\tdef$'} 200 } {1} 201 do_test regexp1-2.12 { 202 set v1 "abc\rdef" 203 db eval {SELECT $v1 REGEXP '^abc\rdef$'} 204 } {1} 205 do_test regexp1-2.13 { 206 set v1 "abc\fdef" 207 db eval {SELECT $v1 REGEXP '^abc\fdef$'} 208 } {1} 209 do_test regexp1-2.14 { 210 set v1 "abc\vdef" 211 db eval {SELECT $v1 REGEXP '^abc\vdef$'} 212 } {1} 213 do_execsql_test regexp1-2.15 { 214 SELECT 'abc\def' REGEXP '^abc\\def', 215 'abc(def' REGEXP '^abc\(def', 216 'abc)def' REGEXP '^abc\)def', 217 'abc*def' REGEXP '^abc\*def', 218 'abc.def' REGEXP '^abc\.def', 219 'abc+def' REGEXP '^abc\+def', 220 'abc?def' REGEXP '^abc\?def', 221 'abc[def' REGEXP '^abc\[def', 222 'abc$def' REGEXP '^abc\$', 223 '^def' REGEXP '\^def', 224 'abc{4}x' REGEXP '^abc\{4\}x$', 225 'abc|def' REGEXP '^abc\|def$' 226 } {1 1 1 1 1 1 1 1 1 1 1 1} 227 228 do_execsql_test regexp1-2.20 { 229 SELECT 'abc$¢€xyz' REGEXP '^abc\u0024\u00a2\u20acxyz$', 230 'abc$¢€xyz' REGEXP '^abc\u0024\u00A2\u20ACxyz$', 231 'abc$¢€xyz' REGEXP '^abc\x24\xa2\u20acxyz$' 232 } {1 1 1} 233 do_execsql_test regexp1-2.21 { 234 SELECT 'abc$¢€xyz' REGEXP '^abc[\u0024][\u00a2][\u20ac]xyz$', 235 'abc$¢€xyz' REGEXP '^abc[\u0024\u00A2\u20AC]{3}xyz$', 236 'abc$¢€xyz' REGEXP '^abc[\x24][\xa2\u20ac]+xyz$' 237 } {1 1 1} 238 do_execsql_test regexp1-2.22 { 239 SELECT 'abc$¢€xyz' REGEXP '^abc[^\u0025-X][^ -\u007f][^\u20ab]xyz$' 240 } {1} 241 242 # 2022-07-03 243 # https://sqlite.org/forum/forumpost/96692f8ba5 244 # The REGEXP extension mishandles the prefix search optimization when 245 # the prefix contains 3-byte UTF8 characters. 246 # 247 reset_db 248 load_static_extension db regexp 249 do_execsql_test regexp1-3.1 { 250 CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT); 251 INSERT INTO t1(id, a) VALUES(1, '日本語'); 252 SELECT a, hex(a), length(a) FROM t1; 253 } {日本語 E697A5E69CACE8AA9E 3} 254 do_execsql_test regexp1-3.2 { 255 SELECT * FROM t1 WHERE a='日本語'; 256 } {1 日本語} 257 do_execsql_test regexp1-3.3 { 258 SELECT * FROM t1 WHERE a LIKE '日本語'; 259 } {1 日本語} 260 do_execsql_test regexp1-3.4 { 261 SELECT * FROM t1 wHERE a REGEXP '日本語'; 262 } {1 日本語} 263 264 finish_test