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

--- Zeugswetter Andreas SB
<ZeugswetterA(at)wien(dot)spardat(dot)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/