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 29set testdir [file dirname $argv0] 30source $testdir/tester.tcl 31 32do_execsql_test like3-1.1 { 33 PRAGMA encoding=UTF8; 34 CREATE TABLE t1(a,b TEXT COLLATE nocase); 35 INSERT INTO t1(a,b) 36 VALUES(1,'abc'), 37 (2,'ABX'), 38 (3,'BCD'), 39 (4,x'616263'), 40 (5,x'414258'), 41 (6,x'424344'); 42 CREATE INDEX t1ba ON t1(b,a); 43 44 SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a; 45} {1 abc 2 ABX 4 abc 5 ABX} 46do_execsql_test like3-1.2 { 47 SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a; 48} {1 abc 2 ABX 4 abc 5 ABX} 49 50do_execsql_test like3-2.0 { 51 CREATE TABLE t2(a, b TEXT); 52 INSERT INTO t2 SELECT a, b FROM t1; 53 CREATE INDEX t2ba ON t2(b,a); 54 SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a; 55} {1 abc 4 abc} 56do_execsql_test like3-2.1 { 57 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a; 58} {1 abc 4 abc} 59do_execsql_test like3-2.2 { 60 SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*' 61} {4 abc} 62do_execsql_test like3-2.3 { 63 SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*' 64} {4 abc} 65do_execsql_test like3-2.4 { 66 SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162' 67} {4 abc} 68do_execsql_test like3-2.5 { 69 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162' 70} {4 abc} 71 72do_execsql_test like3-3.0 { 73 CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase); 74 INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz'); 75 INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3; 76 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x; 77} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 78do_execsql_test like3-3.1 { 79 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC; 80} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 81do_execsql_test like3-3.1ck { 82 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC; 83} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 84do_execsql_test like3-3.2 { 85 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC; 86} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 87do_execsql_test like3-3.2ck { 88 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC; 89} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 90 91do_execsql_test like3-4.0 { 92 CREATE TABLE t4(x TEXT COLLATE nocase); 93 CREATE INDEX t4x ON t4(x DESC); 94 INSERT INTO t4(x) SELECT x FROM t3; 95 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x; 96} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 97do_execsql_test like3-4.1 { 98 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC; 99} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 100do_execsql_test like3-4.1ck { 101 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC; 102} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'} 103do_execsql_test like3-4.2 { 104 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC; 105} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 106do_execsql_test like3-4.2ck { 107 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC; 108} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'} 109 110 111 112finish_test 113