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

> > > 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.