AryaWu/sqlite
0
1# 2015-03-062#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#12# This file implements regression tests for SQLite library. The13# focus of this file is testing the LIKE and GLOB operators and14# in particular the optimizations that occur to help those operators15# run faster and that those optimizations work correctly when there16# are both strings and blobs being tested.17#18# Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following19# 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 29set testdir [file dirname $argv0]30source $testdir/tester.tcl31 32ifcapable !like_match_blobs {33 finish_test34 return35}36 37do_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}51do_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 55do_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}61do_execsql_test like3-2.1 {62 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;63} {1 abc 4 abc}64do_execsql_test like3-2.2 {65 SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'66} {4 abc}67do_execsql_test like3-2.3 {68 SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'69} {4 abc}70do_execsql_test like3-2.4 {71 SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'72} {4 abc}73do_execsql_test like3-2.5 {74 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'75} {4 abc}76 77do_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'}83do_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'}86do_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'}89do_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'}92do_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 96do_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'}102do_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'}105do_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'}108do_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'}111do_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://sqlite.org/src/tktview/c94369cae9b561b1f996116# The like optimization fails for a column with numeric affinity if117# the pattern '/%' or begins with the escape character.118#119do_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}124do_eqp_test like3-5.101 {125 SELECT x FROM t5a WHERE x LIKE '/%';126} {127 QUERY PLAN128 `--SCAN t5a129}130do_execsql_test like3-5.110 {131 SELECT x FROM t5a WHERE x LIKE '/a%';132} {/abc}133ifcapable !icu {134do_eqp_test like3-5.111 {135 SELECT x FROM t5a WHERE x LIKE '/a%';136} {137 QUERY PLAN138 `--SEARCH t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?)139}140}141do_execsql_test like3-5.120 {142 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';143} {123}144do_eqp_test like3-5.121 {145 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';146} {147 QUERY PLAN148 `--SCAN t5a149}150do_execsql_test like3-5.122 {151 SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';152} {-234}153do_eqp_test like3-5.123 {154 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';155} {156 QUERY PLAN157 `--SCAN t5a158}159 160do_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}165do_eqp_test like3-5.201 {166 SELECT x FROM t5b WHERE x GLOB '/*';167} {168 QUERY PLAN169 `--SCAN t5b170}171do_execsql_test like3-5.210 {172 SELECT x FROM t5b WHERE x GLOB '/a*';173} {/abc}174do_eqp_test like3-5.211 {175 SELECT x FROM t5b WHERE x GLOB '/a*';176} {177 QUERY PLAN178 `--SEARCH t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)179}180 181# 2019-05-01182# another case of the above reported on the mailing list by Manuel Rigger.183#184do_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-08192# Yet another case for the above from Manuel Rigger.193#194do_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-14202# Ticket https://sqlite.org/src/info/ce8717f0885af975203do_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-03211# Ticket https://sqlite.org/src/info/0f0428096f212do_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}218do_execsql_test like3-5.421 {219 SELECT t0.c0 GLOB '-*' FROM t0;220} {1}221 222 223 224# 2019-02-27225# Verify that the LIKE optimization works with an ESCAPE clause when226# using PRAGMA case_sensitive_like=ON.227#228ifcapable !icu {229do_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}233do_eqp_test like3-6.110 {234 SELECT * FROM t1 WHERE path LIKE 'a%';235} {236 QUERY PLAN237 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)238}239do_eqp_test like3-6.120 {240 SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x';241} {242 QUERY PLAN243 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)244}245do_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}251do_eqp_test like3-6.210 {252 SELECT * FROM t2 WHERE path LIKE 'a%';253} {254 QUERY PLAN255 `--SEARCH t2 USING INDEX t2path (path>? AND path<?)256}257do_eqp_test like3-6.220 {258 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';259} {260 QUERY PLAN261 `--SEARCH t2 USING INDEX t2path (path>? AND path<?)262}263db eval {PRAGMA case_sensitive_like=ON}264do_eqp_test like3-6.230 {265 SELECT * FROM t2 WHERE path LIKE 'a%';266} {267 QUERY PLAN268 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)269}270do_eqp_test like3-6.240 {271 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';272} {273 QUERY PLAN274 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)275}276}277 278#-------------------------------------------------------------------------279 280ifcapable utf16 {281 reset_db282 do_execsql_test like3-7.0 {283 PRAGMA encoding = 'UTF-16be';284 285 CREATE TABLE Example(word TEXT NOT NULL);286 CREATE INDEX Example_word on Example(word);287 288 INSERT INTO Example VALUES(char(0x307F));289 }290 291 do_execsql_test like3-7.1 {292 SELECT char(0x307F)=='み';293 } {1}294 295 do_execsql_test like3-7.1 {296 SELECT * FROM Example WHERE word GLOB 'み*'297 } {み}298 299 do_execsql_test like3-7.2 {300 SELECT * FROM Example WHERE word >= char(0x307F) AND word < char(0x3080);301 } {み}302}303 304#-------------------------------------------------------------------------305reset_db306 307# See forum thread https://sqlite.org/forum/info/d7b90d92ffbfc61f308foreach enc {309 UTF-8310 UTF-16le 311 UTF-16be312} {313 ifcapable icu {314 if {$enc=="UTF-8"} {315 # The invalid UTF8 used in these tests is incompatible with ICU316 # https://sqlite.org/forum/forumpost/2ca8a09a7e317 continue318 }319 }320 foreach {tn expr} {321 1 "CAST (X'FF' AS TEXT)"322 2 "CAST (X'FFBF' AS TEXT)"323 3 "CAST (X'FFBFBF' AS TEXT)"324 4 "CAST (X'FFBFBFBF' AS TEXT)"325 326 5 "'abc' || CAST (X'FF' AS TEXT)"327 6 "'def' || CAST (X'FFBF' AS TEXT)"328 7 "'ghi' || CAST (X'FFBFBF' AS TEXT)"329 8 "'jkl' || CAST (X'FFBFBFBF' AS TEXT)"330 } {331 reset_db332 execsql "PRAGMA encoding = '$enc'"333 set tn utf[string range $enc 4 end].$tn334 do_execsql_test like3-8.$tn.1 {335 CREATE TABLE t1(x);336 }337 338 do_execsql_test like3-8.$tn.2 {339 PRAGMA encoding340 } $enc341 342 do_execsql_test like3-8.$tn.3 "343 INSERT INTO t1 VALUES( $expr )344 "345 346 do_execsql_test like3-8.$tn.4 {347 SELECT typeof(x) FROM t1348 } {text}349 350 set x [db one {SELECT x || '%' FROM t1}]351 352 do_execsql_test like3-8.$tn.5 {353 SELECT rowid FROM t1 WHERE x LIKE $x354 } 1355 356 do_execsql_test like3-8.$tn.6 {357 CREATE INDEX i1 ON t1(x);358 }359 360 do_execsql_test like3-8.$tn.7 {361 SELECT rowid FROM t1 WHERE x LIKE $x362 } 1363 }364}365 366finish_test367