Re: AW: AW: Like vs '=' bug with indexing - Mailing list pgsql-hackers

From m w
Subject Re: AW: AW: Like vs '=' bug with indexing
Date
Msg-id 20010131131827.10840.qmail@web12404.mail.yahoo.com
Whole thread Raw
In response to AW: AW: Like vs '=' bug with indexing  (Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>)
List pgsql-hackers
--- Zeugswetter Andreas SB
<ZeugswetterA@wien.spardat.at> wrote:
> 
> > > > 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.

That doesn't make sense, if there were trailing
blanks, '=' would not work. Also, since the same
function is being used on both ends, one presumes that
any trailing blanks would also be present on each. Why
would spaces kill a 'like' during and index scan, but
not during a table scan?


> 
> > > > This happens in both 7.0 and 7.1.


__________________________________________________
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/


pgsql-hackers by date:

Previous
From: Zeugswetter Andreas SB
Date:
Subject: AW: AW: Like vs '=' bug with indexing
Next
From: "Martin A. Marques"
Date:
Subject: Re: grant privileges to a database