SQLite: Hex Artifact Content

Artifact cf0ff2d06c9d8456283aeff405b911642298441206306aeaeaa93973233b1195:

File test/like3.test — part of check-in [f191431d] at 2018-09-10 12:40:57 on branch trunk — Fix the LIKE optimization so that it is disabled when the LHS of the expression does not have TEXT affinity and the RHS is the pattern '/%' or the RHS begins with the escape character. Fix for ticket [c94369cae9b561b1f996d0054b]. (user: drh size: 5707) [more...]
0000: 23 20 32 30 31 35 2d 30 33 2d 30 36 0a 23 0a 23  # 2015-03-06.#.#
0010: 20 54 68 65 20 61 75 74 68 6f 72 20 64 69 73 63   The author disc
0020: 6c 61 69 6d 73 20 63 6f 70 79 72 69 67 68 74 20  laims copyright 
0030: 74 6f 20 74 68 69 73 20 73 6f 75 72 63 65 20 63  to this source c
0040: 6f 64 65 2e 20 20 49 6e 20 70 6c 61 63 65 20 6f  ode.  In place o
0050: 66 0a 23 20 61 20 6c 65 67 61 6c 20 6e 6f 74 69  f.# a legal noti
0060: 63 65 2c 20 68 65 72 65 20 69 73 20 61 20 62 6c  ce, here is a bl
0070: 65 73 73 69 6e 67 3a 0a 23 0a 23 20 20 20 20 4d  essing:.#.#    M
0080: 61 79 20 79 6f 75 20 64 6f 20 67 6f 6f 64 20 61  ay you do good a
0090: 6e 64 20 6e 6f 74 20 65 76 69 6c 2e 0a 23 20 20  nd not evil..#  
00a0: 20 20 4d 61 79 20 79 6f 75 20 66 69 6e 64 20 66    May you find f
00b0: 6f 72 67 69 76 65 6e 65 73 73 20 66 6f 72 20 79  orgiveness for y
00c0: 6f 75 72 73 65 6c 66 20 61 6e 64 20 66 6f 72 67  ourself and forg
00d0: 69 76 65 20 6f 74 68 65 72 73 2e 0a 23 20 20 20  ive others..#   
00e0: 20 4d 61 79 20 79 6f 75 20 73 68 61 72 65 20 66   May you share f
00f0: 72 65 65 6c 79 2c 20 6e 65 76 65 72 20 74 61 6b  reely, never tak
0100: 69 6e 67 20 6d 6f 72 65 20 74 68 61 6e 20 79 6f  ing more than yo
0110: 75 20 67 69 76 65 2e 0a 23 0a 23 2a 2a 2a 2a 2a  u 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 0a 23 0a 23 20 54 68 69 73 20 66 69 6c 65  **.#.# This file
0170: 20 69 6d 70 6c 65 6d 65 6e 74 73 20 72 65 67 72   implements regr
0180: 65 73 73 69 6f 6e 20 74 65 73 74 73 20 66 6f 72  ession tests for
0190: 20 53 51 4c 69 74 65 20 6c 69 62 72 61 72 79 2e   SQLite library.
01a0: 20 20 54 68 65 0a 23 20 66 6f 63 75 73 20 6f 66    The.# focus of
01b0: 20 74 68 69 73 20 66 69 6c 65 20 69 73 20 74 65   this file is te
01c0: 73 74 69 6e 67 20 74 68 65 20 4c 49 4b 45 20 61  sting the LIKE a
01d0: 6e 64 20 47 4c 4f 42 20 6f 70 65 72 61 74 6f 72  nd GLOB operator
01e0: 73 20 61 6e 64 0a 23 20 69 6e 20 70 61 72 74 69  s and.# in parti
01f0: 63 75 6c 61 72 20 74 68 65 20 6f 70 74 69 6d 69  cular the optimi
0200: 7a 61 74 69 6f 6e 73 20 74 68 61 74 20 6f 63 63  zations that occ
0210: 75 72 20 74 6f 20 68 65 6c 70 20 74 68 6f 73 65  ur to help those
0220: 20 6f 70 65 72 61 74 6f 72 73 0a 23 20 72 75 6e   operators.# run
0230: 20 66 61 73 74 65 72 20 61 6e 64 20 74 68 61 74   faster and that
0240: 20 74 68 6f 73 65 20 6f 70 74 69 6d 69 7a 61 74   those optimizat
0250: 69 6f 6e 73 20 77 6f 72 6b 20 63 6f 72 72 65 63  ions work correc
0260: 74 6c 79 20 77 68 65 6e 20 74 68 65 72 65 0a 23  tly when there.#
0270: 20 61 72 65 20 62 6f 74 68 20 73 74 72 69 6e 67   are both string
0280: 73 20 61 6e 64 20 62 6c 6f 62 73 20 62 65 69 6e  s and blobs bein
0290: 67 20 74 65 73 74 65 64 2e 0a 23 0a 23 20 54 69  g tested..#.# Ti
02a0: 63 6b 65 74 20 30 35 66 34 33 62 65 38 66 64 64  cket 05f43be8fdd
02b0: 61 39 66 62 64 39 34 38 64 33 37 34 33 31 39 62  a9fbd948d374319b
02c0: 39 39 62 30 35 34 31 34 30 62 63 33 36 20 73 68  99b054140bc36 sh
02d0: 6f 77 73 20 74 68 61 74 20 74 68 65 20 66 6f 6c  ows that the fol
02e0: 6c 6f 77 69 6e 67 0a 23 20 53 51 4c 20 77 61 73  lowing.# SQL was
02f0: 20 6e 6f 74 20 77 6f 72 6b 69 6e 67 20 63 6f 72   not working cor
0300: 72 65 63 74 6c 79 3a 0a 23 0a 23 20 20 20 20 20  rectly:.#.#     
0310: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 28  CREATE TABLE t1(
0320: 78 20 54 45 58 54 20 55 4e 49 51 55 45 20 43 4f  x TEXT UNIQUE CO
0330: 4c 4c 41 54 45 20 6e 6f 63 61 73 65 29 3b 0a 23  LLATE nocase);.#
0340: 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f       INSERT INTO
0350: 20 74 31 28 78 29 20 56 41 4c 55 45 53 28 78 27   t1(x) VALUES(x'
0360: 36 31 36 32 36 33 27 29 3b 0a 23 20 20 20 20 20  616263');.#     
0370: 53 45 4c 45 43 54 20 27 71 75 65 72 79 2d 31 27  SELECT 'query-1'
0380: 2c 20 78 20 46 52 4f 4d 20 74 31 20 57 48 45 52  , x FROM t1 WHER
0390: 45 20 78 20 4c 49 4b 45 20 27 61 25 27 3b 0a 23  E x LIKE 'a%';.#
03a0: 20 20 20 20 20 53 45 4c 45 43 54 20 27 71 75 65       SELECT 'que
03b0: 72 79 2d 32 27 2c 20 78 20 46 52 4f 4d 20 74 31  ry-2', x FROM t1
03c0: 20 57 48 45 52 45 20 2b 78 20 4c 49 4b 45 20 27   WHERE +x LIKE '
03d0: 61 25 27 3b 0a 23 0a 23 20 54 68 69 73 20 73 63  a%';.#.# This sc
03e0: 72 69 70 74 20 76 65 72 69 66 69 65 73 20 74 68  ript verifies th
03f0: 61 74 20 69 74 20 77 6f 72 6b 73 20 72 69 67 68  at it works righ
0400: 74 20 6e 6f 77 2e 0a 23 0a 0a 73 65 74 20 74 65  t now..#..set te
0410: 73 74 64 69 72 20 5b 66 69 6c 65 20 64 69 72 6e  stdir [file dirn
0420: 61 6d 65 20 24 61 72 67 76 30 5d 0a 73 6f 75 72  ame $argv0].sour
0430: 63 65 20 24 74 65 73 74 64 69 72 2f 74 65 73 74  ce $testdir/test
0440: 65 72 2e 74 63 6c 0a 0a 69 66 63 61 70 61 62 6c  er.tcl..ifcapabl
0450: 65 20 21 6c 69 6b 65 5f 6d 61 74 63 68 5f 62 6c  e !like_match_bl
0460: 6f 62 73 20 7b 0a 20 20 66 69 6e 69 73 68 5f 74  obs {.  finish_t
0470: 65 73 74 0a 20 20 72 65 74 75 72 6e 0a 7d 0a 0a  est.  return.}..
0480: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
0490: 6c 69 6b 65 33 2d 31 2e 31 20 7b 0a 20 20 50 52  like3-1.1 {.  PR
04a0: 41 47 4d 41 20 65 6e 63 6f 64 69 6e 67 3d 55 54  AGMA encoding=UT
04b0: 46 38 3b 0a 20 20 43 52 45 41 54 45 20 54 41 42  F8;.  CREATE TAB
04c0: 4c 45 20 74 31 28 61 2c 62 20 54 45 58 54 20 43  LE t1(a,b TEXT C
04d0: 4f 4c 4c 41 54 45 20 6e 6f 63 61 73 65 29 3b 0a  OLLATE nocase);.
04e0: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31    INSERT INTO t1
04f0: 28 61 2c 62 29 0a 20 20 20 20 20 56 41 4c 55 45  (a,b).     VALUE
0500: 53 28 31 2c 27 61 62 63 27 29 2c 0a 20 20 20 20  S(1,'abc'),.    
0510: 20 20 20 20 20 20 20 28 32 2c 27 41 42 58 27 29         (2,'ABX')
0520: 2c 0a 20 20 20 20 20 20 20 20 20 20 20 28 33 2c  ,.           (3,
0530: 27 42 43 44 27 29 2c 0a 20 20 20 20 20 20 20 20  'BCD'),.        
0540: 20 20 20 28 34 2c 78 27 36 31 36 32 36 33 27 29     (4,x'616263')
0550: 2c 0a 20 20 20 20 20 20 20 20 20 20 20 28 35 2c  ,.           (5,
0560: 78 27 34 31 34 32 35 38 27 29 2c 0a 20 20 20 20  x'414258'),.    
0570: 20 20 20 20 20 20 20 28 36 2c 78 27 34 32 34 33         (6,x'4243
0580: 34 34 27 29 3b 0a 20 20 43 52 45 41 54 45 20 49  44');.  CREATE I
0590: 4e 44 45 58 20 74 31 62 61 20 4f 4e 20 74 31 28  NDEX t1ba ON t1(
05a0: 62 2c 61 29 3b 0a 0a 20 20 53 45 4c 45 43 54 20  b,a);..  SELECT 
05b0: 61 2c 20 62 20 46 52 4f 4d 20 74 31 20 57 48 45  a, b FROM t1 WHE
05c0: 52 45 20 62 20 4c 49 4b 45 20 27 61 42 25 27 20  RE b LIKE 'aB%' 
05d0: 4f 52 44 45 52 20 42 59 20 2b 61 3b 0a 7d 20 7b  ORDER BY +a;.} {
05e0: 31 20 61 62 63 20 32 20 41 42 58 20 34 20 61 62  1 abc 2 ABX 4 ab
05f0: 63 20 35 20 41 42 58 7d 0a 64 6f 5f 65 78 65 63  c 5 ABX}.do_exec
0600: 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65 33 2d 31  sql_test like3-1
0610: 2e 32 20 7b 0a 20 20 53 45 4c 45 43 54 20 61 2c  .2 {.  SELECT a,
0620: 20 62 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   b FROM t1 WHERE
0630: 20 2b 62 20 4c 49 4b 45 20 27 61 42 25 27 20 4f   +b LIKE 'aB%' O
0640: 52 44 45 52 20 42 59 20 2b 61 3b 0a 7d 20 7b 31  RDER BY +a;.} {1
0650: 20 61 62 63 20 32 20 41 42 58 20 34 20 61 62 63   abc 2 ABX 4 abc
0660: 20 35 20 41 42 58 7d 0a 0a 64 6f 5f 65 78 65 63   5 ABX}..do_exec
0670: 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65 33 2d 32  sql_test like3-2
0680: 2e 30 20 7b 0a 20 20 43 52 45 41 54 45 20 54 41  .0 {.  CREATE TA
0690: 42 4c 45 20 74 32 28 61 2c 20 62 20 54 45 58 54  BLE t2(a, b TEXT
06a0: 29 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  );.  INSERT INTO
06b0: 20 74 32 20 53 45 4c 45 43 54 20 61 2c 20 62 20   t2 SELECT a, b 
06c0: 46 52 4f 4d 20 74 31 3b 0a 20 20 43 52 45 41 54  FROM t1;.  CREAT
06d0: 45 20 49 4e 44 45 58 20 74 32 62 61 20 4f 4e 20  E INDEX t2ba ON 
06e0: 74 32 28 62 2c 61 29 3b 0a 20 20 53 45 4c 45 43  t2(b,a);.  SELEC
06f0: 54 20 61 2c 20 62 20 46 52 4f 4d 20 74 32 20 57  T a, b FROM t2 W
0700: 48 45 52 45 20 62 20 47 4c 4f 42 20 27 61 62 2a  HERE b GLOB 'ab*
0710: 27 20 4f 52 44 45 52 20 42 59 20 2b 61 3b 0a 7d  ' ORDER BY +a;.}
0720: 20 7b 31 20 61 62 63 20 34 20 61 62 63 7d 0a 64   {1 abc 4 abc}.d
0730: 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 6c  o_execsql_test l
0740: 69 6b 65 33 2d 32 2e 31 20 7b 0a 20 20 53 45 4c  ike3-2.1 {.  SEL
0750: 45 43 54 20 61 2c 20 62 20 46 52 4f 4d 20 74 32  ECT a, b FROM t2
0760: 20 57 48 45 52 45 20 2b 62 20 47 4c 4f 42 20 27   WHERE +b GLOB '
0770: 61 62 2a 27 20 4f 52 44 45 52 20 42 59 20 2b 61  ab*' ORDER BY +a
0780: 3b 0a 7d 20 7b 31 20 61 62 63 20 34 20 61 62 63  ;.} {1 abc 4 abc
0790: 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73  }.do_execsql_tes
07a0: 74 20 6c 69 6b 65 33 2d 32 2e 32 20 7b 0a 20 20  t like3-2.2 {.  
07b0: 53 45 4c 45 43 54 20 61 2c 20 62 20 46 52 4f 4d  SELECT a, b FROM
07c0: 20 74 32 20 57 48 45 52 45 20 62 3e 3d 78 27 36   t2 WHERE b>=x'6
07d0: 31 36 32 27 20 41 4e 44 20 62 20 47 4c 4f 42 20  162' AND b GLOB 
07e0: 27 61 62 2a 27 0a 7d 20 7b 34 20 61 62 63 7d 0a  'ab*'.} {4 abc}.
07f0: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
0800: 6c 69 6b 65 33 2d 32 2e 33 20 7b 0a 20 20 53 45  like3-2.3 {.  SE
0810: 4c 45 43 54 20 61 2c 20 62 20 46 52 4f 4d 20 74  LECT a, b FROM t
0820: 32 20 57 48 45 52 45 20 2b 62 3e 3d 78 27 36 31  2 WHERE +b>=x'61
0830: 36 32 27 20 41 4e 44 20 2b 62 20 47 4c 4f 42 20  62' AND +b GLOB 
0840: 27 61 62 2a 27 0a 7d 20 7b 34 20 61 62 63 7d 0a  'ab*'.} {4 abc}.
0850: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
0860: 6c 69 6b 65 33 2d 32 2e 34 20 7b 0a 20 20 53 45  like3-2.4 {.  SE
0870: 4c 45 43 54 20 61 2c 20 62 20 46 52 4f 4d 20 74  LECT a, b FROM t
0880: 32 20 57 48 45 52 45 20 62 20 47 4c 4f 42 20 27  2 WHERE b GLOB '
0890: 61 62 2a 27 20 41 4e 44 20 62 3e 3d 78 27 36 31  ab*' AND b>=x'61
08a0: 36 32 27 0a 7d 20 7b 34 20 61 62 63 7d 0a 64 6f  62'.} {4 abc}.do
08b0: 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 6c 69  _execsql_test li
08c0: 6b 65 33 2d 32 2e 35 20 7b 0a 20 20 53 45 4c 45  ke3-2.5 {.  SELE
08d0: 43 54 20 61 2c 20 62 20 46 52 4f 4d 20 74 32 20  CT a, b FROM t2 
08e0: 57 48 45 52 45 20 2b 62 20 47 4c 4f 42 20 27 61  WHERE +b GLOB 'a
08f0: 62 2a 27 20 41 4e 44 20 2b 62 3e 3d 78 27 36 31  b*' AND +b>=x'61
0900: 36 32 27 0a 7d 20 7b 34 20 61 62 63 7d 0a 0a 64  62'.} {4 abc}..d
0910: 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 6c  o_execsql_test l
0920: 69 6b 65 33 2d 33 2e 30 20 7b 0a 20 20 43 52 45  ike3-3.0 {.  CRE
0930: 41 54 45 20 54 41 42 4c 45 20 74 33 28 78 20 54  ATE TABLE t3(x T
0940: 45 58 54 20 50 52 49 4d 41 52 59 20 4b 45 59 20  EXT PRIMARY KEY 
0950: 43 4f 4c 4c 41 54 45 20 6e 6f 63 61 73 65 29 3b  COLLATE nocase);
0960: 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74  .  INSERT INTO t
0970: 33 28 78 29 20 56 41 4c 55 45 53 28 27 61 61 61  3(x) VALUES('aaa
0980: 27 29 2c 28 27 61 62 63 27 29 2c 28 27 61 62 64  '),('abc'),('abd
0990: 27 29 2c 28 27 61 62 65 27 29 2c 28 27 61 63 7a  '),('abe'),('acz
09a0: 27 29 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e 54  ');.  INSERT INT
09b0: 4f 20 74 33 28 78 29 20 53 45 4c 45 43 54 20 43  O t3(x) SELECT C
09c0: 41 53 54 28 78 20 41 53 20 62 6c 6f 62 29 20 46  AST(x AS blob) F
09d0: 52 4f 4d 20 74 33 3b 0a 20 20 53 45 4c 45 43 54  ROM t3;.  SELECT
09e0: 20 71 75 6f 74 65 28 78 29 20 46 52 4f 4d 20 74   quote(x) FROM t
09f0: 33 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27  3 WHERE x LIKE '
0a00: 61 62 25 27 20 4f 52 44 45 52 20 42 59 20 78 3b  ab%' ORDER BY x;
0a10: 0a 7d 20 7b 27 61 62 63 27 20 27 61 62 64 27 20  .} {'abc' 'abd' 
0a20: 27 61 62 65 27 20 58 27 36 31 36 32 36 33 27 20  'abe' X'616263' 
0a30: 58 27 36 31 36 32 36 34 27 20 58 27 36 31 36 32  X'616264' X'6162
0a40: 36 35 27 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f  65'}.do_execsql_
0a50: 74 65 73 74 20 6c 69 6b 65 33 2d 33 2e 31 20 7b  test like3-3.1 {
0a60: 0a 20 20 53 45 4c 45 43 54 20 71 75 6f 74 65 28  .  SELECT quote(
0a70: 78 29 20 46 52 4f 4d 20 74 33 20 57 48 45 52 45  x) FROM t3 WHERE
0a80: 20 78 20 4c 49 4b 45 20 27 61 62 25 27 20 4f 52   x LIKE 'ab%' OR
0a90: 44 45 52 20 42 59 20 78 20 44 45 53 43 3b 0a 7d  DER BY x DESC;.}
0aa0: 20 7b 58 27 36 31 36 32 36 35 27 20 58 27 36 31   {X'616265' X'61
0ab0: 36 32 36 34 27 20 58 27 36 31 36 32 36 33 27 20  6264' X'616263' 
0ac0: 27 61 62 65 27 20 27 61 62 64 27 20 27 61 62 63  'abe' 'abd' 'abc
0ad0: 27 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65  '}.do_execsql_te
0ae0: 73 74 20 6c 69 6b 65 33 2d 33 2e 31 63 6b 20 7b  st like3-3.1ck {
0af0: 0a 20 20 53 45 4c 45 43 54 20 71 75 6f 74 65 28  .  SELECT quote(
0b00: 78 29 20 46 52 4f 4d 20 74 33 20 57 48 45 52 45  x) FROM t3 WHERE
0b10: 20 78 20 4c 49 4b 45 20 27 61 62 25 27 20 4f 52   x LIKE 'ab%' OR
0b20: 44 45 52 20 42 59 20 2b 78 20 44 45 53 43 3b 0a  DER BY +x DESC;.
0b30: 7d 20 7b 58 27 36 31 36 32 36 35 27 20 58 27 36  } {X'616265' X'6
0b40: 31 36 32 36 34 27 20 58 27 36 31 36 32 36 33 27  16264' X'616263'
0b50: 20 27 61 62 65 27 20 27 61 62 64 27 20 27 61 62   'abe' 'abd' 'ab
0b60: 63 27 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74  c'}.do_execsql_t
0b70: 65 73 74 20 6c 69 6b 65 33 2d 33 2e 32 20 7b 0a  est like3-3.2 {.
0b80: 20 20 53 45 4c 45 43 54 20 71 75 6f 74 65 28 78    SELECT quote(x
0b90: 29 20 46 52 4f 4d 20 74 33 20 57 48 45 52 45 20  ) FROM t3 WHERE 
0ba0: 78 20 4c 49 4b 45 20 27 61 62 25 27 20 4f 52 44  x LIKE 'ab%' ORD
0bb0: 45 52 20 42 59 20 78 20 41 53 43 3b 0a 7d 20 7b  ER BY x ASC;.} {
0bc0: 27 61 62 63 27 20 27 61 62 64 27 20 27 61 62 65  'abc' 'abd' 'abe
0bd0: 27 20 58 27 36 31 36 32 36 33 27 20 58 27 36 31  ' X'616263' X'61
0be0: 36 32 36 34 27 20 58 27 36 31 36 32 36 35 27 7d  6264' X'616265'}
0bf0: 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74  .do_execsql_test
0c00: 20 6c 69 6b 65 33 2d 33 2e 32 63 6b 20 7b 0a 20   like3-3.2ck {. 
0c10: 20 53 45 4c 45 43 54 20 71 75 6f 74 65 28 78 29   SELECT quote(x)
0c20: 20 46 52 4f 4d 20 74 33 20 57 48 45 52 45 20 78   FROM t3 WHERE x
0c30: 20 4c 49 4b 45 20 27 61 62 25 27 20 4f 52 44 45   LIKE 'ab%' ORDE
0c40: 52 20 42 59 20 2b 78 20 41 53 43 3b 0a 7d 20 7b  R BY +x ASC;.} {
0c50: 27 61 62 63 27 20 27 61 62 64 27 20 27 61 62 65  'abc' 'abd' 'abe
0c60: 27 20 58 27 36 31 36 32 36 33 27 20 58 27 36 31  ' X'616263' X'61
0c70: 36 32 36 34 27 20 58 27 36 31 36 32 36 35 27 7d  6264' X'616265'}
0c80: 0a 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73  ..do_execsql_tes
0c90: 74 20 6c 69 6b 65 33 2d 34 2e 30 20 7b 0a 20 20  t like3-4.0 {.  
0ca0: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 34 28  CREATE TABLE t4(
0cb0: 78 20 54 45 58 54 20 43 4f 4c 4c 41 54 45 20 6e  x TEXT COLLATE n
0cc0: 6f 63 61 73 65 29 3b 0a 20 20 43 52 45 41 54 45  ocase);.  CREATE
0cd0: 20 49 4e 44 45 58 20 74 34 78 20 4f 4e 20 74 34   INDEX t4x ON t4
0ce0: 28 78 20 44 45 53 43 29 3b 0a 20 20 49 4e 53 45  (x DESC);.  INSE
0cf0: 52 54 20 49 4e 54 4f 20 74 34 28 78 29 20 53 45  RT INTO t4(x) SE
0d00: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 33 3b 0a  LECT x FROM t3;.
0d10: 20 20 53 45 4c 45 43 54 20 71 75 6f 74 65 28 78    SELECT quote(x
0d20: 29 20 46 52 4f 4d 20 74 34 20 57 48 45 52 45 20  ) FROM t4 WHERE 
0d30: 78 20 4c 49 4b 45 20 27 61 62 25 27 20 4f 52 44  x LIKE 'ab%' ORD
0d40: 45 52 20 42 59 20 78 3b 0a 7d 20 7b 27 61 62 63  ER BY x;.} {'abc
0d50: 27 20 27 61 62 64 27 20 27 61 62 65 27 20 58 27  ' 'abd' 'abe' X'
0d60: 36 31 36 32 36 33 27 20 58 27 36 31 36 32 36 34  616263' X'616264
0d70: 27 20 58 27 36 31 36 32 36 35 27 7d 0a 64 6f 5f  ' X'616265'}.do_
0d80: 65 78 65 63 73 71 6c 5f 74 65 73 74 20 6c 69 6b  execsql_test lik
0d90: 65 33 2d 34 2e 31 20 7b 0a 20 20 53 45 4c 45 43  e3-4.1 {.  SELEC
0da0: 54 20 71 75 6f 74 65 28 78 29 20 46 52 4f 4d 20  T quote(x) FROM 
0db0: 74 34 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  t4 WHERE x LIKE 
0dc0: 27 61 62 25 27 20 4f 52 44 45 52 20 42 59 20 78  'ab%' ORDER BY x
0dd0: 20 44 45 53 43 3b 0a 7d 20 7b 58 27 36 31 36 32   DESC;.} {X'6162
0de0: 36 35 27 20 58 27 36 31 36 32 36 34 27 20 58 27  65' X'616264' X'
0df0: 36 31 36 32 36 33 27 20 27 61 62 65 27 20 27 61  616263' 'abe' 'a
0e00: 62 64 27 20 27 61 62 63 27 7d 0a 64 6f 5f 65 78  bd' 'abc'}.do_ex
0e10: 65 63 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65 33  ecsql_test like3
0e20: 2d 34 2e 31 63 6b 20 7b 0a 20 20 53 45 4c 45 43  -4.1ck {.  SELEC
0e30: 54 20 71 75 6f 74 65 28 78 29 20 46 52 4f 4d 20  T quote(x) FROM 
0e40: 74 34 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  t4 WHERE x LIKE 
0e50: 27 61 62 25 27 20 4f 52 44 45 52 20 42 59 20 2b  'ab%' ORDER BY +
0e60: 78 20 44 45 53 43 3b 0a 7d 20 7b 58 27 36 31 36  x DESC;.} {X'616
0e70: 32 36 35 27 20 58 27 36 31 36 32 36 34 27 20 58  265' X'616264' X
0e80: 27 36 31 36 32 36 33 27 20 27 61 62 65 27 20 27  '616263' 'abe' '
0e90: 61 62 64 27 20 27 61 62 63 27 7d 0a 64 6f 5f 65  abd' 'abc'}.do_e
0ea0: 78 65 63 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65  xecsql_test like
0eb0: 33 2d 34 2e 32 20 7b 0a 20 20 53 45 4c 45 43 54  3-4.2 {.  SELECT
0ec0: 20 71 75 6f 74 65 28 78 29 20 46 52 4f 4d 20 74   quote(x) FROM t
0ed0: 34 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27  4 WHERE x LIKE '
0ee0: 61 62 25 27 20 4f 52 44 45 52 20 42 59 20 78 20  ab%' ORDER BY x 
0ef0: 41 53 43 3b 0a 7d 20 7b 27 61 62 63 27 20 27 61  ASC;.} {'abc' 'a
0f00: 62 64 27 20 27 61 62 65 27 20 58 27 36 31 36 32  bd' 'abe' X'6162
0f10: 36 33 27 20 58 27 36 31 36 32 36 34 27 20 58 27  63' X'616264' X'
0f20: 36 31 36 32 36 35 27 7d 0a 64 6f 5f 65 78 65 63  616265'}.do_exec
0f30: 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65 33 2d 34  sql_test like3-4
0f40: 2e 32 63 6b 20 7b 0a 20 20 53 45 4c 45 43 54 20  .2ck {.  SELECT 
0f50: 71 75 6f 74 65 28 78 29 20 46 52 4f 4d 20 74 34  quote(x) FROM t4
0f60: 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27 61   WHERE x LIKE 'a
0f70: 62 25 27 20 4f 52 44 45 52 20 42 59 20 2b 78 20  b%' ORDER BY +x 
0f80: 41 53 43 3b 0a 7d 20 7b 27 61 62 63 27 20 27 61  ASC;.} {'abc' 'a
0f90: 62 64 27 20 27 61 62 65 27 20 58 27 36 31 36 32  bd' 'abe' X'6162
0fa0: 36 33 27 20 58 27 36 31 36 32 36 34 27 20 58 27  63' X'616264' X'
0fb0: 36 31 36 32 36 35 27 7d 0a 0a 23 20 32 30 31 38  616265'}..# 2018
0fc0: 2d 30 39 2d 31 30 20 74 69 63 6b 65 74 20 68 74  -09-10 ticket ht
0fd0: 74 70 73 3a 2f 2f 77 77 77 2e 73 71 6c 69 74 65  tps://www.sqlite
0fe0: 2e 6f 72 67 2f 73 72 63 2f 74 6b 74 76 69 65 77  .org/src/tktview
0ff0: 2f 63 39 34 33 36 39 63 61 65 39 62 35 36 31 62  /c94369cae9b561b
1000: 31 66 39 39 36 0a 23 20 54 68 65 20 6c 69 6b 65  1f996.# The like
1010: 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 20 66 61   optimization fa
1020: 69 6c 73 20 66 6f 72 20 61 20 63 6f 6c 75 6d 6e  ils for a column
1030: 20 77 69 74 68 20 6e 75 6d 65 72 69 63 20 61 66   with numeric af
1040: 66 69 6e 69 74 79 20 69 66 0a 23 20 74 68 65 20  finity if.# the 
1050: 70 61 74 74 65 72 6e 20 27 2f 25 27 20 6f 72 20  pattern '/%' or 
1060: 62 65 67 69 6e 73 20 77 69 74 68 20 74 68 65 20  begins with the 
1070: 65 73 63 61 70 65 20 63 68 61 72 61 63 74 65 72  escape character
1080: 2e 0a 23 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74  ..#.do_execsql_t
1090: 65 73 74 20 6c 69 6b 65 33 2d 35 2e 31 30 30 20  est like3-5.100 
10a0: 7b 0a 20 20 43 52 45 41 54 45 20 54 41 42 4c 45  {.  CREATE TABLE
10b0: 20 74 35 61 28 78 20 49 4e 54 20 55 4e 49 51 55   t5a(x INT UNIQU
10c0: 45 20 43 4f 4c 4c 41 54 45 20 6e 6f 63 61 73 65  E COLLATE nocase
10d0: 29 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  );.  INSERT INTO
10e0: 20 74 35 61 28 78 29 20 56 41 4c 55 45 53 28 27   t5a(x) VALUES('
10f0: 2f 61 62 63 27 29 2c 28 31 32 33 29 2c 28 2d 32  /abc'),(123),(-2
1100: 33 34 29 3b 0a 20 20 53 45 4c 45 43 54 20 78 20  34);.  SELECT x 
1110: 46 52 4f 4d 20 74 35 61 20 57 48 45 52 45 20 78  FROM t5a WHERE x
1120: 20 4c 49 4b 45 20 27 2f 25 27 3b 0a 7d 20 7b 2f   LIKE '/%';.} {/
1130: 61 62 63 7d 0a 64 6f 5f 65 71 70 5f 74 65 73 74  abc}.do_eqp_test
1140: 20 6c 69 6b 65 33 2d 35 2e 31 30 31 20 7b 0a 20   like3-5.101 {. 
1150: 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74   SELECT x FROM t
1160: 35 61 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  5a WHERE x LIKE 
1170: 27 2f 25 27 3b 0a 7d 20 7b 0a 20 20 51 55 45 52  '/%';.} {.  QUER
1180: 59 20 50 4c 41 4e 0a 20 20 60 2d 2d 53 43 41 4e  Y PLAN.  `--SCAN
1190: 20 54 41 42 4c 45 20 74 35 61 0a 7d 0a 64 6f 5f   TABLE t5a.}.do_
11a0: 65 78 65 63 73 71 6c 5f 74 65 73 74 20 6c 69 6b  execsql_test lik
11b0: 65 33 2d 35 2e 31 31 30 20 7b 0a 20 20 53 45 4c  e3-5.110 {.  SEL
11c0: 45 43 54 20 78 20 46 52 4f 4d 20 74 35 61 20 57  ECT x FROM t5a W
11d0: 48 45 52 45 20 78 20 4c 49 4b 45 20 27 2f 61 25  HERE x LIKE '/a%
11e0: 27 3b 0a 7d 20 7b 2f 61 62 63 7d 0a 64 6f 5f 65  ';.} {/abc}.do_e
11f0: 71 70 5f 74 65 73 74 20 6c 69 6b 65 33 2d 35 2e  qp_test like3-5.
1200: 31 31 31 20 7b 0a 20 20 53 45 4c 45 43 54 20 78  111 {.  SELECT x
1210: 20 46 52 4f 4d 20 74 35 61 20 57 48 45 52 45 20   FROM t5a WHERE 
1220: 78 20 4c 49 4b 45 20 27 2f 61 25 27 3b 0a 7d 20  x LIKE '/a%';.} 
1230: 7b 0a 20 20 51 55 45 52 59 20 50 4c 41 4e 0a 20  {.  QUERY PLAN. 
1240: 20 60 2d 2d 53 45 41 52 43 48 20 54 41 42 4c 45   `--SEARCH TABLE
1250: 20 74 35 61 20 55 53 49 4e 47 20 43 4f 56 45 52   t5a USING COVER
1260: 49 4e 47 20 49 4e 44 45 58 20 73 71 6c 69 74 65  ING INDEX sqlite
1270: 5f 61 75 74 6f 69 6e 64 65 78 5f 74 35 61 5f 31  _autoindex_t5a_1
1280: 20 28 78 3e 3f 20 41 4e 44 20 78 3c 3f 29 0a 7d   (x>? AND x<?).}
1290: 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74  .do_execsql_test
12a0: 20 6c 69 6b 65 33 2d 35 2e 31 32 30 20 7b 0a 20   like3-5.120 {. 
12b0: 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74   SELECT x FROM t
12c0: 35 61 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20  5a WHERE x LIKE 
12d0: 27 5e 31 32 25 27 20 45 53 43 41 50 45 20 27 5e  '^12%' ESCAPE '^
12e0: 27 3b 0a 7d 20 7b 31 32 33 7d 0a 64 6f 5f 65 71  ';.} {123}.do_eq
12f0: 70 5f 74 65 73 74 20 6c 69 6b 65 33 2d 35 2e 31  p_test like3-5.1
1300: 32 31 20 7b 0a 20 20 53 45 4c 45 43 54 20 78 20  21 {.  SELECT x 
1310: 46 52 4f 4d 20 74 35 61 20 57 48 45 52 45 20 78  FROM t5a WHERE x
1320: 20 4c 49 4b 45 20 27 5e 31 32 25 27 20 45 53 43   LIKE '^12%' ESC
1330: 41 50 45 20 27 5e 27 3b 0a 7d 20 7b 0a 20 20 51  APE '^';.} {.  Q
1340: 55 45 52 59 20 50 4c 41 4e 0a 20 20 60 2d 2d 53  UERY PLAN.  `--S
1350: 43 41 4e 20 54 41 42 4c 45 20 74 35 61 0a 7d 0a  CAN TABLE t5a.}.
1360: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
1370: 6c 69 6b 65 33 2d 35 2e 31 32 32 20 7b 0a 20 20  like3-5.122 {.  
1380: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 35  SELECT x FROM t5
1390: 61 20 57 48 45 52 45 20 78 20 4c 49 4b 45 20 27  a WHERE x LIKE '
13a0: 5e 2d 32 25 27 20 45 53 43 41 50 45 20 27 5e 27  ^-2%' ESCAPE '^'
13b0: 3b 0a 7d 20 7b 2d 32 33 34 7d 0a 64 6f 5f 65 71  ;.} {-234}.do_eq
13c0: 70 5f 74 65 73 74 20 6c 69 6b 65 33 2d 35 2e 31  p_test like3-5.1
13d0: 32 33 20 7b 0a 20 20 53 45 4c 45 43 54 20 78 20  23 {.  SELECT x 
13e0: 46 52 4f 4d 20 74 35 61 20 57 48 45 52 45 20 78  FROM t5a WHERE x
13f0: 20 4c 49 4b 45 20 27 5e 31 32 25 27 20 45 53 43   LIKE '^12%' ESC
1400: 41 50 45 20 27 5e 27 3b 0a 7d 20 7b 0a 20 20 51  APE '^';.} {.  Q
1410: 55 45 52 59 20 50 4c 41 4e 0a 20 20 60 2d 2d 53  UERY PLAN.  `--S
1420: 43 41 4e 20 54 41 42 4c 45 20 74 35 61 0a 7d 0a  CAN TABLE t5a.}.
1430: 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74  .do_execsql_test
1440: 20 6c 69 6b 65 33 2d 35 2e 32 30 30 20 7b 0a 20   like3-5.200 {. 
1450: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 35   CREATE TABLE t5
1460: 62 28 78 20 49 4e 54 20 55 4e 49 51 55 45 20 43  b(x INT UNIQUE C
1470: 4f 4c 4c 41 54 45 20 62 69 6e 61 72 79 29 3b 0a  OLLATE binary);.
1480: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 35    INSERT INTO t5
1490: 62 28 78 29 20 56 41 4c 55 45 53 28 27 2f 61 62  b(x) VALUES('/ab
14a0: 63 27 29 2c 28 31 32 33 29 2c 28 2d 32 33 34 29  c'),(123),(-234)
14b0: 3b 0a 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f  ;.  SELECT x FRO
14c0: 4d 20 74 35 62 20 57 48 45 52 45 20 78 20 47 4c  M t5b WHERE x GL
14d0: 4f 42 20 27 2f 2a 27 3b 0a 7d 20 7b 2f 61 62 63  OB '/*';.} {/abc
14e0: 7d 0a 64 6f 5f 65 71 70 5f 74 65 73 74 20 6c 69  }.do_eqp_test li
14f0: 6b 65 33 2d 35 2e 32 30 31 20 7b 0a 20 20 53 45  ke3-5.201 {.  SE
1500: 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 35 62 20  LECT x FROM t5b 
1510: 57 48 45 52 45 20 78 20 47 4c 4f 42 20 27 2f 2a  WHERE x GLOB '/*
1520: 27 3b 0a 7d 20 7b 0a 20 20 51 55 45 52 59 20 50  ';.} {.  QUERY P
1530: 4c 41 4e 0a 20 20 60 2d 2d 53 43 41 4e 20 54 41  LAN.  `--SCAN TA
1540: 42 4c 45 20 74 35 62 0a 7d 0a 64 6f 5f 65 78 65  BLE t5b.}.do_exe
1550: 63 73 71 6c 5f 74 65 73 74 20 6c 69 6b 65 33 2d  csql_test like3-
1560: 35 2e 32 31 30 20 7b 0a 20 20 53 45 4c 45 43 54  5.210 {.  SELECT
1570: 20 78 20 46 52 4f 4d 20 74 35 62 20 57 48 45 52   x FROM t5b WHER
1580: 45 20 78 20 47 4c 4f 42 20 27 2f 61 2a 27 3b 0a  E x GLOB '/a*';.
1590: 7d 20 7b 2f 61 62 63 7d 0a 64 6f 5f 65 71 70 5f  } {/abc}.do_eqp_
15a0: 74 65 73 74 20 6c 69 6b 65 33 2d 35 2e 32 31 31  test like3-5.211
15b0: 20 7b 0a 20 20 53 45 4c 45 43 54 20 78 20 46 52   {.  SELECT x FR
15c0: 4f 4d 20 74 35 62 20 57 48 45 52 45 20 78 20 47  OM t5b WHERE x G
15d0: 4c 4f 42 20 27 2f 61 2a 27 3b 0a 7d 20 7b 0a 20  LOB '/a*';.} {. 
15e0: 20 51 55 45 52 59 20 50 4c 41 4e 0a 20 20 60 2d   QUERY PLAN.  `-
15f0: 2d 53 45 41 52 43 48 20 54 41 42 4c 45 20 74 35  -SEARCH TABLE t5
1600: 62 20 55 53 49 4e 47 20 43 4f 56 45 52 49 4e 47  b USING COVERING
1610: 20 49 4e 44 45 58 20 73 71 6c 69 74 65 5f 61 75   INDEX sqlite_au
1620: 74 6f 69 6e 64 65 78 5f 74 35 62 5f 31 20 28 78  toindex_t5b_1 (x
1630: 3e 3f 20 41 4e 44 20 78 3c 3f 29 0a 7d 0a 0a 66  >? AND x<?).}..f
1640: 69 6e 69 73 68 5f 74 65 73 74 0a                 inish_test.