CC'd to the PostgreSQL Hackers list.

I performed the same test as Brian on the cvs version of 6.4 and it
exhibits the same behavior.  could we get a fix in for the release.
		-DEJ

> Hehehe... 
> 
> Try using the "*", as I posted to your early send... see if it makes
> any
> difference.
Doing a like '*' will search for the character '*';

> > er sorry about that last "early send" message...
> > 
> > well, at first I though you were correct, but it turns out that
> > postgresql
> > is also inconsistent. Consider a table with a field username. 3
> records:
> > 
> > "Brian % Schaffner"
> > "Brian T Schaffner"
> > "%"
> > 
> > select * from table where username='%' gets all rows (expected)
> > select * from table where username='%%' gets the row with "%"
> (expected)
> > select * from table where username='%%%' gets the row with "%"
> > (expected)
> > select * from table where username='%%%%' gets no rows (expected)
> > select * from table where username='% %' gets the 2 name rows
> (expected)
> > select * from table where username='% %%' gets the 2 name rows (NOT
> > expected)
> > select * from table where username='%% %' gets no rows (NOT
> expected)
> > select * from table where username='% % %' gets the 2 name rows
> > (expected)
> > select * from table where username='% %% %' gets the 2 name rows
> (NOT
> > expected)
> > select * from table where username='% % % %' gets no rows (expected)
> > 
> > so, if %% is the LIKE representation for a literal %, then why does
> '%
> > %%' return
> > the 2 name rows, and '%% %' return no rows, and '% %% %' not return
> the
> > row with the
> > embedded literal %?
> > 
> > I could not get postgres to single out the row with the embedded %
> using
> > LIKE.
> > 
> > why is this not getting any easier to define?
> > 
> > -----Original Message-----
> > From: Daniel J. Lashua [mailto:djl@stftx9.irngtx.tel.gte.com]
> > Sent: Friday, September 25, 1998 10:58 AM
> > To: Brian Schaffner
> > Cc: 'Rasmus Lerdorf'; 'bourbon@bourbon.netvision.net.il';
> > php3@lists.php.net
> > Subject: RE: [PHP3] ARGH!! strstr() changed?
> > 
> > 
> > On Fri, 25 Sep 1998, Brian Schaffner wrote:
> > 
> > > in PostgreSQL (6.3.2 on FreeBSD 2.2.6):
> > > 
> > > select * from table where field like '%%';
> > > 
> > > returns NO rows;
> > > 
> > > select * from table where field like '%';
> > > 
> > > returns ALL rows;
> > > 
> > > -brian-
> > > 
> > 
> > 
> > I am not in any way doubting what you say... but that doesn't seem
> > right.
> > They should both reuturn all rows. Maybe in Postgres %% is the way
> to
> > state you actually want to search for ONE "%"?
> > 
> > Out of curiosity, if you have time, could you test that. Make a
> table
> > with
> > a field and in one row of the table in the field insert "%". Then do
> > your
> > select * from table where somefield LIKE '%%' and see if it returns
> the
> > one row?
> > 
> > Am I offbase, or does that sound like incorrect behavior to you too?
> > 
> > Daniel
> > 
> > 
> 
> 
> --
> PHP 3 Mailing List   http://www.php.net/
> To unsubscribe send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest list:  php3-digest-subscribe@lists.php.net
> For help: php3-help@lists.php.net  Archive:
> http://www.php.net/mailsearch.php3
> 

Fri, 25 Sep 1998 13:55:51 -0500
"Jackson, DeJuan" <djackson@cpsgroup.com>
RE: [PHP3] [OFF-TOPIC] POSTGRES
I am just getting to this message.   I have overhauled the LIKE handling
for the upcoming 6.5 beta(soon to start).

I am attaching a patch that represents my changes.

FYI, the SQL standard say LIKE '%%' is the same as LIKE '%'.  To match a
literal '%', you have to do '\%'.

Let me know if you see any LIKE problems.  Thanks.


> CC'd to the PostgreSQL Hackers list.
> 
> I performed the same test as Brian on the cvs version of 6.4 and it
> exhibits the same behavior.  could we get a fix in for the release.
> 		-DEJ
> 
> > Hehehe... 
> > 
> > Try using the "*", as I posted to your early send... see if it makes
> > any
> > difference.
> Doing a like '*' will search for the character '*';
> 
> > > er sorry about that last "early send" message...
> > > 
> > > well, at first I though you were correct, but it turns out that
> > > postgresql
> > > is also inconsistent. Consider a table with a field username. 3
> > records:
> > > 
> > > "Brian % Schaffner"
> > > "Brian T Schaffner"
> > > "%"
> > > 
> > > select * from table where username='%' gets all rows (expected)
> > > select * from table where username='%%' gets the row with "%"
> > (expected)
> > > select * from table where username='%%%' gets the row with "%"
> > > (expected)
> > > select * from table where username='%%%%' gets no rows (expected)
> > > select * from table where username='% %' gets the 2 name rows
> > (expected)
> > > select * from table where username='% %%' gets the 2 name rows (NOT
> > > expected)
> > > select * from table where username='%% %' gets no rows (NOT
> > expected)
> > > select * from table where username='% % %' gets the 2 name rows
> > > (expected)
> > > select * from table where username='% %% %' gets the 2 name rows
> > (NOT
> > > expected)
> > > select * from table where username='% % % %' gets no rows (expected)
> > > 
> > > so, if %% is the LIKE representation for a literal %, then why does
> > '%
> > > %%' return
> > > the 2 name rows, and '%% %' return no rows, and '% %% %' not return
> > the
> > > row with the
> > > embedded literal %?
> > > 
> > > I could not get postgres to single out the row with the embedded %
> > using
> > > LIKE.
> > > 
> > > why is this not getting any easier to define?
> > > 
> > > -----Original Message-----
> > > From: Daniel J. Lashua [mailto:djl@stftx9.irngtx.tel.gte.com]
> > > Sent: Friday, September 25, 1998 10:58 AM
> > > To: Brian Schaffner
> > > Cc: 'Rasmus Lerdorf'; 'bourbon@bourbon.netvision.net.il';
> > > php3@lists.php.net
> > > Subject: RE: [PHP3] ARGH!! strstr() changed?
> > > 
> > > 
> > > On Fri, 25 Sep 1998, Brian Schaffner wrote:
> > > 
> > > > in PostgreSQL (6.3.2 on FreeBSD 2.2.6):
> > > > 
> > > > select * from table where field like '%%';
> > > > 
> > > > returns NO rows;
> > > > 
> > > > select * from table where field like '%';
> > > > 
> > > > returns ALL rows;
> > > > 
> > > > -brian-
> > > > 
> > > 
> > > 
> > > I am not in any way doubting what you say... but that doesn't seem
> > > right.
> > > They should both reuturn all rows. Maybe in Postgres %% is the way
> > to
> > > state you actually want to search for ONE "%"?
> > > 
> > > Out of curiosity, if you have time, could you test that. Make a
> > table
> > > with
> > > a field and in one row of the table in the field insert "%". Then do
> > > your
> > > select * from table where somefield LIKE '%%' and see if it returns
> > the
> > > one row?
> > > 
> > > Am I offbase, or does that sound like incorrect behavior to you too?
> > > 
> > > Daniel
> > > 
> > > 
> > 
> > 
> > --
> > PHP 3 Mailing List   http://www.php.net/
> > To unsubscribe send an empty message to php3-unsubscribe@lists.php.net
> > To subscribe to the digest list:  php3-digest-subscribe@lists.php.net
> > For help: php3-help@lists.php.net  Archive:
> > http://www.php.net/mailsearch.php3
> > 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: like.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/utils/adt/like.c,v
retrieving revision 1.22
retrieving revision 1.23
diff -c -r1.22 -r1.23
*** like.c	1999/03/15 02:18:36	1.22
--- like.c	1999/03/15 13:45:09	1.23
***************
*** 150,159 ****
  {
  	int			matched;
  
! 	for (; *p; text ++, p++)
  	{
- 		if (*text == '\0' && *p != '%')
- 			return LIKE_ABORT;
  		switch (*p)
  		{
  			case '\\':
--- 150,157 ----
  {
  	int			matched;
  
! 	for (; *p && *text; text++, p++)
  	{
  		switch (*p)
  		{
  			case '\\':
***************
*** 161,187 ****
  				p++;
  				/* FALLTHROUGH */
  			default:
! 				if (*text !=*p)
  					return LIKE_FALSE;
! 				continue;
  			case '_':
  				/* Match anything. */
! 				continue;
  			case '%':
! 				while (*++p == '%')
! 					/* Consecutive percents act just like one. */
! 					continue;
  				if (*p == '\0')
  					/* Trailing percent matches everything. */
  					return LIKE_TRUE;
  				while (*text)
! 					if ((matched = DoMatch(text ++, p)) != LIKE_FALSE)
  						return matched;
  				return LIKE_ABORT;
  		}
  	}
  
! 	return *text == '\0';
  }
  
  
--- 159,201 ----
  				p++;
  				/* FALLTHROUGH */
  			default:
! 				if (*text != *p)
  					return LIKE_FALSE;
! 				break;
  			case '_':
  				/* Match anything. */
! 				break;
  			case '%':
! 				/* %% is the same as % according to the SQL standard */
! 				/* Advance past all %'s */
! 				while (*p == '%')
! 					p++;
  				if (*p == '\0')
  					/* Trailing percent matches everything. */
  					return LIKE_TRUE;
  				while (*text)
! 				{
! 					/* Optimization to prevent most recursion */
! 					if ((*text == *p ||
! 					     *p == '\\' || *p == '%' || *p == '_') &&
! 						(matched = DoMatch(text, p)) != LIKE_FALSE)
  						return matched;
+ 					text++;
+ 				}
  				return LIKE_ABORT;
  		}
  	}
  
! 	if (*text != '\0')
! 		return LIKE_ABORT;
! 	else
! 	{
! 		/* End of input string.  Do we have matching string remaining? */
! 		if (p[0] == '\0' || (p[0] == '%' && p[1] == '\0'))
! 			return LIKE_TRUE;
! 		else
! 			return LIKE_ABORT;
! 	}
  }
Mon, 15 Mar 1999 08:48:17 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RE: [PHP3] [OFF-TOPIC] POSTGRES