Chagnes the ESCAPE clause on the LIKE operator to overwrite wildcard

characters, in order ot match the behavior of PosgreSQL.

FossilOrigin-Name: 11e0844f71e8f2d27ce9363fb505e02fd7795c61dae0b3886cf0d8df4484dd97

7 files changed, 72 insertions, 25 deletions

diff --git a/ext/icu/icu.c b/ext/icu/icu.c
index 7fbe32a7e..92d7c5438 100644
--- a/ext/icu/icu.c
+++ b/ext/icu/icu.c
@@ -143,7 +143,7 @@ static int icuLikeCompare(
     **     3. uPattern is an unescaped escape character, or
     **     4. uPattern is to be handled as an ordinary character
     */
-    if( !prevEscape && uPattern==MATCH_ALL ){
+    if( uPattern==MATCH_ALL && !prevEscape && uPattern!=(uint32_t)uEsc ){
       /* Case 1. */
       uint8_t c;
 
@@ -169,12 +169,12 @@ static int icuLikeCompare(
       }
       return 0;
 
-    }else if( !prevEscape && uPattern==MATCH_ONE ){
+    }else if( uPattern==MATCH_ONE && !prevEscape && uPattern!=(uint32_t)uEsc ){
       /* Case 2. */
       if( *zString==0 ) return 0;
       SQLITE_ICU_SKIP_UTF8(zString);
 
-    }else if( !prevEscape && uPattern==(uint32_t)uEsc){
+    }else if( uPattern==(uint32_t)uEsc && !prevEscape ){
       /* Case 3. */
       prevEscape = 1;
 
diff --git a/manifest b/manifest
index f57846354..145549d62 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\san\sinteger\soverflow\sproblem\swith\sthe\sdbstat\svirtual\stable\sthat\scomes\sup\nwhen\strying\sto\sanalyze\sa\scorrupt\sdatabase.
-D 2020-03-19T17:27:52.087
+C Chagnes\sthe\sESCAPE\sclause\son\sthe\sLIKE\soperator\sto\soverwrite\swildcard\ncharacters,\sin\sorder\sot\smatch\sthe\sbehavior\sof\sPosgreSQL.
+D 2020-03-19T18:13:29.000
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -231,7 +231,7 @@ F ext/fts5/tool/loadfts5.tcl 95b03429ee6b138645703c6ca192c3ac96eaf093
 F ext/fts5/tool/mkfts5c.tcl d1c2a9ab8e0ec690a52316f33dd9b1d379942f45
 F ext/fts5/tool/showfts5.tcl d54da0e067306663e2d5d523965ca487698e722c
 F ext/icu/README.txt a295e91db742b153e8dce8f7efd31d28ad1eea4df31ef4daa3eedc85be2f5138
-F ext/icu/icu.c 7adfe8a72dd4f54b47684dc9b88523399c6ef119d733b73e17371445f7428dd1
+F ext/icu/icu.c 91c021c7e3e8bbba286960810fa303295c622e323567b2e6def4ce58e4466e60
 F ext/icu/sqliteicu.h 728867a802baa5a96de7495e9689a8e01715ef37
 F ext/lsm1/Makefile a553b728bba6c11201b795188c5708915cc4290f02b7df6ba7e8c4c943fd5cd9
 F ext/lsm1/Makefile.msc f8c878b467232226de288da320e1ac71c131f5ec91e08b21f502303347260013
@@ -486,7 +486,7 @@ F src/delete.c 11000121c4281c0bce4e41db29addfaea0038eaa127ece02557c9207bc3e541d
 F src/expr.c ed718ee2206166c9c2fc4fe89eadb1f369318aeb8645e06033566b387970fb9a
 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
 F src/fkey.c 4b575423b0a5d4898b1a7868ce985cf1a8ad91c741c9abbb108ff02536d20f41
-F src/func.c 108577cebe8a50c86d849a93b99493a54e348dd0b846f00d13b52ca973d5baf4
+F src/func.c f3dcdc0e95509864767c1f0991b19360f969e44177f4e058fd51da9a6154f47e
 F src/global.c 79a988b56b06ce2d08ebefe1d35da9aa25b3851faa47ea5233361c4827185a64
 F src/hash.c 8d7dda241d0ebdafb6ffdeda3149a412d7df75102cecfc1021c98d6219823b19
 F src/hash.h 9d56a9079d523b648774c1784b74b89bd93fac7b365210157482e4319a468f38
@@ -1036,7 +1036,7 @@ F test/hexlit.test 4a6a5f46e3c65c4bf1fa06f5dd5a9507a5627751
 F test/hidden.test 23c1393a79e846d68fd902d72c85d5e5dcf98711
 F test/hook.test 1604b3b2f5931430087540404555c1b6be3618600b81558657c66b533ed70b13
 F test/hook2.test b9ff3b8c6519fb67f33192f1afe86e7782ee4ac8
-F test/icu.test 41aa8847745a879b897a7febea0f8f9efc8e67fe8bf680589b6e07c7b0a1569a
+F test/icu.test 716a6b89fbabe5cc63e0cd4c260befb08fd7b9d761f04d43669233292f0753b1
 F test/ieee754.test 806fc0ce7f305f57e3331eaceeddcfec9339e607
 F test/imposter1.test c3f1db2d3db2c24611a6596a3fc0ffc14f1466c8
 F test/in.test ae4ba0fe3232fdd84ef1090a68c5cd6ccd93f1f8774d5c967dd0c1b301492eed
@@ -1113,9 +1113,9 @@ F test/kvtest.c 94da54bb66aae7a54e47cf7e4ea4acecc0f217560f79ad3abfcc0361d6d557ba
 F test/lastinsert.test 42e948fd6442f07d60acbd15d33fb86473e0ef63
 F test/laststmtchanges.test ae613f53819206b3222771828d024154d51db200
 F test/lemon-test01.y 58b764610fd934e189ffbb0bbfa33d171b9cb06019b55bdc04d090d6767e11d7
-F test/like.test 3d702d79bf871fa32985b1ce334294c587e3948d3ab972001e811a58577e8b3c
+F test/like.test 47b81d5de2ff19d996d49a65d50ec9754246aacbe0e950b48d186d9d8171eaf0
 F test/like2.test 3b2ee13149ba4a8a60b59756f4e5d345573852da
-F test/like3.test 4f940ad275c006319950054a7a65661f476772171b82b6fdf795e4dda36f246f
+F test/like3.test 03d1bdf848483b78d2cfd1db283d75c4ec2e37c8b8eccc006813f3978d78fbbd
 F test/limit.test 0c99a27a87b14c646a9d583c7c89fd06c352663e
 F test/limit2.test 9409b033284642a859fafc95f29a5a6a557bd57c1f0d7c3f554bd64ed69df77e
 F test/loadext.test faa4f6eed07a5aac35d57fdd7bc07f8fc82464cfd327567c10cf0ba3c86cde04
@@ -1860,7 +1860,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 3a51919ac23ae7312d78905334dc97742b517a7476052a85aa07945d9c77697b
-R 11059e9f9145d47852beb0ef202844f6
+P 1d64f4a8af81fe1235fffa54884d8f842a48ff6a33d6172f0cd65bf42fe8b2a1
+R 199dfbc9c8ca72850da0099d6c1cde58
 U drh
-Z 6be6a8645f621862376d12081de1edc9
+Z 2c8ae1f04515bf2ea893e9cb3af9713c
diff --git a/manifest.uuid b/manifest.uuid
index 0081a7113..6dd6aeb2a 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-1d64f4a8af81fe1235fffa54884d8f842a48ff6a33d6172f0cd65bf42fe8b2a1
\ No newline at end of file
+11e0844f71e8f2d27ce9363fb505e02fd7795c61dae0b3886cf0d8df4484dd97
\ No newline at end of file
diff --git a/src/func.c b/src/func.c
index 966a0075e..167b6afd7 100644
--- a/src/func.c
+++ b/src/func.c
@@ -853,6 +853,7 @@ static void likeFunc(
   int nPat;
   sqlite3 *db = sqlite3_context_db_handle(context);
   struct compareInfo *pInfo = sqlite3_user_data(context);
+  struct compareInfo backupInfo;
 
 #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
   if( sqlite3_value_type(argv[0])==SQLITE_BLOB
@@ -888,6 +889,12 @@ static void likeFunc(
       return;
     }
     escape = sqlite3Utf8Read(&zEsc);
+    if( escape==pInfo->matchAll || escape==pInfo->matchOne ){
+      memcpy(&backupInfo, pInfo, sizeof(backupInfo));
+      pInfo = &backupInfo;
+      if( escape==pInfo->matchAll ) pInfo->matchAll = 0;
+      if( escape==pInfo->matchOne ) pInfo->matchOne = 0;
+    }
   }else{
     escape = pInfo->matchSet;
   }
@@ -1870,6 +1877,16 @@ int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
   if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
     return 0;
   }
+
+  /* The memcpy() statement assumes that the wildcard characters are
+  ** the first three statements in the compareInfo structure.  The
+  ** asserts() that follow verify that assumption
+  */
+  memcpy(aWc, pDef->pUserData, 3);
+  assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
+  assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
+  assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
+
   if( nExpr<3 ){
     aWc[3] = 0;
   }else{
@@ -1878,17 +1895,11 @@ int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
     if( pEscape->op!=TK_STRING ) return 0;
     zEscape = pEscape->u.zToken;
     if( zEscape[0]==0 || zEscape[1]!=0 ) return 0;
+    if( zEscape[0]==aWc[0] ) return 0;
+    if( zEscape[0]==aWc[1] ) return 0;
     aWc[3] = zEscape[0];
   }
 
-  /* The memcpy() statement assumes that the wildcard characters are
-  ** the first three statements in the compareInfo structure.  The
-  ** asserts() that follow verify that assumption
-  */
-  memcpy(aWc, pDef->pUserData, 3);
-  assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
-  assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
-  assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
   *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
   return 1;
 }
diff --git a/test/icu.test b/test/icu.test
index 4c4e6d14e..644cbb1f0 100644
--- a/test/icu.test
+++ b/test/icu.test
@@ -146,4 +146,22 @@ ifcapable icu {
   }
 }
 
+# 2020-03-19
+# The ESCAPE clause on LIKE takes precedence over wildcards
+#
+do_execsql_test idu-6.0 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT);
+  INSERT INTO t1 VALUES
+    (1,'abcde'),
+    (2,'abc_'),
+    (3,'abc__'),
+    (4,'abc%'),
+    (5,'abc%%');
+  SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%';
+} {4}
+do_execsql_test icu-6.1 {
+  SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_';
+} {2}
+
 finish_test
diff --git a/test/like.test b/test/like.test
index 0fc80254b..c29ebb267 100644
--- a/test/like.test
+++ b/test/like.test
@@ -1113,4 +1113,22 @@ do_execsql_test 16.2 {
   SELECT * FROM t1 WHERE a LIKE ' 1-';
 } {{ 1-}}
 
+# 2020-03-19
+# The ESCAPE clause on LIKE takes precedence over wildcards
+#
+do_execsql_test 17.0 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT);
+  INSERT INTO t1 VALUES
+    (1,'abcde'),
+    (2,'abc_'),
+    (3,'abc__'),
+    (4,'abc%'),
+    (5,'abc%%');
+  SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%';
+} {4}
+do_execsql_test 17.1 {
+  SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_';
+} {2}
+
 finish_test
diff --git a/test/like3.test b/test/like3.test
index 07053155e..3bfe30c31 100644
--- a/test/like3.test
+++ b/test/like3.test
@@ -237,7 +237,7 @@ do_eqp_test like3-6.110 {
   `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
 }
 do_eqp_test like3-6.120 {
-  SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE '_';
+  SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x';
 } {
   QUERY PLAN
   `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
@@ -255,7 +255,7 @@ do_eqp_test like3-6.210 {
   `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
 }
 do_eqp_test like3-6.220 {
-  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '_';
+  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
 } {
   QUERY PLAN
   `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
@@ -268,7 +268,7 @@ do_eqp_test like3-6.230 {
   `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)
 }
 do_eqp_test like3-6.240 {
-  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '_';
+  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
 } {
   QUERY PLAN
   `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)