# 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
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-1.3 {
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-1.4 {
SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
} {1 abc 4 abc}
finish_test