gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/like3.test (about) 1 # 2015-03-06 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 regression tests for SQLite library. The 13 # focus of this file is testing the LIKE and GLOB operators and 14 # in particular the optimizations that occur to help those operators 15 # run faster and that those optimizations work correctly when there 16 # are both strings and blobs being tested. 17 # 18 # Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following 19 # SQL was not working correctly: 20 # 21 # CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase); 22 # INSERT INTO t1(x) VALUES(x'616263'); 23 # SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%'; 24 # SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%'; 25 # 26 # This script verifies that it works right now. 27 # 28 29 set testdir [file dirname $argv0] 30 source $testdir/tester.tcl 31 32 ifcapable !like_match_blobs { 33 finish_test 34 return 35 } 36 37 do_execsql_test like3-1.1 { 38 PRAGMA encoding=UTF8; 39 CREATE TABLE t1(a,b TEXT COLLATE nocase); 40 INSERT INTO t1(a,b) 41 VALUES(1,'abc'), 42 (2,'ABX'), 43 (3,'BCD'), 44 (4,x'616263'), 45 (5,x'414258'), 46 (6,x'424344'); 47 CREATE INDEX t1ba ON t1(b,a); 48 49 SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a; 50 } {1 abc 2 ABX 4 abc 5 ABX} 51 do_execsql_test like3-1.2 { 52 SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a; 53 } {1 abc 2 ABX 4 abc 5 ABX} 54 55 do_execsql_test like3-2.0 { 56 CREATE TABLE t2(a, b TEXT); 57 INSERT INTO t2 SELECT a, b FROM t1; 58 CREATE INDEX t2ba ON t2(b,a); 59 SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a; 60 } {1 abc 4 abc} 61 do_execsql_test like3-2.1 { 62 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a; 63 } {1 abc 4 abc} 64 do_execsql_test like3-2.2 { 65 SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*' 66 } {4 abc} 67 do_execsql_test like3-2.3 { 68 SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*' 69 } {4 abc} 70 do_execsql_test like3-2.4 { 71 SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162' 72 } {4 abc} 73 do_execsql_test like3-2.5 { 74 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162' 75 } {4 abc} 76 77 do_execsql_test like3-3.0 { 78 CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase); 79 INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz'); 80 INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3; 81 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x; 82 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 83 do_execsql_test like3-3.1 { 84 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC; 85 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 86 do_execsql_test like3-3.1ck { 87 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC; 88 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 89 do_execsql_test like3-3.2 { 90 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC; 91 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 92 do_execsql_test like3-3.2ck { 93 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC; 94 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 95 96 do_execsql_test like3-4.0 { 97 CREATE TABLE t4(x TEXT COLLATE nocase); 98 CREATE INDEX t4x ON t4(x DESC); 99 INSERT INTO t4(x) SELECT x FROM t3; 100 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x; 101 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 102 do_execsql_test like3-4.1 { 103 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC; 104 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 105 do_execsql_test like3-4.1ck { 106 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC; 107 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 108 do_execsql_test like3-4.2 { 109 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC; 110 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 111 do_execsql_test like3-4.2ck { 112 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC; 113 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 114 115 # 2018-09-10 ticket https://www.sqlite.org/src/tktview/c94369cae9b561b1f996 116 # The like optimization fails for a column with numeric affinity if 117 # the pattern '/%' or begins with the escape character. 118 # 119 do_execsql_test like3-5.100 { 120 CREATE TABLE t5a(x INT UNIQUE COLLATE nocase); 121 INSERT INTO t5a(x) VALUES('/abc'),(123),(-234); 122 SELECT x FROM t5a WHERE x LIKE '/%'; 123 } {/abc} 124 do_eqp_test like3-5.101 { 125 SELECT x FROM t5a WHERE x LIKE '/%'; 126 } { 127 QUERY PLAN 128 `--SCAN t5a 129 } 130 do_execsql_test like3-5.110 { 131 SELECT x FROM t5a WHERE x LIKE '/a%'; 132 } {/abc} 133 ifcapable !icu { 134 do_eqp_test like3-5.111 { 135 SELECT x FROM t5a WHERE x LIKE '/a%'; 136 } { 137 QUERY PLAN 138 `--SEARCH t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?) 139 } 140 } 141 do_execsql_test like3-5.120 { 142 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^'; 143 } {123} 144 do_eqp_test like3-5.121 { 145 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^'; 146 } { 147 QUERY PLAN 148 `--SCAN t5a 149 } 150 do_execsql_test like3-5.122 { 151 SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^'; 152 } {-234} 153 do_eqp_test like3-5.123 { 154 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^'; 155 } { 156 QUERY PLAN 157 `--SCAN t5a 158 } 159 160 do_execsql_test like3-5.200 { 161 CREATE TABLE t5b(x INT UNIQUE COLLATE binary); 162 INSERT INTO t5b(x) VALUES('/abc'),(123),(-234); 163 SELECT x FROM t5b WHERE x GLOB '/*'; 164 } {/abc} 165 do_eqp_test like3-5.201 { 166 SELECT x FROM t5b WHERE x GLOB '/*'; 167 } { 168 QUERY PLAN 169 `--SCAN t5b 170 } 171 do_execsql_test like3-5.210 { 172 SELECT x FROM t5b WHERE x GLOB '/a*'; 173 } {/abc} 174 do_eqp_test like3-5.211 { 175 SELECT x FROM t5b WHERE x GLOB '/a*'; 176 } { 177 QUERY PLAN 178 `--SEARCH t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?) 179 } 180 181 # 2019-05-01 182 # another case of the above reported on the mailing list by Manuel Rigger. 183 # 184 do_execsql_test like3-5.300 { 185 CREATE TABLE t5c (c0 REAL); 186 CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE); 187 INSERT INTO t5c(rowid, c0) VALUES (99,'+/'); 188 SELECT * FROM t5c WHERE (c0 LIKE '+/'); 189 } {+/} 190 191 # 2019-05-08 192 # Yet another case for the above from Manuel Rigger. 193 # 194 do_execsql_test like3-5.400 { 195 DROP TABLE IF EXISTS t0; 196 CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE); 197 INSERT INTO t0(c0) VALUES ('./'); 198 SELECT * FROM t0 WHERE t0.c0 LIKE './'; 199 } {./} 200 201 # 2019-06-14 202 # Ticket https://www.sqlite.org/src/info/ce8717f0885af975 203 do_execsql_test like3-5.410 { 204 DROP TABLE IF EXISTS t0; 205 CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE); 206 INSERT INTO t0(c0) VALUES ('.1%'); 207 SELECT * FROM t0 WHERE t0.c0 LIKE '.1%'; 208 } {.1%} 209 210 # 2019-09-03 211 # Ticket https://www.sqlite.org/src/info/0f0428096f 212 do_execsql_test like3-5.420 { 213 DROP TABLE IF EXISTS t0; 214 CREATE TABLE t0(c0 UNIQUE); 215 INSERT INTO t0(c0) VALUES(-1); 216 SELECT * FROM t0 WHERE t0.c0 GLOB '-*'; 217 } {-1} 218 do_execsql_test like3-5.421 { 219 SELECT t0.c0 GLOB '-*' FROM t0; 220 } {1} 221 222 223 224 # 2019-02-27 225 # Verify that the LIKE optimization works with an ESCAPE clause when 226 # using PRAGMA case_sensitive_like=ON. 227 # 228 ifcapable !icu { 229 do_execsql_test like3-6.100 { 230 DROP TABLE IF EXISTS t1; 231 CREATE TABLE t1(path TEXT COLLATE nocase PRIMARY KEY,a,b,c) WITHOUT ROWID; 232 } 233 do_eqp_test like3-6.110 { 234 SELECT * FROM t1 WHERE path LIKE 'a%'; 235 } { 236 QUERY PLAN 237 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?) 238 } 239 do_eqp_test like3-6.120 { 240 SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x'; 241 } { 242 QUERY PLAN 243 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?) 244 } 245 do_execsql_test like3-6.200 { 246 DROP TABLE IF EXISTS t2; 247 CREATE TABLE t2(path TEXT,x,y,z); 248 CREATE INDEX t2path ON t2(path COLLATE nocase); 249 CREATE INDEX t2path2 ON t2(path); 250 } 251 do_eqp_test like3-6.210 { 252 SELECT * FROM t2 WHERE path LIKE 'a%'; 253 } { 254 QUERY PLAN 255 `--SEARCH t2 USING INDEX t2path (path>? AND path<?) 256 } 257 do_eqp_test like3-6.220 { 258 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\'; 259 } { 260 QUERY PLAN 261 `--SEARCH t2 USING INDEX t2path (path>? AND path<?) 262 } 263 db eval {PRAGMA case_sensitive_like=ON} 264 do_eqp_test like3-6.230 { 265 SELECT * FROM t2 WHERE path LIKE 'a%'; 266 } { 267 QUERY PLAN 268 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?) 269 } 270 do_eqp_test like3-6.240 { 271 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\'; 272 } { 273 QUERY PLAN 274 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?) 275 } 276 } 277 278 finish_test