/ Hex Artifact Content
Login

Artifact 2a3ddbd5d91503f914eabae67a47c4196fe33a58:


0000: 23 20 32 30 30 35 20 41 75 67 75 73 74 20 31 33  # 2005 August 13
0010: 0a 23 0a 23 20 54 68 65 20 61 75 74 68 6f 72 20  .#.# The author 
0020: 64 69 73 63 6c 61 69 6d 73 20 63 6f 70 79 72 69  disclaims copyri
0030: 67 68 74 20 74 6f 20 74 68 69 73 20 73 6f 75 72  ght to this sour
0040: 63 65 20 63 6f 64 65 2e 20 20 49 6e 20 70 6c 61  ce code.  In pla
0050: 63 65 20 6f 66 0a 23 20 61 20 6c 65 67 61 6c 20  ce of.# a legal 
0060: 6e 6f 74 69 63 65 2c 20 68 65 72 65 20 69 73 20  notice, here is 
0070: 61 20 62 6c 65 73 73 69 6e 67 3a 0a 23 0a 23 20  a blessing:.#.# 
0080: 20 20 20 4d 61 79 20 79 6f 75 20 64 6f 20 67 6f     May you do go
0090: 6f 64 20 61 6e 64 20 6e 6f 74 20 65 76 69 6c 2e  od and not evil.
00a0: 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20 66 69  .#    May you fi
00b0: 6e 64 20 66 6f 72 67 69 76 65 6e 65 73 73 20 66  nd forgiveness f
00c0: 6f 72 20 79 6f 75 72 73 65 6c 66 20 61 6e 64 20  or yourself and 
00d0: 66 6f 72 67 69 76 65 20 6f 74 68 65 72 73 2e 0a  forgive others..
00e0: 23 20 20 20 20 4d 61 79 20 79 6f 75 20 73 68 61  #    May you sha
00f0: 72 65 20 66 72 65 65 6c 79 2c 20 6e 65 76 65 72  re freely, never
0100: 20 74 61 6b 69 6e 67 20 6d 6f 72 65 20 74 68 61   taking more tha
0110: 6e 20 79 6f 75 20 67 69 76 65 2e 0a 23 0a 23 2a  n you give..#.#*
0120: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0130: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0140: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0150: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0160: 2a 2a 2a 2a 2a 2a 0a 23 20 54 68 69 73 20 66 69  ******.# This fi
0170: 6c 65 20 69 6d 70 6c 65 6d 65 6e 74 73 20 72 65  le implements re
0180: 67 72 65 73 73 69 6f 6e 20 74 65 73 74 73 20 66  gression tests f
0190: 6f 72 20 53 51 4c 69 74 65 20 6c 69 62 72 61 72  or SQLite librar
01a0: 79 2e 20 20 54 68 65 0a 23 20 66 6f 63 75 73 20  y.  The.# focus 
01b0: 6f 66 20 74 68 69 73 20 66 69 6c 65 20 69 73 20  of this file is 
01c0: 74 65 73 74 69 6e 67 20 74 68 65 20 4c 49 4b 45  testing the LIKE
01d0: 20 61 6e 64 20 47 4c 4f 42 20 6f 70 65 72 61 74   and GLOB operat
01e0: 6f 72 73 20 61 6e 64 0a 23 20 69 6e 20 70 61 72  ors and.# in par
01f0: 74 69 63 75 6c 61 72 20 74 68 65 20 6f 70 74 69  ticular the opti
0200: 6d 69 7a 61 74 69 6f 6e 73 20 74 68 61 74 20 6f  mizations that o
0210: 63 63 75 72 20 74 6f 20 68 65 6c 70 20 74 68 6f  ccur to help tho
0220: 73 65 20 6f 70 65 72 61 74 6f 72 73 0a 23 20 72  se operators.# r
0230: 75 6e 20 66 61 73 74 65 72 2e 0a 23 0a 23 20 24  un faster..#.# $
0240: 49 64 3a 20 6c 69 6b 65 2e 74 65 73 74 2c 76 20  Id: like.test,v 
0250: 31 2e 39 20 32 30 30 38 2f 30 32 2f 32 33 20 32  1.9 2008/02/23 2
0260: 31 3a 35 35 3a 34 30 20 64 72 68 20 45 78 70 20  1:55:40 drh Exp 
0270: 24 0a 0a 73 65 74 20 74 65 73 74 64 69 72 20 5b  $..set testdir [
0280: 66 69 6c 65 20 64 69 72 6e 61 6d 65 20 24 61 72  file dirname $ar
0290: 67 76 30 5d 0a 73 6f 75 72 63 65 20 24 74 65 73  gv0].source $tes
02a0: 74 64 69 72 2f 74 65 73 74 65 72 2e 74 63 6c 0a  tdir/tester.tcl.
02b0: 0a 23 20 43 72 65 61 74 65 20 73 6f 6d 65 20 73  .# Create some s
02c0: 61 6d 70 6c 65 20 64 61 74 61 20 74 6f 20 77 6f  ample data to wo
02d0: 72 6b 20 77 69 74 68 2e 0a 23 0a 64 6f 5f 74 65  rk with..#.do_te
02e0: 73 74 20 6c 69 6b 65 2d 31 2e 30 20 7b 0a 20 20  st like-1.0 {.  
02f0: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 43 52  execsql {.    CR
0300: 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 78 20  EATE TABLE t1(x 
0310: 54 45 58 54 29 3b 0a 20 20 7d 0a 20 20 66 6f 72  TEXT);.  }.  for
0320: 65 61 63 68 20 73 74 72 20 7b 0a 20 20 20 20 61  each str {.    a
0330: 0a 20 20 20 20 61 62 0a 20 20 20 20 61 62 63 0a  .    ab.    abc.
0340: 20 20 20 20 61 62 63 64 0a 0a 20 20 20 20 61 63      abcd..    ac
0350: 64 0a 20 20 20 20 61 62 64 0a 20 20 20 20 62 63  d.    abd.    bc
0360: 0a 20 20 20 20 62 63 64 0a 0a 20 20 20 20 78 79  .    bcd..    xy
0370: 7a 0a 20 20 20 20 41 42 43 0a 20 20 20 20 43 44  z.    ABC.    CD
0380: 45 0a 20 20 20 20 7b 41 42 43 20 61 62 63 20 78  E.    {ABC abc x
0390: 79 7a 7d 0a 20 20 7d 20 7b 0a 20 20 20 20 64 62  yz}.  } {.    db
03a0: 20 65 76 61 6c 20 7b 49 4e 53 45 52 54 20 49 4e   eval {INSERT IN
03b0: 54 4f 20 74 31 20 56 41 4c 55 45 53 28 3a 73 74  TO t1 VALUES(:st
03c0: 72 29 7d 0a 20 20 7d 0a 20 20 65 78 65 63 73 71  r)}.  }.  execsq
03d0: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 63  l {.    SELECT c
03e0: 6f 75 6e 74 28 2a 29 20 46 52 4f 4d 20 74 31 3b  ount(*) FROM t1;
03f0: 0a 20 20 7d 0a 7d 20 7b 31 32 7d 0a 0a 23 20 54  .  }.} {12}..# T
0400: 65 73 74 20 74 68 61 74 20 62 6f 74 68 20 63 61  est that both ca
0410: 73 65 20 73 65 6e 73 69 74 69 76 65 20 61 6e 64  se sensitive and
0420: 20 69 6e 73 65 6e 73 69 74 69 76 65 20 76 65 72   insensitive ver
0430: 73 69 6f 6e 20 6f 66 20 4c 49 4b 45 20 77 6f 72  sion of LIKE wor
0440: 6b 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69 6b  k..#.do_test lik
0450: 65 2d 31 2e 31 20 7b 0a 20 20 65 78 65 63 73 71  e-1.1 {.  execsq
0460: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78  l {.    SELECT x
0470: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 78   FROM t1 WHERE x
0480: 20 4c 49 4b 45 20 27 61 62 63 27 20 4f 52 44 45   LIKE 'abc' ORDE
0490: 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b 41  R BY 1;.  }.} {A
04a0: 42 43 20 61 62 63 7d 0a 64 6f 5f 74 65 73 74 20  BC abc}.do_test 
04b0: 6c 69 6b 65 2d 31 2e 32 20 7b 0a 20 20 65 78 65  like-1.2 {.  exe
04c0: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
04d0: 54 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52  T x FROM t1 WHER
04e0: 45 20 78 20 47 4c 4f 42 20 27 61 62 63 27 20 4f  E x GLOB 'abc' O
04f0: 52 44 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d  RDER BY 1;.  }.}
0500: 20 7b 61 62 63 7d 0a 64 6f 5f 74 65 73 74 20 6c   {abc}.do_test l
0510: 69 6b 65 2d 31 2e 33 20 7b 0a 20 20 65 78 65 63  ike-1.3 {.  exec
0520: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
0530: 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   x FROM t1 WHERE
0540: 20 78 20 4c 49 4b 45 20 27 41 42 43 27 20 4f 52   x LIKE 'ABC' OR
0550: 44 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d 20  DER BY 1;.  }.} 
0560: 7b 41 42 43 20 61 62 63 7d 0a 64 6f 5f 74 65 73  {ABC abc}.do_tes
0570: 74 20 6c 69 6b 65 2d 31 2e 34 20 7b 0a 20 20 65  t like-1.4 {.  e
0580: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
0590: 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57 48  ECT x FROM t1 WH
05a0: 45 52 45 20 78 20 4c 49 4b 45 20 27 61 42 63 27  ERE x LIKE 'aBc'
05b0: 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20 20 7d   ORDER BY 1;.  }
05c0: 0a 7d 20 7b 41 42 43 20 61 62 63 7d 0a 64 6f 5f  .} {ABC abc}.do_
05d0: 74 65 73 74 20 6c 69 6b 65 2d 31 2e 35 20 7b 0a  test like-1.5 {.
05e0: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
05f0: 50 52 41 47 4d 41 20 63 61 73 65 5f 73 65 6e 73  PRAGMA case_sens
0600: 69 74 69 76 65 5f 6c 69 6b 65 3d 6f 6e 3b 0a 20  itive_like=on;. 
0610: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
0620: 20 74 31 20 57 48 45 52 45 20 78 20 4c 49 4b 45   t1 WHERE x LIKE
0630: 20 27 61 62 63 27 20 4f 52 44 45 52 20 42 59 20   'abc' ORDER BY 
0640: 31 3b 0a 20 20 7d 0a 7d 20 7b 61 62 63 7d 0a 64  1;.  }.} {abc}.d
0650: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 31 2e 36 20  o_test like-1.6 
0660: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
0670: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
0680: 74 31 20 57 48 45 52 45 20 78 20 47 4c 4f 42 20  t1 WHERE x GLOB 
0690: 27 61 62 63 27 20 4f 52 44 45 52 20 42 59 20 31  'abc' ORDER BY 1
06a0: 3b 0a 20 20 7d 0a 7d 20 7b 61 62 63 7d 0a 64 6f  ;.  }.} {abc}.do
06b0: 5f 74 65 73 74 20 6c 69 6b 65 2d 31 2e 37 20 7b  _test like-1.7 {
06c0: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
06d0: 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74   SELECT x FROM t
06e0: 31 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27  1 WHERE x LIKE '
06f0: 41 42 43 27 20 4f 52 44 45 52 20 42 59 20 31 3b  ABC' ORDER BY 1;
0700: 0a 20 20 7d 0a 7d 20 7b 41 42 43 7d 0a 64 6f 5f  .  }.} {ABC}.do_
0710: 74 65 73 74 20 6c 69 6b 65 2d 31 2e 38 20 7b 0a  test like-1.8 {.
0720: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
0730: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31  SELECT x FROM t1
0740: 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61   WHERE x LIKE 'a
0750: 42 63 27 20 4f 52 44 45 52 20 42 59 20 31 3b 0a  Bc' ORDER BY 1;.
0760: 20 20 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73 74    }.} {}.do_test
0770: 20 6c 69 6b 65 2d 31 2e 39 20 7b 0a 20 20 65 78   like-1.9 {.  ex
0780: 65 63 73 71 6c 20 7b 0a 20 20 20 20 50 52 41 47  ecsql {.    PRAG
0790: 4d 41 20 63 61 73 65 5f 73 65 6e 73 69 74 69 76  MA case_sensitiv
07a0: 65 5f 6c 69 6b 65 3d 6f 66 66 3b 0a 20 20 20 20  e_like=off;.    
07b0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31  SELECT x FROM t1
07c0: 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61   WHERE x LIKE 'a
07d0: 62 63 27 20 4f 52 44 45 52 20 42 59 20 31 3b 0a  bc' ORDER BY 1;.
07e0: 20 20 7d 0a 7d 20 7b 41 42 43 20 61 62 63 7d 0a    }.} {ABC abc}.
07f0: 0a 23 20 54 65 73 74 73 20 6f 66 20 74 68 65 20  .# Tests of the 
0800: 52 45 47 45 58 50 20 6f 70 65 72 61 74 6f 72 0a  REGEXP operator.
0810: 23 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 32  #.do_test like-2
0820: 2e 31 20 7b 0a 20 20 70 72 6f 63 20 74 65 73 74  .1 {.  proc test
0830: 5f 72 65 67 65 78 70 20 7b 61 20 62 7d 20 7b 0a  _regexp {a b} {.
0840: 20 20 20 20 72 65 74 75 72 6e 20 5b 72 65 67 65      return [rege
0850: 78 70 20 24 61 20 24 62 5d 0a 20 20 7d 0a 20 20  xp $a $b].  }.  
0860: 64 62 20 66 75 6e 63 74 69 6f 6e 20 72 65 67 65  db function rege
0870: 78 70 20 74 65 73 74 5f 72 65 67 65 78 70 0a 20  xp test_regexp. 
0880: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
0890: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20  ELECT x FROM t1 
08a0: 57 48 45 52 45 20 78 20 52 45 47 45 58 50 20 27  WHERE x REGEXP '
08b0: 61 62 63 27 20 4f 52 44 45 52 20 42 59 20 31 3b  abc' ORDER BY 1;
08c0: 0a 20 20 7d 0a 7d 20 7b 7b 41 42 43 20 61 62 63  .  }.} {{ABC abc
08d0: 20 78 79 7a 7d 20 61 62 63 20 61 62 63 64 7d 0a   xyz} abc abcd}.
08e0: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 32 2e 32  do_test like-2.2
08f0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
0900: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
0910: 20 74 31 20 57 48 45 52 45 20 78 20 52 45 47 45   t1 WHERE x REGE
0920: 58 50 20 27 5e 61 62 63 27 20 4f 52 44 45 52 20  XP '^abc' ORDER 
0930: 42 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b 61 62 63  BY 1;.  }.} {abc
0940: 20 61 62 63 64 7d 0a 0a 23 20 54 65 73 74 73 20   abcd}..# Tests 
0950: 6f 66 20 74 68 65 20 4d 41 54 43 48 20 6f 70 65  of the MATCH ope
0960: 72 61 74 6f 72 0a 23 0a 64 6f 5f 74 65 73 74 20  rator.#.do_test 
0970: 6c 69 6b 65 2d 32 2e 33 20 7b 0a 20 20 70 72 6f  like-2.3 {.  pro
0980: 63 20 74 65 73 74 5f 6d 61 74 63 68 20 7b 61 20  c test_match {a 
0990: 62 7d 20 7b 0a 20 20 20 20 72 65 74 75 72 6e 20  b} {.    return 
09a0: 5b 73 74 72 69 6e 67 20 6d 61 74 63 68 20 24 61  [string match $a
09b0: 20 24 62 5d 0a 20 20 7d 0a 20 20 64 62 20 66 75   $b].  }.  db fu
09c0: 6e 63 74 69 6f 6e 20 6d 61 74 63 68 20 74 65 73  nction match tes
09d0: 74 5f 6d 61 74 63 68 0a 20 20 65 78 65 63 73 71  t_match.  execsq
09e0: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78  l {.    SELECT x
09f0: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 78   FROM t1 WHERE x
0a00: 20 4d 41 54 43 48 20 27 2a 61 62 63 2a 27 20 4f   MATCH '*abc*' O
0a10: 52 44 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d  RDER BY 1;.  }.}
0a20: 20 7b 7b 41 42 43 20 61 62 63 20 78 79 7a 7d 20   {{ABC abc xyz} 
0a30: 61 62 63 20 61 62 63 64 7d 0a 64 6f 5f 74 65 73  abc abcd}.do_tes
0a40: 74 20 6c 69 6b 65 2d 32 2e 34 20 7b 0a 20 20 65  t like-2.4 {.  e
0a50: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
0a60: 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57 48  ECT x FROM t1 WH
0a70: 45 52 45 20 78 20 4d 41 54 43 48 20 27 61 62 63  ERE x MATCH 'abc
0a80: 2a 27 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20  *' ORDER BY 1;. 
0a90: 20 7d 0a 7d 20 7b 61 62 63 20 61 62 63 64 7d 0a   }.} {abc abcd}.
0aa0: 0a 23 20 46 6f 72 20 74 68 65 20 72 65 6d 61 69  .# For the remai
0ab0: 6e 69 6e 67 20 74 65 73 74 73 2c 20 77 65 20 6e  ning tests, we n
0ac0: 65 65 64 20 74 6f 20 68 61 76 65 20 74 68 65 20  eed to have the 
0ad0: 6c 69 6b 65 20 6f 70 74 69 6d 69 7a 61 74 69 6f  like optimizatio
0ae0: 6e 73 0a 23 20 65 6e 61 62 6c 65 64 2e 0a 23 0a  ns.# enabled..#.
0af0: 69 66 63 61 70 61 62 6c 65 20 21 6c 69 6b 65 5f  ifcapable !like_
0b00: 6f 70 74 20 7b 0a 20 20 66 69 6e 69 73 68 5f 74  opt {.  finish_t
0b10: 65 73 74 0a 20 20 72 65 74 75 72 6e 0a 7d 20 0a  est.  return.} .
0b20: 0a 23 20 54 68 69 73 20 70 72 6f 63 65 64 75 72  .# This procedur
0b30: 65 20 65 78 65 63 75 74 65 73 20 74 68 65 20 53  e executes the S
0b40: 51 4c 2e 20 20 54 68 65 6e 20 69 74 20 61 70 70  QL.  Then it app
0b50: 65 6e 64 73 20 74 6f 20 74 68 65 20 72 65 73 75  ends to the resu
0b60: 6c 74 20 74 68 65 0a 23 20 22 73 6f 72 74 22 20  lt the.# "sort" 
0b70: 6f 72 20 22 6e 6f 73 6f 72 74 22 20 6b 65 79 77  or "nosort" keyw
0b80: 6f 72 64 20 28 61 73 20 69 6e 20 74 68 65 20 63  ord (as in the c
0b90: 6b 73 6f 72 74 20 70 72 6f 63 65 64 75 72 65 20  ksort procedure 
0ba0: 61 62 6f 76 65 29 20 74 68 65 6e 0a 23 20 69 74  above) then.# it
0bb0: 20 61 70 70 65 6e 64 73 20 74 68 65 20 3a 3a 73   appends the ::s
0bc0: 71 6c 69 74 65 5f 71 75 65 72 79 5f 70 6c 61 6e  qlite_query_plan
0bd0: 20 76 61 72 69 61 62 6c 65 2e 0a 23 0a 70 72 6f   variable..#.pro
0be0: 63 20 71 75 65 72 79 70 6c 61 6e 20 7b 73 71 6c  c queryplan {sql
0bf0: 7d 20 7b 0a 20 20 73 65 74 20 3a 3a 73 71 6c 69  } {.  set ::sqli
0c00: 74 65 5f 73 6f 72 74 5f 63 6f 75 6e 74 20 30 0a  te_sort_count 0.
0c10: 20 20 73 65 74 20 64 61 74 61 20 5b 65 78 65 63    set data [exec
0c20: 73 71 6c 20 24 73 71 6c 5d 0a 20 20 69 66 20 7b  sql $sql].  if {
0c30: 24 3a 3a 73 71 6c 69 74 65 5f 73 6f 72 74 5f 63  $::sqlite_sort_c
0c40: 6f 75 6e 74 7d 20 7b 73 65 74 20 78 20 73 6f 72  ount} {set x sor
0c50: 74 7d 20 7b 73 65 74 20 78 20 6e 6f 73 6f 72 74  t} {set x nosort
0c60: 7d 0a 20 20 6c 61 70 70 65 6e 64 20 64 61 74 61  }.  lappend data
0c70: 20 24 78 0a 20 20 72 65 74 75 72 6e 20 5b 63 6f   $x.  return [co
0c80: 6e 63 61 74 20 24 64 61 74 61 20 24 3a 3a 73 71  ncat $data $::sq
0c90: 6c 69 74 65 5f 71 75 65 72 79 5f 70 6c 61 6e 5d  lite_query_plan]
0ca0: 0a 7d 0a 0a 23 20 50 65 72 66 6f 72 6d 20 74 65  .}..# Perform te
0cb0: 73 74 73 20 6f 6e 20 74 68 65 20 6c 69 6b 65 20  sts on the like 
0cc0: 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 2e 0a 23 0a  optimization..#.
0cd0: 23 20 57 69 74 68 20 6e 6f 20 69 6e 64 65 78 20  # With no index 
0ce0: 6f 6e 20 74 31 2e 78 20 61 6e 64 20 77 69 74 68  on t1.x and with
0cf0: 20 63 61 73 65 20 73 65 6e 73 69 74 69 76 69 74   case sensitivit
0d00: 79 20 74 75 72 6e 65 64 20 6f 66 66 2c 20 6e 6f  y turned off, no
0d10: 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 0a 23 20   optimization.# 
0d20: 69 73 20 70 65 72 66 6f 72 6d 65 64 2e 0a 23 0a  is performed..#.
0d30: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31  do_test like-3.1
0d40: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
0d50: 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71  like_count 0.  q
0d60: 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53  ueryplan {.    S
0d70: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20  ELECT x FROM t1 
0d80: 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61 62  WHERE x LIKE 'ab
0d90: 63 25 27 20 4f 52 44 45 52 20 42 59 20 31 3b 0a  c%' ORDER BY 1;.
0da0: 20 20 7d 0a 7d 20 7b 41 42 43 20 7b 41 42 43 20    }.} {ABC {ABC 
0db0: 61 62 63 20 78 79 7a 7d 20 61 62 63 20 61 62 63  abc xyz} abc abc
0dc0: 64 20 73 6f 72 74 20 74 31 20 7b 7d 7d 0a 64 6f  d sort t1 {}}.do
0dd0: 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 32 20 7b  _test like-3.2 {
0de0: 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69  .  set sqlite_li
0df0: 6b 65 5f 63 6f 75 6e 74 0a 7d 20 7b 31 32 7d 0a  ke_count.} {12}.
0e00: 0a 23 20 57 69 74 68 20 61 6e 20 69 6e 64 65 78  .# With an index
0e10: 20 6f 6e 20 74 31 2e 78 20 61 6e 64 20 63 61 73   on t1.x and cas
0e20: 65 20 73 65 6e 73 69 74 69 76 69 74 79 20 6f 6e  e sensitivity on
0e30: 2c 20 6f 70 74 69 6d 69 7a 65 20 63 6f 6d 70 6c  , optimize compl
0e40: 65 74 65 6c 79 2e 0a 23 0a 64 6f 5f 74 65 73 74  etely..#.do_test
0e50: 20 6c 69 6b 65 2d 33 2e 33 20 7b 0a 20 20 73 65   like-3.3 {.  se
0e60: 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f  t sqlite_like_co
0e70: 75 6e 74 20 30 0a 20 20 65 78 65 63 73 71 6c 20  unt 0.  execsql 
0e80: 7b 0a 20 20 20 20 50 52 41 47 4d 41 20 63 61 73  {.    PRAGMA cas
0e90: 65 5f 73 65 6e 73 69 74 69 76 65 5f 6c 69 6b 65  e_sensitive_like
0ea0: 3d 6f 6e 3b 0a 20 20 20 20 43 52 45 41 54 45 20  =on;.    CREATE 
0eb0: 49 4e 44 45 58 20 69 31 20 4f 4e 20 74 31 28 78  INDEX i1 ON t1(x
0ec0: 29 3b 0a 20 20 7d 0a 20 20 71 75 65 72 79 70 6c  );.  }.  querypl
0ed0: 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  an {.    SELECT 
0ee0: 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  x FROM t1 WHERE 
0ef0: 78 20 4c 49 4b 45 20 27 61 62 63 25 27 20 4f 52  x LIKE 'abc%' OR
0f00: 44 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d 20  DER BY 1;.  }.} 
0f10: 7b 61 62 63 20 61 62 63 64 20 6e 6f 73 6f 72 74  {abc abcd nosort
0f20: 20 7b 7d 20 69 31 7d 0a 64 6f 5f 74 65 73 74 20   {} i1}.do_test 
0f30: 6c 69 6b 65 2d 33 2e 34 20 7b 0a 20 20 73 65 74  like-3.4 {.  set
0f40: 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75   sqlite_like_cou
0f50: 6e 74 0a 7d 20 30 0a 0a 23 20 50 61 72 74 69 61  nt.} 0..# Partia
0f60: 6c 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 20 77  l optimization w
0f70: 68 65 6e 20 74 68 65 20 70 61 74 74 65 72 6e 20  hen the pattern 
0f80: 64 6f 65 73 20 6e 6f 74 20 65 6e 64 20 69 6e 20  does not end in 
0f90: 27 25 27 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69  '%'.#.do_test li
0fa0: 6b 65 2d 33 2e 35 20 7b 0a 20 20 73 65 74 20 73  ke-3.5 {.  set s
0fb0: 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74  qlite_like_count
0fc0: 20 30 0a 20 20 71 75 65 72 79 70 6c 61 6e 20 7b   0.  queryplan {
0fd0: 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20 46 52  .    SELECT x FR
0fe0: 4f 4d 20 74 31 20 57 48 45 52 45 20 78 20 4c 49  OM t1 WHERE x LI
0ff0: 4b 45 20 27 61 5f 63 27 20 4f 52 44 45 52 20 42  KE 'a_c' ORDER B
1000: 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b 61 62 63 20  Y 1;.  }.} {abc 
1010: 6e 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64 6f  nosort {} i1}.do
1020: 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 36 20 7b  _test like-3.6 {
1030: 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69  .  set sqlite_li
1040: 6b 65 5f 63 6f 75 6e 74 0a 7d 20 36 0a 64 6f 5f  ke_count.} 6.do_
1050: 74 65 73 74 20 6c 69 6b 65 2d 33 2e 37 20 7b 0a  test like-3.7 {.
1060: 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b    set sqlite_lik
1070: 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71 75 65 72  e_count 0.  quer
1080: 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45  yplan {.    SELE
1090: 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45  CT x FROM t1 WHE
10a0: 52 45 20 78 20 4c 49 4b 45 20 27 61 62 25 64 27  RE x LIKE 'ab%d'
10b0: 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20 20 7d   ORDER BY 1;.  }
10c0: 0a 7d 20 7b 61 62 63 64 20 61 62 64 20 6e 6f 73  .} {abcd abd nos
10d0: 6f 72 74 20 7b 7d 20 69 31 7d 0a 64 6f 5f 74 65  ort {} i1}.do_te
10e0: 73 74 20 6c 69 6b 65 2d 33 2e 38 20 7b 0a 20 20  st like-3.8 {.  
10f0: 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f  set sqlite_like_
1100: 63 6f 75 6e 74 0a 7d 20 34 0a 64 6f 5f 74 65 73  count.} 4.do_tes
1110: 74 20 6c 69 6b 65 2d 33 2e 39 20 7b 0a 20 20 73  t like-3.9 {.  s
1120: 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63  et sqlite_like_c
1130: 6f 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70 6c  ount 0.  querypl
1140: 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  an {.    SELECT 
1150: 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  x FROM t1 WHERE 
1160: 78 20 4c 49 4b 45 20 27 61 5f 63 25 27 20 4f 52  x LIKE 'a_c%' OR
1170: 44 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d 20  DER BY 1;.  }.} 
1180: 7b 61 62 63 20 61 62 63 64 20 6e 6f 73 6f 72 74  {abc abcd nosort
1190: 20 7b 7d 20 69 31 7d 0a 64 6f 5f 74 65 73 74 20   {} i1}.do_test 
11a0: 6c 69 6b 65 2d 33 2e 31 30 20 7b 0a 20 20 73 65  like-3.10 {.  se
11b0: 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f  t sqlite_like_co
11c0: 75 6e 74 0a 7d 20 36 0a 0a 23 20 4e 6f 20 6f 70  unt.} 6..# No op
11d0: 74 69 6d 69 7a 61 74 69 6f 6e 20 77 68 65 6e 20  timization when 
11e0: 74 68 65 20 70 61 74 74 65 72 6e 20 62 65 67 69  the pattern begi
11f0: 6e 73 20 77 69 74 68 20 61 20 77 69 6c 64 63 61  ns with a wildca
1200: 72 64 2e 0a 23 20 4e 6f 74 65 20 74 68 61 74 20  rd..# Note that 
1210: 74 68 65 20 69 6e 64 65 78 20 69 73 20 73 74 69  the index is sti
1220: 6c 6c 20 75 73 65 64 20 62 75 74 20 6f 6e 6c 79  ll used but only
1230: 20 66 6f 72 20 73 6f 72 74 69 6e 67 2e 0a 23 0a   for sorting..#.
1240: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31  do_test like-3.1
1250: 31 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65  1 {.  set sqlite
1260: 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20  _like_count 0.  
1270: 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20  queryplan {.    
1280: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31  SELECT x FROM t1
1290: 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 25   WHERE x LIKE '%
12a0: 62 63 64 27 20 4f 52 44 45 52 20 42 59 20 31 3b  bcd' ORDER BY 1;
12b0: 0a 20 20 7d 0a 7d 20 7b 61 62 63 64 20 62 63 64  .  }.} {abcd bcd
12c0: 20 6e 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64   nosort {} i1}.d
12d0: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31 32  o_test like-3.12
12e0: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
12f0: 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d 20 31 32 0a  like_count.} 12.
1300: 0a 23 20 4e 6f 20 6f 70 74 69 6d 69 7a 61 74 69  .# No optimizati
1310: 6f 6e 20 66 6f 72 20 63 61 73 65 20 69 6e 73 65  on for case inse
1320: 6e 73 69 74 69 76 65 20 4c 49 4b 45 0a 23 0a 64  nsitive LIKE.#.d
1330: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31 33  o_test like-3.13
1340: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
1350: 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71  like_count 0.  q
1360: 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 50  ueryplan {.    P
1370: 52 41 47 4d 41 20 63 61 73 65 5f 73 65 6e 73 69  RAGMA case_sensi
1380: 74 69 76 65 5f 6c 69 6b 65 3d 6f 66 66 3b 0a 20  tive_like=off;. 
1390: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
13a0: 20 74 31 20 57 48 45 52 45 20 78 20 4c 49 4b 45   t1 WHERE x LIKE
13b0: 20 27 61 62 63 25 27 20 4f 52 44 45 52 20 42 59   'abc%' ORDER BY
13c0: 20 31 3b 0a 20 20 7d 0a 7d 20 7b 41 42 43 20 7b   1;.  }.} {ABC {
13d0: 41 42 43 20 61 62 63 20 78 79 7a 7d 20 61 62 63  ABC abc xyz} abc
13e0: 20 61 62 63 64 20 6e 6f 73 6f 72 74 20 7b 7d 20   abcd nosort {} 
13f0: 69 31 7d 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65  i1}.do_test like
1400: 2d 33 2e 31 34 20 7b 0a 20 20 73 65 74 20 73 71  -3.14 {.  set sq
1410: 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 0a  lite_like_count.
1420: 7d 20 31 32 0a 0a 23 20 4e 6f 20 6f 70 74 69 6d  } 12..# No optim
1430: 69 7a 61 74 69 6f 6e 20 77 69 74 68 6f 75 74 20  ization without 
1440: 61 6e 20 69 6e 64 65 78 2e 0a 23 0a 64 6f 5f 74  an index..#.do_t
1450: 65 73 74 20 6c 69 6b 65 2d 33 2e 31 35 20 7b 0a  est like-3.15 {.
1460: 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b    set sqlite_lik
1470: 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71 75 65 72  e_count 0.  quer
1480: 79 70 6c 61 6e 20 7b 0a 20 20 20 20 50 52 41 47  yplan {.    PRAG
1490: 4d 41 20 63 61 73 65 5f 73 65 6e 73 69 74 69 76  MA case_sensitiv
14a0: 65 5f 6c 69 6b 65 3d 6f 6e 3b 0a 20 20 20 20 44  e_like=on;.    D
14b0: 52 4f 50 20 49 4e 44 45 58 20 69 31 3b 0a 20 20  ROP INDEX i1;.  
14c0: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
14d0: 74 31 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  t1 WHERE x LIKE 
14e0: 27 61 62 63 25 27 20 4f 52 44 45 52 20 42 59 20  'abc%' ORDER BY 
14f0: 31 3b 0a 20 20 7d 0a 7d 20 7b 61 62 63 20 61 62  1;.  }.} {abc ab
1500: 63 64 20 73 6f 72 74 20 74 31 20 7b 7d 7d 0a 64  cd sort t1 {}}.d
1510: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31 36  o_test like-3.16
1520: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
1530: 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d 20 31 32 0a  like_count.} 12.
1540: 0a 23 20 4e 6f 20 47 4c 4f 42 20 6f 70 74 69 6d  .# No GLOB optim
1550: 69 7a 61 74 69 6f 6e 20 77 69 74 68 6f 75 74 20  ization without 
1560: 61 6e 20 69 6e 64 65 78 2e 0a 23 0a 64 6f 5f 74  an index..#.do_t
1570: 65 73 74 20 6c 69 6b 65 2d 33 2e 31 37 20 7b 0a  est like-3.17 {.
1580: 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b    set sqlite_lik
1590: 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71 75 65 72  e_count 0.  quer
15a0: 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45  yplan {.    SELE
15b0: 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45  CT x FROM t1 WHE
15c0: 52 45 20 78 20 47 4c 4f 42 20 27 61 62 63 2a 27  RE x GLOB 'abc*'
15d0: 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20 20 7d   ORDER BY 1;.  }
15e0: 0a 7d 20 7b 61 62 63 20 61 62 63 64 20 73 6f 72  .} {abc abcd sor
15f0: 74 20 74 31 20 7b 7d 7d 0a 64 6f 5f 74 65 73 74  t t1 {}}.do_test
1600: 20 6c 69 6b 65 2d 33 2e 31 38 20 7b 0a 20 20 73   like-3.18 {.  s
1610: 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63  et sqlite_like_c
1620: 6f 75 6e 74 0a 7d 20 31 32 0a 0a 23 20 47 4c 4f  ount.} 12..# GLO
1630: 42 20 69 73 20 6f 70 74 69 6d 69 7a 65 64 20 72  B is optimized r
1640: 65 67 61 72 64 6c 65 73 73 20 6f 66 20 74 68 65  egardless of the
1650: 20 63 61 73 65 5f 73 65 6e 73 69 74 69 76 65 5f   case_sensitive_
1660: 6c 69 6b 65 20 73 65 74 74 69 6e 67 2e 0a 23 0a  like setting..#.
1670: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 31  do_test like-3.1
1680: 39 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65  9 {.  set sqlite
1690: 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20  _like_count 0.  
16a0: 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20  queryplan {.    
16b0: 43 52 45 41 54 45 20 49 4e 44 45 58 20 69 31 20  CREATE INDEX i1 
16c0: 4f 4e 20 74 31 28 78 29 3b 0a 20 20 20 20 53 45  ON t1(x);.    SE
16d0: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57  LECT x FROM t1 W
16e0: 48 45 52 45 20 78 20 47 4c 4f 42 20 27 61 62 63  HERE x GLOB 'abc
16f0: 2a 27 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20  *' ORDER BY 1;. 
1700: 20 7d 0a 7d 20 7b 61 62 63 20 61 62 63 64 20 6e   }.} {abc abcd n
1710: 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64 6f 5f  osort {} i1}.do_
1720: 74 65 73 74 20 6c 69 6b 65 2d 33 2e 32 30 20 7b  test like-3.20 {
1730: 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69  .  set sqlite_li
1740: 6b 65 5f 63 6f 75 6e 74 0a 7d 20 30 0a 64 6f 5f  ke_count.} 0.do_
1750: 74 65 73 74 20 6c 69 6b 65 2d 33 2e 32 31 20 7b  test like-3.21 {
1760: 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69  .  set sqlite_li
1770: 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71 75 65  ke_count 0.  que
1780: 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 50 52 41  ryplan {.    PRA
1790: 47 4d 41 20 63 61 73 65 5f 73 65 6e 73 69 74 69  GMA case_sensiti
17a0: 76 65 5f 6c 69 6b 65 3d 6f 6e 3b 0a 20 20 20 20  ve_like=on;.    
17b0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31  SELECT x FROM t1
17c0: 20 57 48 45 52 45 20 78 20 47 4c 4f 42 20 27 61   WHERE x GLOB 'a
17d0: 62 63 2a 27 20 4f 52 44 45 52 20 42 59 20 31 3b  bc*' ORDER BY 1;
17e0: 0a 20 20 7d 0a 7d 20 7b 61 62 63 20 61 62 63 64  .  }.} {abc abcd
17f0: 20 6e 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64   nosort {} i1}.d
1800: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 32 32  o_test like-3.22
1810: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
1820: 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d 20 30 0a 64  like_count.} 0.d
1830: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 33 2e 32 33  o_test like-3.23
1840: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
1850: 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71  like_count 0.  q
1860: 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 50  ueryplan {.    P
1870: 52 41 47 4d 41 20 63 61 73 65 5f 73 65 6e 73 69  RAGMA case_sensi
1880: 74 69 76 65 5f 6c 69 6b 65 3d 6f 66 66 3b 0a 20  tive_like=off;. 
1890: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
18a0: 20 74 31 20 57 48 45 52 45 20 78 20 47 4c 4f 42   t1 WHERE x GLOB
18b0: 20 27 61 5b 62 63 5d 64 27 20 4f 52 44 45 52 20   'a[bc]d' ORDER 
18c0: 42 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b 61 62 64  BY 1;.  }.} {abd
18d0: 20 61 63 64 20 6e 6f 73 6f 72 74 20 7b 7d 20 69   acd nosort {} i
18e0: 31 7d 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d  1}.do_test like-
18f0: 33 2e 32 34 20 7b 0a 20 20 73 65 74 20 73 71 6c  3.24 {.  set sql
1900: 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d  ite_like_count.}
1910: 20 36 0a 0a 23 20 4e 6f 20 6f 70 74 69 6d 69 7a   6..# No optimiz
1920: 61 74 69 6f 6e 20 69 66 20 74 68 65 20 4c 48 53  ation if the LHS
1930: 20 6f 66 20 74 68 65 20 4c 49 4b 45 20 69 73 20   of the LIKE is 
1940: 6e 6f 74 20 61 20 63 6f 6c 75 6d 6e 20 6e 61 6d  not a column nam
1950: 65 20 6f 72 0a 23 20 69 66 20 74 68 65 20 52 48  e or.# if the RH
1960: 53 20 69 73 20 6e 6f 74 20 61 20 73 74 72 69 6e  S is not a strin
1970: 67 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69 6b  g..#.do_test lik
1980: 65 2d 34 2e 31 20 7b 0a 20 20 65 78 65 63 73 71  e-4.1 {.  execsq
1990: 6c 20 7b 50 52 41 47 4d 41 20 63 61 73 65 5f 73  l {PRAGMA case_s
19a0: 65 6e 73 69 74 69 76 65 5f 6c 69 6b 65 3d 6f 6e  ensitive_like=on
19b0: 7d 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c  }.  set sqlite_l
19c0: 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20 71 75  ike_count 0.  qu
19d0: 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45  eryplan {.    SE
19e0: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31 20 57  LECT x FROM t1 W
19f0: 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61 62 63  HERE x LIKE 'abc
1a00: 25 27 20 4f 52 44 45 52 20 42 59 20 31 0a 20 20  %' ORDER BY 1.  
1a10: 7d 0a 7d 20 7b 61 62 63 20 61 62 63 64 20 6e 6f  }.} {abc abcd no
1a20: 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64 6f 5f 74  sort {} i1}.do_t
1a30: 65 73 74 20 6c 69 6b 65 2d 34 2e 32 20 7b 0a 20  est like-4.2 {. 
1a40: 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65   set sqlite_like
1a50: 5f 63 6f 75 6e 74 0a 7d 20 30 0a 64 6f 5f 74 65  _count.} 0.do_te
1a60: 73 74 20 6c 69 6b 65 2d 34 2e 33 20 7b 0a 20 20  st like-4.3 {.  
1a70: 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f  set sqlite_like_
1a80: 63 6f 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70  count 0.  queryp
1a90: 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  lan {.    SELECT
1aa0: 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   x FROM t1 WHERE
1ab0: 20 2b 78 20 4c 49 4b 45 20 27 61 62 63 25 27 20   +x LIKE 'abc%' 
1ac0: 4f 52 44 45 52 20 42 59 20 31 0a 20 20 7d 0a 7d  ORDER BY 1.  }.}
1ad0: 20 7b 61 62 63 20 61 62 63 64 20 6e 6f 73 6f 72   {abc abcd nosor
1ae0: 74 20 7b 7d 20 69 31 7d 0a 64 6f 5f 74 65 73 74  t {} i1}.do_test
1af0: 20 6c 69 6b 65 2d 34 2e 34 20 7b 0a 20 20 73 65   like-4.4 {.  se
1b00: 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f  t sqlite_like_co
1b10: 75 6e 74 0a 7d 20 31 32 0a 64 6f 5f 74 65 73 74  unt.} 12.do_test
1b20: 20 6c 69 6b 65 2d 34 2e 35 20 7b 0a 20 20 73 65   like-4.5 {.  se
1b30: 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f  t sqlite_like_co
1b40: 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70 6c 61  unt 0.  querypla
1b50: 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78  n {.    SELECT x
1b60: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 78   FROM t1 WHERE x
1b70: 20 4c 49 4b 45 20 28 27 61 62 27 20 7c 7c 20 27   LIKE ('ab' || '
1b80: 63 25 27 29 20 4f 52 44 45 52 20 42 59 20 31 0a  c%') ORDER BY 1.
1b90: 20 20 7d 0a 7d 20 7b 61 62 63 20 61 62 63 64 20    }.} {abc abcd 
1ba0: 6e 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a 64 6f  nosort {} i1}.do
1bb0: 5f 74 65 73 74 20 6c 69 6b 65 2d 34 2e 36 20 7b  _test like-4.6 {
1bc0: 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c 69  .  set sqlite_li
1bd0: 6b 65 5f 63 6f 75 6e 74 0a 7d 20 31 32 0a 0a 23  ke_count.} 12..#
1be0: 20 43 6f 6c 6c 61 74 69 6e 67 20 73 65 71 75 65   Collating seque
1bf0: 6e 63 65 73 20 6f 6e 20 74 68 65 20 69 6e 64 65  nces on the inde
1c00: 78 20 64 69 73 61 62 6c 65 20 74 68 65 20 4c 49  x disable the LI
1c10: 4b 45 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 2e  KE optimization.
1c20: 0a 23 20 4f 72 20 69 66 20 74 68 65 20 4e 4f 43  .# Or if the NOC
1c30: 41 53 45 20 63 6f 6c 6c 61 74 69 6e 67 20 73 65  ASE collating se
1c40: 71 75 65 6e 63 65 20 69 73 20 75 73 65 64 2c 20  quence is used, 
1c50: 74 68 65 20 4c 49 4b 45 20 6f 70 74 69 6d 69 7a  the LIKE optimiz
1c60: 61 74 69 6f 6e 0a 23 20 69 73 20 65 6e 61 62 6c  ation.# is enabl
1c70: 65 64 20 77 68 65 6e 20 63 61 73 65 5f 73 65 6e  ed when case_sen
1c80: 73 69 74 69 76 65 5f 6c 69 6b 65 20 69 73 20 4f  sitive_like is O
1c90: 46 46 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69  FF..#.do_test li
1ca0: 6b 65 2d 35 2e 31 20 7b 0a 20 20 65 78 65 63 73  ke-5.1 {.  execs
1cb0: 71 6c 20 7b 50 52 41 47 4d 41 20 63 61 73 65 5f  ql {PRAGMA case_
1cc0: 73 65 6e 73 69 74 69 76 65 5f 6c 69 6b 65 3d 6f  sensitive_like=o
1cd0: 66 66 7d 0a 20 20 73 65 74 20 73 71 6c 69 74 65  ff}.  set sqlite
1ce0: 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20 30 0a 20 20  _like_count 0.  
1cf0: 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20  queryplan {.    
1d00: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 31  SELECT x FROM t1
1d10: 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61   WHERE x LIKE 'a
1d20: 62 63 25 27 20 4f 52 44 45 52 20 42 59 20 31 0a  bc%' ORDER BY 1.
1d30: 20 20 7d 0a 7d 20 7b 41 42 43 20 7b 41 42 43 20    }.} {ABC {ABC 
1d40: 61 62 63 20 78 79 7a 7d 20 61 62 63 20 61 62 63  abc xyz} abc abc
1d50: 64 20 6e 6f 73 6f 72 74 20 7b 7d 20 69 31 7d 0a  d nosort {} i1}.
1d60: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 35 2e 32  do_test like-5.2
1d70: 20 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f   {.  set sqlite_
1d80: 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d 20 31 32 0a  like_count.} 12.
1d90: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 35 2e 33  do_test like-5.3
1da0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
1db0: 20 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20     CREATE TABLE 
1dc0: 74 32 28 78 20 43 4f 4c 4c 41 54 45 20 4e 4f 43  t2(x COLLATE NOC
1dd0: 41 53 45 29 3b 0a 20 20 20 20 49 4e 53 45 52 54  ASE);.    INSERT
1de0: 20 49 4e 54 4f 20 74 32 20 53 45 4c 45 43 54 20   INTO t2 SELECT 
1df0: 2a 20 46 52 4f 4d 20 74 31 3b 0a 20 20 20 20 43  * FROM t1;.    C
1e00: 52 45 41 54 45 20 49 4e 44 45 58 20 69 32 20 4f  REATE INDEX i2 O
1e10: 4e 20 74 32 28 78 20 43 4f 4c 4c 41 54 45 20 4e  N t2(x COLLATE N
1e20: 4f 43 41 53 45 29 3b 0a 20 20 7d 0a 20 20 73 65  OCASE);.  }.  se
1e30: 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f  t sqlite_like_co
1e40: 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70 6c 61  unt 0.  querypla
1e50: 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78  n {.    SELECT x
1e60: 20 46 52 4f 4d 20 74 32 20 57 48 45 52 45 20 78   FROM t2 WHERE x
1e70: 20 4c 49 4b 45 20 27 61 62 63 25 27 20 4f 52 44   LIKE 'abc%' ORD
1e80: 45 52 20 42 59 20 31 0a 20 20 7d 0a 7d 20 7b 61  ER BY 1.  }.} {a
1e90: 62 63 20 41 42 43 20 7b 41 42 43 20 61 62 63 20  bc ABC {ABC abc 
1ea0: 78 79 7a 7d 20 61 62 63 64 20 6e 6f 73 6f 72 74  xyz} abcd nosort
1eb0: 20 7b 7d 20 69 32 7d 0a 64 6f 5f 74 65 73 74 20   {} i2}.do_test 
1ec0: 6c 69 6b 65 2d 35 2e 34 20 7b 0a 20 20 73 65 74  like-5.4 {.  set
1ed0: 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75   sqlite_like_cou
1ee0: 6e 74 0a 7d 20 30 0a 64 6f 5f 74 65 73 74 20 6c  nt.} 0.do_test l
1ef0: 69 6b 65 2d 35 2e 35 20 7b 0a 20 20 65 78 65 63  ike-5.5 {.  exec
1f00: 73 71 6c 20 7b 0a 20 20 20 20 50 52 41 47 4d 41  sql {.    PRAGMA
1f10: 20 63 61 73 65 5f 73 65 6e 73 69 74 69 76 65 5f   case_sensitive_
1f20: 6c 69 6b 65 3d 6f 6e 3b 0a 20 20 7d 0a 20 20 73  like=on;.  }.  s
1f30: 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63  et sqlite_like_c
1f40: 6f 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70 6c  ount 0.  querypl
1f50: 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  an {.    SELECT 
1f60: 78 20 46 52 4f 4d 20 74 32 20 57 48 45 52 45 20  x FROM t2 WHERE 
1f70: 78 20 4c 49 4b 45 20 27 61 62 63 25 27 20 4f 52  x LIKE 'abc%' OR
1f80: 44 45 52 20 42 59 20 31 0a 20 20 7d 0a 7d 20 7b  DER BY 1.  }.} {
1f90: 61 62 63 20 61 62 63 64 20 6e 6f 73 6f 72 74 20  abc abcd nosort 
1fa0: 7b 7d 20 69 32 7d 0a 64 6f 5f 74 65 73 74 20 6c  {} i2}.do_test l
1fb0: 69 6b 65 2d 35 2e 36 20 7b 0a 20 20 73 65 74 20  ike-5.6 {.  set 
1fc0: 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e  sqlite_like_coun
1fd0: 74 0a 7d 20 31 32 0a 64 6f 5f 74 65 73 74 20 6c  t.} 12.do_test l
1fe0: 69 6b 65 2d 35 2e 37 20 7b 0a 20 20 65 78 65 63  ike-5.7 {.  exec
1ff0: 73 71 6c 20 7b 0a 20 20 20 20 50 52 41 47 4d 41  sql {.    PRAGMA
2000: 20 63 61 73 65 5f 73 65 6e 73 69 74 69 76 65 5f   case_sensitive_
2010: 6c 69 6b 65 3d 6f 66 66 3b 0a 20 20 7d 0a 20 20  like=off;.  }.  
2020: 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f  set sqlite_like_
2030: 63 6f 75 6e 74 20 30 0a 20 20 71 75 65 72 79 70  count 0.  queryp
2040: 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  lan {.    SELECT
2050: 20 78 20 46 52 4f 4d 20 74 32 20 57 48 45 52 45   x FROM t2 WHERE
2060: 20 78 20 47 4c 4f 42 20 27 61 62 63 2a 27 20 4f   x GLOB 'abc*' O
2070: 52 44 45 52 20 42 59 20 31 0a 20 20 7d 0a 7d 20  RDER BY 1.  }.} 
2080: 7b 61 62 63 20 61 62 63 64 20 6e 6f 73 6f 72 74  {abc abcd nosort
2090: 20 7b 7d 20 69 32 7d 0a 64 6f 5f 74 65 73 74 20   {} i2}.do_test 
20a0: 6c 69 6b 65 2d 35 2e 38 20 7b 0a 20 20 73 65 74  like-5.8 {.  set
20b0: 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75   sqlite_like_cou
20c0: 6e 74 0a 7d 20 31 32 0a 64 6f 5f 74 65 73 74 20  nt.} 12.do_test 
20d0: 6c 69 6b 65 2d 35 2e 31 31 20 7b 0a 20 20 65 78  like-5.11 {.  ex
20e0: 65 63 73 71 6c 20 7b 50 52 41 47 4d 41 20 63 61  ecsql {PRAGMA ca
20f0: 73 65 5f 73 65 6e 73 69 74 69 76 65 5f 6c 69 6b  se_sensitive_lik
2100: 65 3d 6f 66 66 7d 0a 20 20 73 65 74 20 73 71 6c  e=off}.  set sql
2110: 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20 30  ite_like_count 0
2120: 0a 20 20 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20  .  queryplan {. 
2130: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
2140: 20 74 31 20 57 48 45 52 45 20 78 20 4c 49 4b 45   t1 WHERE x LIKE
2150: 20 27 41 42 43 25 27 20 4f 52 44 45 52 20 42 59   'ABC%' ORDER BY
2160: 20 31 0a 20 20 7d 0a 7d 20 7b 41 42 43 20 7b 41   1.  }.} {ABC {A
2170: 42 43 20 61 62 63 20 78 79 7a 7d 20 61 62 63 20  BC abc xyz} abc 
2180: 61 62 63 64 20 6e 6f 73 6f 72 74 20 7b 7d 20 69  abcd nosort {} i
2190: 31 7d 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d  1}.do_test like-
21a0: 35 2e 31 32 20 7b 0a 20 20 73 65 74 20 73 71 6c  5.12 {.  set sql
21b0: 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 0a 7d  ite_like_count.}
21c0: 20 31 32 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65   12.do_test like
21d0: 2d 35 2e 31 33 20 7b 0a 20 20 73 65 74 20 73 71  -5.13 {.  set sq
21e0: 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20  lite_like_count 
21f0: 30 0a 20 20 71 75 65 72 79 70 6c 61 6e 20 7b 0a  0.  queryplan {.
2200: 20 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f      SELECT x FRO
2210: 4d 20 74 32 20 57 48 45 52 45 20 78 20 4c 49 4b  M t2 WHERE x LIK
2220: 45 20 27 41 42 43 25 27 20 4f 52 44 45 52 20 42  E 'ABC%' ORDER B
2230: 59 20 31 0a 20 20 7d 0a 7d 20 7b 61 62 63 20 41  Y 1.  }.} {abc A
2240: 42 43 20 7b 41 42 43 20 61 62 63 20 78 79 7a 7d  BC {ABC abc xyz}
2250: 20 61 62 63 64 20 6e 6f 73 6f 72 74 20 7b 7d 20   abcd nosort {} 
2260: 69 32 7d 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65  i2}.do_test like
2270: 2d 35 2e 31 34 20 7b 0a 20 20 73 65 74 20 73 71  -5.14 {.  set sq
2280: 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 0a  lite_like_count.
2290: 7d 20 30 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65  } 0.do_test like
22a0: 2d 35 2e 31 35 20 7b 0a 20 20 65 78 65 63 73 71  -5.15 {.  execsq
22b0: 6c 20 7b 0a 20 20 20 20 50 52 41 47 4d 41 20 63  l {.    PRAGMA c
22c0: 61 73 65 5f 73 65 6e 73 69 74 69 76 65 5f 6c 69  ase_sensitive_li
22d0: 6b 65 3d 6f 6e 3b 0a 20 20 7d 0a 20 20 73 65 74  ke=on;.  }.  set
22e0: 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f 63 6f 75   sqlite_like_cou
22f0: 6e 74 20 30 0a 20 20 71 75 65 72 79 70 6c 61 6e  nt 0.  queryplan
2300: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20   {.    SELECT x 
2310: 46 52 4f 4d 20 74 32 20 57 48 45 52 45 20 78 20  FROM t2 WHERE x 
2320: 4c 49 4b 45 20 27 41 42 43 25 27 20 4f 52 44 45  LIKE 'ABC%' ORDE
2330: 52 20 42 59 20 31 0a 20 20 7d 0a 7d 20 7b 41 42  R BY 1.  }.} {AB
2340: 43 20 7b 41 42 43 20 61 62 63 20 78 79 7a 7d 20  C {ABC abc xyz} 
2350: 6e 6f 73 6f 72 74 20 7b 7d 20 69 32 7d 0a 64 6f  nosort {} i2}.do
2360: 5f 74 65 73 74 20 6c 69 6b 65 2d 35 2e 31 36 20  _test like-5.16 
2370: 7b 0a 20 20 73 65 74 20 73 71 6c 69 74 65 5f 6c  {.  set sqlite_l
2380: 69 6b 65 5f 63 6f 75 6e 74 0a 7d 20 31 32 0a 64  ike_count.} 12.d
2390: 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 35 2e 31 37  o_test like-5.17
23a0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
23b0: 20 20 20 50 52 41 47 4d 41 20 63 61 73 65 5f 73     PRAGMA case_s
23c0: 65 6e 73 69 74 69 76 65 5f 6c 69 6b 65 3d 6f 66  ensitive_like=of
23d0: 66 3b 0a 20 20 7d 0a 20 20 73 65 74 20 73 71 6c  f;.  }.  set sql
23e0: 69 74 65 5f 6c 69 6b 65 5f 63 6f 75 6e 74 20 30  ite_like_count 0
23f0: 0a 20 20 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20  .  queryplan {. 
2400: 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d     SELECT x FROM
2410: 20 74 32 20 57 48 45 52 45 20 78 20 47 4c 4f 42   t2 WHERE x GLOB
2420: 20 27 41 42 43 2a 27 20 4f 52 44 45 52 20 42 59   'ABC*' ORDER BY
2430: 20 31 0a 20 20 7d 0a 7d 20 7b 41 42 43 20 7b 41   1.  }.} {ABC {A
2440: 42 43 20 61 62 63 20 78 79 7a 7d 20 6e 6f 73 6f  BC abc xyz} noso
2450: 72 74 20 7b 7d 20 69 32 7d 0a 64 6f 5f 74 65 73  rt {} i2}.do_tes
2460: 74 20 6c 69 6b 65 2d 35 2e 31 38 20 7b 0a 20 20  t like-5.18 {.  
2470: 73 65 74 20 73 71 6c 69 74 65 5f 6c 69 6b 65 5f  set sqlite_like_
2480: 63 6f 75 6e 74 0a 7d 20 31 32 0a 0a 23 20 42 6f  count.} 12..# Bo
2490: 75 6e 64 61 72 79 20 63 61 73 65 2e 20 20 54 68  undary case.  Th
24a0: 65 20 70 72 65 66 69 78 20 66 6f 72 20 61 20 4c  e prefix for a L
24b0: 49 4b 45 20 63 6f 6d 70 61 72 69 73 6f 6e 20 69  IKE comparison i
24c0: 73 20 72 6f 75 6e 64 65 64 20 75 70 0a 23 20 77  s rounded up.# w
24d0: 68 65 6e 20 63 6f 6e 73 74 72 75 63 74 69 6e 67  hen constructing
24e0: 20 74 68 65 20 63 6f 6d 70 61 72 69 73 6f 6e 2e   the comparison.
24f0: 20 20 45 78 61 6d 70 6c 65 3a 20 20 22 61 62 22    Example:  "ab"
2500: 20 62 65 63 6f 6d 65 73 20 22 61 63 22 2e 0a 23   becomes "ac"..#
2510: 20 49 6e 20 6f 74 68 65 72 20 77 6f 72 64 73 2c   In other words,
2520: 20 74 68 65 20 6c 61 73 74 20 63 68 61 72 61 63   the last charac
2530: 74 65 72 20 69 73 20 69 6e 63 72 65 61 73 65 64  ter is increased
2540: 20 62 79 20 6f 6e 65 2e 0a 23 0a 23 20 4d 61 6b   by one..#.# Mak
2550: 65 20 73 75 72 65 20 74 68 69 73 20 68 61 70 70  e sure this happ
2560: 65 6e 73 20 63 6f 72 72 65 63 74 6c 79 20 77 68  ens correctly wh
2570: 65 6e 20 74 68 65 20 6c 61 73 74 20 63 68 61 72  en the last char
2580: 61 63 74 65 72 20 69 73 20 61 20 0a 23 20 22 7a  acter is a .# "z
2590: 22 20 61 6e 64 20 77 65 20 61 72 65 20 64 6f 69  " and we are doi
25a0: 6e 67 20 63 61 73 65 2d 69 6e 73 65 6e 73 69 74  ng case-insensit
25b0: 69 76 65 20 63 6f 6d 70 61 72 69 73 6f 6e 73 2e  ive comparisons.
25c0: 0a 23 0a 23 20 54 69 63 6b 65 74 20 23 32 39 35  .#.# Ticket #295
25d0: 39 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65  9.#.do_test like
25e0: 2d 35 2e 32 31 20 7b 0a 20 20 65 78 65 63 73 71  -5.21 {.  execsq
25f0: 6c 20 7b 0a 20 20 20 20 50 52 41 47 4d 41 20 63  l {.    PRAGMA c
2600: 61 73 65 5f 73 65 6e 73 69 74 69 76 65 5f 6c 69  ase_sensitive_li
2610: 6b 65 3d 6f 66 66 3b 0a 20 20 20 20 49 4e 53 45  ke=off;.    INSE
2620: 52 54 20 49 4e 54 4f 20 74 32 20 56 41 4c 55 45  RT INTO t2 VALUE
2630: 53 28 27 5a 5a 2d 75 70 70 65 72 2d 75 70 70 65  S('ZZ-upper-uppe
2640: 72 27 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20  r');.    INSERT 
2650: 49 4e 54 4f 20 74 32 20 56 41 4c 55 45 53 28 27  INTO t2 VALUES('
2660: 7a 5a 2d 6c 6f 77 65 72 2d 75 70 70 65 72 27 29  zZ-lower-upper')
2670: 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54  ;.    INSERT INT
2680: 4f 20 74 32 20 56 41 4c 55 45 53 28 27 5a 7a 2d  O t2 VALUES('Zz-
2690: 75 70 70 65 72 2d 6c 6f 77 65 72 27 29 3b 0a 20  upper-lower');. 
26a0: 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74     INSERT INTO t
26b0: 32 20 56 41 4c 55 45 53 28 27 7a 7a 2d 6c 6f 77  2 VALUES('zz-low
26c0: 65 72 2d 6c 6f 77 65 72 27 29 3b 0a 20 20 7d 0a  er-lower');.  }.
26d0: 20 20 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20    queryplan {.  
26e0: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
26f0: 74 32 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  t2 WHERE x LIKE 
2700: 27 7a 7a 25 27 3b 0a 20 20 7d 0a 7d 20 7b 7a 7a  'zz%';.  }.} {zz
2710: 2d 6c 6f 77 65 72 2d 6c 6f 77 65 72 20 7a 5a 2d  -lower-lower zZ-
2720: 6c 6f 77 65 72 2d 75 70 70 65 72 20 5a 7a 2d 75  lower-upper Zz-u
2730: 70 70 65 72 2d 6c 6f 77 65 72 20 5a 5a 2d 75 70  pper-lower ZZ-up
2740: 70 65 72 2d 75 70 70 65 72 20 6e 6f 73 6f 72 74  per-upper nosort
2750: 20 7b 7d 20 69 32 7d 0a 64 6f 5f 74 65 73 74 20   {} i2}.do_test 
2760: 6c 69 6b 65 2d 35 2e 32 32 20 7b 0a 20 20 71 75  like-5.22 {.  qu
2770: 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45  eryplan {.    SE
2780: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32 20 57  LECT x FROM t2 W
2790: 48 45 52 45 20 78 20 4c 49 4b 45 20 27 7a 5a 25  HERE x LIKE 'zZ%
27a0: 27 3b 0a 20 20 7d 0a 7d 20 7b 7a 7a 2d 6c 6f 77  ';.  }.} {zz-low
27b0: 65 72 2d 6c 6f 77 65 72 20 7a 5a 2d 6c 6f 77 65  er-lower zZ-lowe
27c0: 72 2d 75 70 70 65 72 20 5a 7a 2d 75 70 70 65 72  r-upper Zz-upper
27d0: 2d 6c 6f 77 65 72 20 5a 5a 2d 75 70 70 65 72 2d  -lower ZZ-upper-
27e0: 75 70 70 65 72 20 6e 6f 73 6f 72 74 20 7b 7d 20  upper nosort {} 
27f0: 69 32 7d 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65  i2}.do_test like
2800: 2d 35 2e 32 33 20 7b 0a 20 20 71 75 65 72 79 70  -5.23 {.  queryp
2810: 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  lan {.    SELECT
2820: 20 78 20 46 52 4f 4d 20 74 32 20 57 48 45 52 45   x FROM t2 WHERE
2830: 20 78 20 4c 49 4b 45 20 27 5a 7a 25 27 3b 0a 20   x LIKE 'Zz%';. 
2840: 20 7d 0a 7d 20 7b 7a 7a 2d 6c 6f 77 65 72 2d 6c   }.} {zz-lower-l
2850: 6f 77 65 72 20 7a 5a 2d 6c 6f 77 65 72 2d 75 70  ower zZ-lower-up
2860: 70 65 72 20 5a 7a 2d 75 70 70 65 72 2d 6c 6f 77  per Zz-upper-low
2870: 65 72 20 5a 5a 2d 75 70 70 65 72 2d 75 70 70 65  er ZZ-upper-uppe
2880: 72 20 6e 6f 73 6f 72 74 20 7b 7d 20 69 32 7d 0a  r nosort {} i2}.
2890: 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d 35 2e 32  do_test like-5.2
28a0: 34 20 7b 0a 20 20 71 75 65 72 79 70 6c 61 6e 20  4 {.  queryplan 
28b0: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20 46  {.    SELECT x F
28c0: 52 4f 4d 20 74 32 20 57 48 45 52 45 20 78 20 4c  ROM t2 WHERE x L
28d0: 49 4b 45 20 27 5a 5a 25 27 3b 0a 20 20 7d 0a 7d  IKE 'ZZ%';.  }.}
28e0: 20 7b 7a 7a 2d 6c 6f 77 65 72 2d 6c 6f 77 65 72   {zz-lower-lower
28f0: 20 7a 5a 2d 6c 6f 77 65 72 2d 75 70 70 65 72 20   zZ-lower-upper 
2900: 5a 7a 2d 75 70 70 65 72 2d 6c 6f 77 65 72 20 5a  Zz-upper-lower Z
2910: 5a 2d 75 70 70 65 72 2d 75 70 70 65 72 20 6e 6f  Z-upper-upper no
2920: 73 6f 72 74 20 7b 7d 20 69 32 7d 0a 64 6f 5f 74  sort {} i2}.do_t
2930: 65 73 74 20 6c 69 6b 65 2d 35 2e 32 35 20 7b 0a  est like-5.25 {.
2940: 20 20 71 75 65 72 79 70 6c 61 6e 20 7b 0a 20 20    queryplan {.  
2950: 20 20 50 52 41 47 4d 41 20 63 61 73 65 5f 73 65    PRAGMA case_se
2960: 6e 73 69 74 69 76 65 5f 6c 69 6b 65 3d 6f 6e 3b  nsitive_like=on;
2970: 0a 20 20 20 20 43 52 45 41 54 45 20 54 41 42 4c  .    CREATE TABL
2980: 45 20 74 33 28 78 29 3b 0a 20 20 20 20 43 52 45  E t3(x);.    CRE
2990: 41 54 45 20 49 4e 44 45 58 20 69 33 20 4f 4e 20  ATE INDEX i3 ON 
29a0: 74 33 28 78 29 3b 0a 20 20 20 20 49 4e 53 45 52  t3(x);.    INSER
29b0: 54 20 49 4e 54 4f 20 74 33 20 56 41 4c 55 45 53  T INTO t3 VALUES
29c0: 28 27 5a 5a 2d 75 70 70 65 72 2d 75 70 70 65 72  ('ZZ-upper-upper
29d0: 27 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49  ');.    INSERT I
29e0: 4e 54 4f 20 74 33 20 56 41 4c 55 45 53 28 27 7a  NTO t3 VALUES('z
29f0: 5a 2d 6c 6f 77 65 72 2d 75 70 70 65 72 27 29 3b  Z-lower-upper');
2a00: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
2a10: 20 74 33 20 56 41 4c 55 45 53 28 27 5a 7a 2d 75   t3 VALUES('Zz-u
2a20: 70 70 65 72 2d 6c 6f 77 65 72 27 29 3b 0a 20 20  pper-lower');.  
2a30: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 33    INSERT INTO t3
2a40: 20 56 41 4c 55 45 53 28 27 7a 7a 2d 6c 6f 77 65   VALUES('zz-lowe
2a50: 72 2d 6c 6f 77 65 72 27 29 3b 0a 20 20 20 20 53  r-lower');.    S
2a60: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 33 20  ELECT x FROM t3 
2a70: 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 7a 7a  WHERE x LIKE 'zz
2a80: 25 27 3b 0a 20 20 7d 0a 7d 20 7b 7a 7a 2d 6c 6f  %';.  }.} {zz-lo
2a90: 77 65 72 2d 6c 6f 77 65 72 20 6e 6f 73 6f 72 74  wer-lower nosort
2aa0: 20 7b 7d 20 69 33 7d 0a 64 6f 5f 74 65 73 74 20   {} i3}.do_test 
2ab0: 6c 69 6b 65 2d 35 2e 32 36 20 7b 0a 20 20 71 75  like-5.26 {.  qu
2ac0: 65 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45  eryplan {.    SE
2ad0: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 33 20 57  LECT x FROM t3 W
2ae0: 48 45 52 45 20 78 20 4c 49 4b 45 20 27 7a 5a 25  HERE x LIKE 'zZ%
2af0: 27 3b 0a 20 20 7d 0a 7d 20 7b 7a 5a 2d 6c 6f 77  ';.  }.} {zZ-low
2b00: 65 72 2d 75 70 70 65 72 20 6e 6f 73 6f 72 74 20  er-upper nosort 
2b10: 7b 7d 20 69 33 7d 0a 64 6f 5f 74 65 73 74 20 6c  {} i3}.do_test l
2b20: 69 6b 65 2d 35 2e 32 37 20 7b 0a 20 20 71 75 65  ike-5.27 {.  que
2b30: 72 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c  ryplan {.    SEL
2b40: 45 43 54 20 78 20 46 52 4f 4d 20 74 33 20 57 48  ECT x FROM t3 WH
2b50: 45 52 45 20 78 20 4c 49 4b 45 20 27 5a 7a 25 27  ERE x LIKE 'Zz%'
2b60: 3b 0a 20 20 7d 0a 7d 20 7b 5a 7a 2d 75 70 70 65  ;.  }.} {Zz-uppe
2b70: 72 2d 6c 6f 77 65 72 20 6e 6f 73 6f 72 74 20 7b  r-lower nosort {
2b80: 7d 20 69 33 7d 0a 64 6f 5f 74 65 73 74 20 6c 69  } i3}.do_test li
2b90: 6b 65 2d 35 2e 32 38 20 7b 0a 20 20 71 75 65 72  ke-5.28 {.  quer
2ba0: 79 70 6c 61 6e 20 7b 0a 20 20 20 20 53 45 4c 45  yplan {.    SELE
2bb0: 43 54 20 78 20 46 52 4f 4d 20 74 33 20 57 48 45  CT x FROM t3 WHE
2bc0: 52 45 20 78 20 4c 49 4b 45 20 27 5a 5a 25 27 3b  RE x LIKE 'ZZ%';
2bd0: 0a 20 20 7d 0a 7d 20 7b 5a 5a 2d 75 70 70 65 72  .  }.} {ZZ-upper
2be0: 2d 75 70 70 65 72 20 6e 6f 73 6f 72 74 20 7b 7d  -upper nosort {}
2bf0: 20 69 33 7d 0a 0a 0a 23 20 74 69 63 6b 65 74 20   i3}...# ticket 
2c00: 23 32 34 30 37 0a 23 0a 23 20 4d 61 6b 65 20 73  #2407.#.# Make s
2c10: 75 72 65 20 74 68 65 20 4c 49 4b 45 20 70 72 65  ure the LIKE pre
2c20: 66 69 78 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e  fix optimization
2c30: 20 64 6f 65 73 20 6e 6f 74 20 73 74 72 69 70 20   does not strip 
2c40: 6f 66 66 20 6c 65 61 64 69 6e 67 0a 23 20 63 68  off leading.# ch
2c50: 61 72 61 63 74 65 72 73 20 6f 66 20 74 68 65 20  aracters of the 
2c60: 6c 69 6b 65 20 70 61 74 74 65 72 6e 20 74 68 61  like pattern tha
2c70: 74 20 68 61 70 70 65 6e 20 74 6f 20 62 65 20 71  t happen to be q
2c80: 75 6f 74 65 20 63 68 61 72 61 63 74 65 72 73 2e  uote characters.
2c90: 0a 23 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d  .#.do_test like-
2ca0: 36 2e 31 20 7b 0a 20 20 66 6f 72 65 61 63 68 20  6.1 {.  foreach 
2cb0: 78 20 7b 20 27 61 62 63 20 27 62 63 64 20 27 64  x { 'abc 'bcd 'd
2cc0: 65 66 20 27 61 78 20 7d 20 7b 0a 20 20 20 20 73  ef 'ax } {.    s
2cd0: 65 74 20 78 32 20 27 5b 73 74 72 69 6e 67 20 6d  et x2 '[string m
2ce0: 61 70 20 7b 27 20 27 27 7d 20 24 78 5d 27 0a 20  ap {' ''} $x]'. 
2cf0: 20 20 20 64 62 20 65 76 61 6c 20 22 49 4e 53 45     db eval "INSE
2d00: 52 54 20 49 4e 54 4f 20 74 32 20 56 41 4c 55 45  RT INTO t2 VALUE
2d10: 53 28 24 78 32 29 22 0a 20 20 7d 0a 20 20 65 78  S($x2)".  }.  ex
2d20: 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  ecsql {.    SELE
2d30: 43 54 20 2a 20 46 52 4f 4d 20 74 32 20 57 48 45  CT * FROM t2 WHE
2d40: 52 45 20 78 20 4c 49 4b 45 20 27 27 27 61 25 27  RE x LIKE '''a%'
2d50: 0a 20 20 7d 0a 7d 20 7b 27 61 62 63 20 27 61 78  .  }.} {'abc 'ax
2d60: 7d 0a 0a 64 6f 5f 74 65 73 74 20 6c 69 6b 65 2d  }..do_test like-
2d70: 37 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  7.1 {.  execsql 
2d80: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46  {.    SELECT * F
2d90: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 72 6f 77  ROM t1 WHERE row
2da0: 69 64 20 47 4c 4f 42 20 27 31 2a 27 3b 0a 20 20  id GLOB '1*';.  
2db0: 7d 0a 7d 20 7b 61 7d 0a 0a 66 69 6e 69 73 68 5f  }.} {a}..finish_
2dc0: 74 65 73 74 0a                                   test.