modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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  finish_test