Thread

1. Re: BUG #14512: Backslashes in LIKE

   Alex Malek <magicagent@gmail.com> — 2017-03-17T18:16:20Z

   Given the current behavior the same query will work or raise on error based
   on context.
   That is pretty confusing.
   
   Consider:
   
    foo=> CREATE TABLE bar (a varchar);
   CREATE TABLE
   foo=> SELECT * FROM bar WHERE a LIKE 'e\';
    a
   ---
   (0 rows)
   
   foo=> INSERT INTO bar VALUES ('e');
   INSERT 0 1
   foo=> SELECT * FROM bar WHERE a LIKE 'e\';
    a
   ---
   (0 rows)
   
   foo=> INSERT INTO bar VALUES ('ee');
   INSERT 0 1
   foo=> SELECT * FROM bar WHERE a LIKE 'e\';
   ERROR:  LIKE pattern must not end with escape character
   

2. Re: BUG #14512: Backslashes in LIKE

   David G. Johnston <david.g.johnston@gmail.com> — 2017-03-17T18:25:26Z

   On Fri, Mar 17, 2017 at 11:16 AM, Alex Malek <magicagent@gmail.com> wrote:
   
   >
   > Given the current behavior the same query will work or raise on error
   > based on context.
   > That is pretty confusing.
   >
   >
   ​Recently discussed here:
   
   https://www.postgresql.org/message-id/flat/20170124172505.1431.56735%40wrigleys.postgresql.org#20170124172505.1431.56735@wrigleys.postgresql.org
   ​
   In short - preventing a "fails-to-fail" scenario here doesn't seem worthy
   of the effort and run-time cost doing so would entail.
   
   David J.
   

3. Re: BUG #14512: Backslashes in LIKE

   Tom Lane <tgl@sss.pgh.pa.us> — 2017-03-17T20:04:45Z

   "David G. Johnston" <david.g.johnston@gmail.com> writes:
   > On Fri, Mar 17, 2017 at 11:16 AM, Alex Malek <magicagent@gmail.com> wrote:
   >> Given the current behavior the same query will work or raise on error
   >> based on context.
   
   > Recently discussed here:
   > https://www.postgresql.org/message-id/flat/20170124172505.1431.56735%40wrigleys.postgresql.org#20170124172505.1431.56735@wrigleys.postgresql.org
   > In short - preventing a "fails-to-fail" scenario here doesn't seem worthy
   > of the effort and run-time cost doing so would entail.
   
   BTW, looking again at the patch I suggested in
   https://www.postgresql.org/message-id/10287.1485286334%40sss.pgh.pa.us
   it strikes me that the check logic was unnecessarily stupid.  What
   we need to check is that there's not an odd number of backslashes at
   the end of the pattern.  Since we know that backend encodings are
   ASCII-safe, the test logic could be changed to scan backwards,
   something like
   
   	p += plen;
   	nbackslash = 0;
   	while (plen-- > 0)
   	{
   	    if (*(--p) == '\\')
   	        nbackslash++;
   	    else
   		break;
   	}
   	if (nbackslash & 1)
   		ereport(ERROR, ...);
   
   For patterns of practical interest this would be of small and nearly
   constant cost.  Maybe it is worth doing, especially since we've now
   had two independent complaints about it.
   
   			regards, tom lane