# 2015-03-06 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements regression tests for SQLite library. The # focus of this file is testing the LIKE and GLOB operators and # in particular the optimizations that occur to help those operators # run faster and that those optimizations work correctly when there # are both strings and blobs being tested. # # Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following # SQL was not working correctly: # # CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase); # INSERT INTO t1(x) VALUES(x'616263'); # SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%'; # SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%'; # # This script verifies that it works right now. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !like_match_blobs { finish_test return } do_execsql_test like3-1.1 { PRAGMA encoding=UTF8; CREATE TABLE t1(a,b TEXT COLLATE nocase); INSERT INTO t1(a,b) VALUES(1,'abc'), (2,'ABX'), (3,'BCD'), (4,x'616263'), (5,x'414258'), (6,x'424344'); CREATE INDEX t1ba ON t1(b,a); SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a; } {1 abc 2 ABX 4 abc 5 ABX} do_execsql_test like3-1.2 { SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a; } {1 abc 2 ABX 4 abc 5 ABX} do_execsql_test like3-2.0 { CREATE TABLE t2(a, b TEXT); INSERT INTO t2 SELECT a, b FROM t1; CREATE INDEX t2ba ON t2(b,a); SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a; } {1 abc 4 abc} do_execsql_test like3-2.1 { SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a; } {1 abc 4 abc} do_execsql_test like3-2.2 { SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*' } {4 abc} do_execsql_test like3-2.3 { SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*' } {4 abc} do_execsql_test like3-2.4 { SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162' } {4 abc} do_execsql_test like3-2.5 { SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162' } {4 abc} do_execsql_test like3-3.0 { CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase); INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz'); INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3; SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} do_execsql_test like3-3.1 { SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC; } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} do_execsql_test like3-3.1ck { SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC; } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} do_execsql_test like3-3.2 { SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} do_execsql_test like3-3.2ck { SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} do_execsql_test like3-4.0 { CREATE TABLE t4(x TEXT COLLATE nocase); CREATE INDEX t4x ON t4(x DESC); INSERT INTO t4(x) SELECT x FROM t3; SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} do_execsql_test like3-4.1 { SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC; } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} do_execsql_test like3-4.1ck { SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC; } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} do_execsql_test like3-4.2 { SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} do_execsql_test like3-4.2ck { SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC; } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} # 2018-09-10 ticket https://www.sqlite.org/src/tktview/c94369cae9b561b1f996 # The like optimization fails for a column with numeric affinity if # the pattern '/%' or begins with the escape character. # do_execsql_test like3-5.100 { CREATE TABLE t5a(x INT UNIQUE COLLATE nocase); INSERT INTO t5a(x) VALUES('/abc'),(123),(-234); SELECT x FROM t5a WHERE x LIKE '/%'; } {/abc} do_eqp_test like3-5.101 { SELECT x FROM t5a WHERE x LIKE '/%'; } { QUERY PLAN `--SCAN TABLE t5a } do_execsql_test like3-5.110 { SELECT x FROM t5a WHERE x LIKE '/a%'; } {/abc} ifcapable !icu { do_eqp_test like3-5.111 { SELECT x FROM t5a WHERE x LIKE '/a%'; } { QUERY PLAN `--SEARCH TABLE t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x? AND x? AND path? AND path? AND path? AND path? AND path? AND path