Skip site navigation (1) Skip section navigation (2)

AW: AW: Like vs '=' bug with indexing

From: Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at>
To: "'m w'" <mttf2000(at)yahoo(dot)com>
Cc: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: AW: AW: Like vs '=' bug with indexing
Date: 2001-01-31 13:07:09
Message-ID: 11C1E6749A55D411A9670001FA6879633681E7@sdexcsrv1.f000.d0188.sd.spardat.at (view raw or flat)
Thread:
Lists: pgsql-hackers
> > > I am reposting this because I'm not sure it actually
> > > made it to the list.
> > 
> > It did make it to the list, but can you give more
> > details whether or which of the 
> > following you use:
> > 	--enable-locale ?
> > 	--enable-multibyte ?
> > 	LANG=?
> > 	LC_COLLATE=?
> 
> I did not change any of the language or local
> information. My config like is:
> 
> ./configure --with-tcl --with-odbc
> --with-maxbackends=128 --prefix=/usr/local/pgsql 
> 
> > 
> > > I have a function to transform text into a
> > > pseudo-metaphone variable, take this example:
> > 
> > > Here is the problem: Depending on whether there is
> > an
> > > index or not, 'like' behaves differently. Here is
> > a
> > > transcript:
> > > 
> > > cddbsql=# select song, metatext(song) from cdsongs
> > > where metatext(song) like metatext('born to run')
> > > limit 1 ;
> > >     song     | metatext
> > > -------------+----------
> > >  Born To Run | brntorn
> > > (1 row)
> > >  
> > > cddbsql=# create index cdsongs_meta_song on
> > cdsongs
> > > (metatext(song)) ;
> > > CREATE
> > > cddbsql=# select song, metatext(song) from cdsongs
> > > where metatext(song) like metatext('born to run')
> > > limit 1 ;
> > >  song | metatext
> > > ------+----------
> > > (0
> > > rows)
> > 
> > While I do see, that this is bogous, I do not really understand why you 
> > use like in this case when your metatext function does not return any 
> > wildcards. A simple = should lead to the same result. Can you check that
> > with the index in place ? 
> 
> Oddly enough when I use '=' it works, but adding that
> means I have to special case when someone adds '%' at
> the end of the word, which means I will have to parse
> the string comming in. If I add the '%' sign to all
> queries, then I will not get the results intended.

Ah, I wonder wether it might be trailing blanks, that are involved here.
Is your return type of metatext() sql type text ? text and varchar are trailing
blank sensitive. Still sounds strange, that the seq scan ind index scan behaviors 
are different.

> > > This happens in both 7.0 and 7.1.

Responses

pgsql-hackers by date

Next:From: m wDate: 2001-01-31 13:18:27
Subject: Re: AW: AW: Like vs '=' bug with indexing
Previous:From: Magnus HaganderDate: 2001-01-31 10:18:55
Subject: RE: 7.1 question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group