test/like3.test

   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 # 2018-09-10 ticket https://www.sqlite.org/src/tktview/c94369cae9b561b1f996
 116 # The like optimization fails for a column with numeric affinity if
 117 # the pattern '/%' or begins with the escape character.
 118 #
 119 do_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}
 124 do_eqp_test like3-5.101 {
 125   SELECT x FROM t5a WHERE x LIKE '/%';
 126 } {
 127   QUERY PLAN
 128   `--SCAN TABLE t5a
 129 }
 130 do_execsql_test like3-5.110 {
 131   SELECT x FROM t5a WHERE x LIKE '/a%';
 132 } {/abc}
 133 ifcapable !icu {
 134 do_eqp_test like3-5.111 {
 135   SELECT x FROM t5a WHERE x LIKE '/a%';
 136 } {
 137   QUERY PLAN
 138   `--SEARCH TABLE t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?)
 139 }
 140 }
 141 do_execsql_test like3-5.120 {
 142   SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
 143 } {123}
 144 do_eqp_test like3-5.121 {
 145   SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
 146 } {
 147   QUERY PLAN
 148   `--SCAN TABLE t5a
 149 }
 150 do_execsql_test like3-5.122 {
 151   SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';
 152 } {-234}
 153 do_eqp_test like3-5.123 {
 154   SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
 155 } {
 156   QUERY PLAN
 157   `--SCAN TABLE t5a
 158 }
 159
 160 do_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}
 165 do_eqp_test like3-5.201 {
 166   SELECT x FROM t5b WHERE x GLOB '/*';
 167 } {
 168   QUERY PLAN
 169   `--SCAN TABLE t5b
 170 }
 171 do_execsql_test like3-5.210 {
 172   SELECT x FROM t5b WHERE x GLOB '/a*';
 173 } {/abc}
 174 do_eqp_test like3-5.211 {
 175   SELECT x FROM t5b WHERE x GLOB '/a*';
 176 } {
 177   QUERY PLAN
 178   `--SEARCH TABLE t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)
 179 }
 180
 181 # 2019-05-01
 182 # another case of the above reported on the mailing list by Manuel Rigger.
 183 #
 184 do_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-08
 192 # Yet another case for the above from Manuel Rigger.
 193 #
 194 do_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-14
 202 # Ticket https://www.sqlite.org/src/info/ce8717f0885af975
 203 do_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-03
 211 # Ticket https://www.sqlite.org/src/info/0f0428096f
 212 do_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}
 218 do_execsql_test like3-5.421 {
 219   SELECT t0.c0 GLOB '-*' FROM t0;
 220 } {1}
 221
 222
 223
 224 # 2019-02-27
 225 # Verify that the LIKE optimization works with an ESCAPE clause when
 226 # using PRAGMA case_sensitive_like=ON.
 227 #
 228 ifcapable !icu {
 229 do_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 }
 233 do_eqp_test like3-6.110 {
 234   SELECT * FROM t1 WHERE path LIKE 'a%';
 235 } {
 236   QUERY PLAN
 237   `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
 238 }
 239 do_eqp_test like3-6.120 {
 240   SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x';
 241 } {
 242   QUERY PLAN
 243   `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
 244 }
 245 do_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 }
 251 do_eqp_test like3-6.210 {
 252   SELECT * FROM t2 WHERE path LIKE 'a%';
 253 } {
 254   QUERY PLAN
 255   `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
 256 }
 257 do_eqp_test like3-6.220 {
 258   SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
 259 } {
 260   QUERY PLAN
 261   `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
 262 }
 263 db eval {PRAGMA case_sensitive_like=ON}
 264 do_eqp_test like3-6.230 {
 265   SELECT * FROM t2 WHERE path LIKE 'a%';
 266 } {
 267   QUERY PLAN
 268   `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)
 269 }
 270 do_eqp_test like3-6.240 {
 271   SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
 272 } {
 273   QUERY PLAN
 274   `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)
 275 }
 276 }
 277
 278 finish_test