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

Like vs '=' bug with indexing

From: m w <mttf2000(at)yahoo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Like vs '=' bug with indexing
Date: 2001-01-30 18:48:14
Message-ID: 20010130184814.81151.qmail@web12407.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I am reposting this because I'm not sure it actually
made it to the list.


I have a function to transform text into a
pseudo-metaphone variable, take this example:

cddbsql=# select song, metatext(song) from cdsongs
where metatext(song) like metatext('born to run')
limit 3 ;
    song     | metatext
-------------+----------
 Born To Run | brntorn
 Born To Run | brntorn
 Born To Run | brntorn
(3 rows)

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)
      

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/

Responses

pgsql-hackers by date

Next:From: Rod TaylorDate: 2001-01-30 19:24:23
Subject: pg_shadow overusage
Previous:From: Stephan SzaboDate: 2001-01-30 18:19:17
Subject: Re: 7.1beta4 bug creating a certain table

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