1 # 2005 August 13

     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 # This file implements regression tests for SQLite library.  The

    12 # focus of this file is testing the LIKE and GLOB operators and

    13 # in particular the optimizations that occur to help those operators

    14 # run faster.

    15 #

    16 # $Id: like.test,v 1.10 2008/09/09 12:31:34 drh Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 # Create some sample data to work with.

    22 #

    23 do_test like-1.0 {

    24   execsql {

    25     CREATE TABLE t1(x TEXT);

    26   }

    27   foreach str {

    28     a

    29     ab

    30     abc

    31     abcd

    32 

    33     acd

    34     abd

    35     bc

    36     bcd

    37 

    38     xyz

    39     ABC

    40     CDE

    41     {ABC abc xyz}

    42   } {

    43     db eval {INSERT INTO t1 VALUES(:str)}

    44   }

    45   execsql {

    46     SELECT count(*) FROM t1;

    47   }

    48 } {12}

    49 

    50 # Test that both case sensitive and insensitive version of LIKE work.

    51 #

    52 do_test like-1.1 {

    53   execsql {

    54     SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;

    55   }

    56 } {ABC abc}

    57 do_test like-1.2 {

    58   execsql {

    59     SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;

    60   }

    61 } {abc}

    62 do_test like-1.3 {

    63   execsql {

    64     SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;

    65   }

    66 } {ABC abc}

    67 do_test like-1.4 {

    68   execsql {

    69     SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;

    70   }

    71 } {ABC abc}

    72 do_test like-1.5 {

    73   execsql {

    74     PRAGMA case_sensitive_like=on;

    75     SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;

    76   }

    77 } {abc}

    78 do_test like-1.6 {

    79   execsql {

    80     SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;

    81   }

    82 } {abc}

    83 do_test like-1.7 {

    84   execsql {

    85     SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;

    86   }

    87 } {ABC}

    88 do_test like-1.8 {

    89   execsql {

    90     SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;

    91   }

    92 } {}

    93 do_test like-1.9 {

    94   execsql {

    95     PRAGMA case_sensitive_like=off;

    96     SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;

    97   }

    98 } {ABC abc}

    99 

   100 # Tests of the REGEXP operator

   101 #

   102 do_test like-2.1 {

   103   proc test_regexp {a b} {

   104     return [regexp $a $b]

   105   }

   106   db function regexp test_regexp

   107   execsql {

   108     SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;

   109   }

   110 } {{ABC abc xyz} abc abcd}

   111 do_test like-2.2 {

   112   execsql {

   113     SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;

   114   }

   115 } {abc abcd}

   116 

   117 # Tests of the MATCH operator

   118 #

   119 do_test like-2.3 {

   120   proc test_match {a b} {

   121     return [string match $a $b]

   122   }

   123   db function match -argcount 2 test_match

   124   execsql {

   125     SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;

   126   }

   127 } {{ABC abc xyz} abc abcd}

   128 do_test like-2.4 {

   129   execsql {

   130     SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1;

   131   }

   132 } {abc abcd}

   133 

   134 # For the remaining tests, we need to have the like optimizations

   135 # enabled.

   136 #

   137 ifcapable !like_opt {

   138   finish_test

   139   return

   140 } 

   141 

   142 # This procedure executes the SQL.  Then it appends to the result the

   143 # "sort" or "nosort" keyword (as in the cksort procedure above) then

   144 # it appends the ::sqlite_query_plan variable.

   145 #

   146 proc queryplan {sql} {

   147   set ::sqlite_sort_count 0

   148   set data [execsql $sql]

   149   if {$::sqlite_sort_count} {set x sort} {set x nosort}

   150   lappend data $x

   151   return [concat $data $::sqlite_query_plan]

   152 }

   153 

   154 # Perform tests on the like optimization.

   155 #

   156 # With no index on t1.x and with case sensitivity turned off, no optimization

   157 # is performed.

   158 #

   159 do_test like-3.1 {

   160   set sqlite_like_count 0

   161   queryplan {

   162     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;

   163   }

   164 } {ABC {ABC abc xyz} abc abcd sort t1 {}}

   165 do_test like-3.2 {

   166   set sqlite_like_count

   167 } {12}

   168 

   169 # With an index on t1.x and case sensitivity on, optimize completely.

   170 #

   171 do_test like-3.3 {

   172   set sqlite_like_count 0

   173   execsql {

   174     PRAGMA case_sensitive_like=on;

   175     CREATE INDEX i1 ON t1(x);

   176   }

   177   queryplan {

   178     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;

   179   }

   180 } {abc abcd nosort {} i1}

   181 do_test like-3.4 {

   182   set sqlite_like_count

   183 } 0

   184 

   185 # Partial optimization when the pattern does not end in '%'

   186 #

   187 do_test like-3.5 {

   188   set sqlite_like_count 0

   189   queryplan {

   190     SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;

   191   }

   192 } {abc nosort {} i1}

   193 do_test like-3.6 {

   194   set sqlite_like_count

   195 } 6

   196 do_test like-3.7 {

   197   set sqlite_like_count 0

   198   queryplan {

   199     SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;

   200   }

   201 } {abcd abd nosort {} i1}

   202 do_test like-3.8 {

   203   set sqlite_like_count

   204 } 4

   205 do_test like-3.9 {

   206   set sqlite_like_count 0

   207   queryplan {

   208     SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;

   209   }

   210 } {abc abcd nosort {} i1}

   211 do_test like-3.10 {

   212   set sqlite_like_count

   213 } 6

   214 

   215 # No optimization when the pattern begins with a wildcard.

   216 # Note that the index is still used but only for sorting.

   217 #

   218 do_test like-3.11 {

   219   set sqlite_like_count 0

   220   queryplan {

   221     SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;

   222   }

   223 } {abcd bcd nosort {} i1}

   224 do_test like-3.12 {

   225   set sqlite_like_count

   226 } 12

   227 

   228 # No optimization for case insensitive LIKE

   229 #

   230 do_test like-3.13 {

   231   set sqlite_like_count 0

   232   queryplan {

   233     PRAGMA case_sensitive_like=off;

   234     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;

   235   }

   236 } {ABC {ABC abc xyz} abc abcd nosort {} i1}

   237 do_test like-3.14 {

   238   set sqlite_like_count

   239 } 12

   240 

   241 # No optimization without an index.

   242 #

   243 do_test like-3.15 {

   244   set sqlite_like_count 0

   245   queryplan {

   246     PRAGMA case_sensitive_like=on;

   247     DROP INDEX i1;

   248     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;

   249   }

   250 } {abc abcd sort t1 {}}

   251 do_test like-3.16 {

   252   set sqlite_like_count

   253 } 12

   254 

   255 # No GLOB optimization without an index.

   256 #

   257 do_test like-3.17 {

   258   set sqlite_like_count 0

   259   queryplan {

   260     SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;

   261   }

   262 } {abc abcd sort t1 {}}

   263 do_test like-3.18 {

   264   set sqlite_like_count

   265 } 12

   266 

   267 # GLOB is optimized regardless of the case_sensitive_like setting.

   268 #

   269 do_test like-3.19 {

   270   set sqlite_like_count 0

   271   queryplan {

   272     CREATE INDEX i1 ON t1(x);

   273     SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;

   274   }

   275 } {abc abcd nosort {} i1}

   276 do_test like-3.20 {

   277   set sqlite_like_count

   278 } 0

   279 do_test like-3.21 {

   280   set sqlite_like_count 0

   281   queryplan {

   282     PRAGMA case_sensitive_like=on;

   283     SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;

   284   }

   285 } {abc abcd nosort {} i1}

   286 do_test like-3.22 {

   287   set sqlite_like_count

   288 } 0

   289 do_test like-3.23 {

   290   set sqlite_like_count 0

   291   queryplan {

   292     PRAGMA case_sensitive_like=off;

   293     SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;

   294   }

   295 } {abd acd nosort {} i1}

   296 do_test like-3.24 {

   297   set sqlite_like_count

   298 } 6

   299 

   300 # No optimization if the LHS of the LIKE is not a column name or

   301 # if the RHS is not a string.

   302 #

   303 do_test like-4.1 {

   304   execsql {PRAGMA case_sensitive_like=on}

   305   set sqlite_like_count 0

   306   queryplan {

   307     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1

   308   }

   309 } {abc abcd nosort {} i1}

   310 do_test like-4.2 {

   311   set sqlite_like_count

   312 } 0

   313 do_test like-4.3 {

   314   set sqlite_like_count 0

   315   queryplan {

   316     SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1

   317   }

   318 } {abc abcd nosort {} i1}

   319 do_test like-4.4 {

   320   set sqlite_like_count

   321 } 12

   322 do_test like-4.5 {

   323   set sqlite_like_count 0

   324   queryplan {

   325     SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1

   326   }

   327 } {abc abcd nosort {} i1}

   328 do_test like-4.6 {

   329   set sqlite_like_count

   330 } 12

   331 

   332 # Collating sequences on the index disable the LIKE optimization.

   333 # Or if the NOCASE collating sequence is used, the LIKE optimization

   334 # is enabled when case_sensitive_like is OFF.

   335 #

   336 do_test like-5.1 {

   337   execsql {PRAGMA case_sensitive_like=off}

   338   set sqlite_like_count 0

   339   queryplan {

   340     SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1

   341   }

   342 } {ABC {ABC abc xyz} abc abcd nosort {} i1}

   343 do_test like-5.2 {

   344   set sqlite_like_count

   345 } 12

   346 do_test like-5.3 {

   347   execsql {

   348     CREATE TABLE t2(x COLLATE NOCASE);

   349     INSERT INTO t2 SELECT * FROM t1;

   350     CREATE INDEX i2 ON t2(x COLLATE NOCASE);

   351   }

   352   set sqlite_like_count 0

   353   queryplan {

   354     SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1

   355   }

   356 } {abc ABC {ABC abc xyz} abcd nosort {} i2}

   357 do_test like-5.4 {

   358   set sqlite_like_count

   359 } 0

   360 do_test like-5.5 {

   361   execsql {

   362     PRAGMA case_sensitive_like=on;

   363   }

   364   set sqlite_like_count 0

   365   queryplan {

   366     SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1

   367   }

   368 } {abc abcd nosort {} i2}

   369 do_test like-5.6 {

   370   set sqlite_like_count

   371 } 12

   372 do_test like-5.7 {

   373   execsql {

   374     PRAGMA case_sensitive_like=off;

   375   }

   376   set sqlite_like_count 0

   377   queryplan {

   378     SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1

   379   }

   380 } {abc abcd nosort {} i2}

   381 do_test like-5.8 {

   382   set sqlite_like_count

   383 } 12

   384 do_test like-5.11 {

   385   execsql {PRAGMA case_sensitive_like=off}

   386   set sqlite_like_count 0

   387   queryplan {

   388     SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1

   389   }

   390 } {ABC {ABC abc xyz} abc abcd nosort {} i1}

   391 do_test like-5.12 {

   392   set sqlite_like_count

   393 } 12

   394 do_test like-5.13 {

   395   set sqlite_like_count 0

   396   queryplan {

   397     SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1

   398   }

   399 } {abc ABC {ABC abc xyz} abcd nosort {} i2}

   400 do_test like-5.14 {

   401   set sqlite_like_count

   402 } 0

   403 do_test like-5.15 {

   404   execsql {

   405     PRAGMA case_sensitive_like=on;

   406   }

   407   set sqlite_like_count 0

   408   queryplan {

   409     SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1

   410   }

   411 } {ABC {ABC abc xyz} nosort {} i2}

   412 do_test like-5.16 {

   413   set sqlite_like_count

   414 } 12

   415 do_test like-5.17 {

   416   execsql {

   417     PRAGMA case_sensitive_like=off;

   418   }

   419   set sqlite_like_count 0

   420   queryplan {

   421     SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1

   422   }

   423 } {ABC {ABC abc xyz} nosort {} i2}

   424 do_test like-5.18 {

   425   set sqlite_like_count

   426 } 12

   427 

   428 # Boundary case.  The prefix for a LIKE comparison is rounded up

   429 # when constructing the comparison.  Example:  "ab" becomes "ac".

   430 # In other words, the last character is increased by one.

   431 #

   432 # Make sure this happens correctly when the last character is a 

   433 # "z" and we are doing case-insensitive comparisons.

   434 #

   435 # Ticket #2959

   436 #

   437 do_test like-5.21 {

   438   execsql {

   439     PRAGMA case_sensitive_like=off;

   440     INSERT INTO t2 VALUES('ZZ-upper-upper');

   441     INSERT INTO t2 VALUES('zZ-lower-upper');

   442     INSERT INTO t2 VALUES('Zz-upper-lower');

   443     INSERT INTO t2 VALUES('zz-lower-lower');

   444   }

   445   queryplan {

   446     SELECT x FROM t2 WHERE x LIKE 'zz%';

   447   }

   448 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}

   449 do_test like-5.22 {

   450   queryplan {

   451     SELECT x FROM t2 WHERE x LIKE 'zZ%';

   452   }

   453 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}

   454 do_test like-5.23 {

   455   queryplan {

   456     SELECT x FROM t2 WHERE x LIKE 'Zz%';

   457   }

   458 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}

   459 do_test like-5.24 {

   460   queryplan {

   461     SELECT x FROM t2 WHERE x LIKE 'ZZ%';

   462   }

   463 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}

   464 do_test like-5.25 {

   465   queryplan {

   466     PRAGMA case_sensitive_like=on;

   467     CREATE TABLE t3(x);

   468     CREATE INDEX i3 ON t3(x);

   469     INSERT INTO t3 VALUES('ZZ-upper-upper');

   470     INSERT INTO t3 VALUES('zZ-lower-upper');

   471     INSERT INTO t3 VALUES('Zz-upper-lower');

   472     INSERT INTO t3 VALUES('zz-lower-lower');

   473     SELECT x FROM t3 WHERE x LIKE 'zz%';

   474   }

   475 } {zz-lower-lower nosort {} i3}

   476 do_test like-5.26 {

   477   queryplan {

   478     SELECT x FROM t3 WHERE x LIKE 'zZ%';

   479   }

   480 } {zZ-lower-upper nosort {} i3}

   481 do_test like-5.27 {

   482   queryplan {

   483     SELECT x FROM t3 WHERE x LIKE 'Zz%';

   484   }

   485 } {Zz-upper-lower nosort {} i3}

   486 do_test like-5.28 {

   487   queryplan {

   488     SELECT x FROM t3 WHERE x LIKE 'ZZ%';

   489   }

   490 } {ZZ-upper-upper nosort {} i3}

   491 

   492 

   493 # ticket #2407

   494 #

   495 # Make sure the LIKE prefix optimization does not strip off leading

   496 # characters of the like pattern that happen to be quote characters.

   497 #

   498 do_test like-6.1 {

   499   foreach x { 'abc 'bcd 'def 'ax } {

   500     set x2 '[string map {' ''} $x]'

   501     db eval "INSERT INTO t2 VALUES($x2)"

   502   }

   503   execsql {

   504     SELECT * FROM t2 WHERE x LIKE '''a%'

   505   }

   506 } {'abc 'ax}

   507 

   508 do_test like-7.1 {

   509   execsql {

   510     SELECT * FROM t1 WHERE rowid GLOB '1*';

   511   }

   512 } {a}

   513 

   514 # ticket #3345.

   515 #

   516 # Overloading the LIKE function with -1 for the number of arguments

   517 # will overload both the 2-argument and the 3-argument LIKE.

   518 #

   519 do_test like-8.1 {

   520   db eval {

   521     CREATE TABLE t8(x);

   522     INSERT INTO t8 VALUES('abcdef');

   523     INSERT INTO t8 VALUES('ghijkl');

   524     INSERT INTO t8 VALUES('mnopqr');

   525     SELECT 1, x FROM t8 WHERE x LIKE '%h%';

   526     SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';

   527   }

   528 } {1 ghijkl 2 ghijkl}

   529 do_test like-8.2 {

   530   proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE

   531   db function like newlike       ;# Uses -1 for nArg in sqlite3_create_function

   532   db cache flush

   533   db eval {

   534     SELECT 1, x FROM t8 WHERE x LIKE '%h%';

   535     SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';

   536   }

   537 } {1 ghijkl 2 ghijkl}

   538 do_test like-8.3 {

   539   db function like -argcount 2 newlike

   540   db eval {

   541     SELECT 1, x FROM t8 WHERE x LIKE '%h%';

   542     SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';

   543   }

   544 } {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl}

   545 do_test like-8.4 {

   546   db function like -argcount 3 newlike

   547   db eval {

   548     SELECT 1, x FROM t8 WHERE x LIKE '%h%';

   549     SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';

   550   }

   551 } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}

   552 

   553 

   554 finish_test