Hi,

I have come across a weird bug (i think) in postgres 8.1.11 (possibly others)

Without going into my table structure detail I will demonstrate the problem by showing the select statements:

The following statement:
SELECT count(*)
FROM object o, object_version v, object_type ot
where v.id = o.active_versionid and ot.id = o.object_typeid and o.is_active ='t' and (o.is_archived = 'f' or o.is_archived is null)
and o.is_published = 't' and ot.object_type_typeid <> 1

and exists (
select ova.object_versionid from attribute_value av, object_version_attribute ova where ova.attribute_valueid=av.id and object_versionid = v.id
and (upper(av.text_val) like '%KIWI%') )


runs fine and executes with success.
BUT now this is the strange bit, if I have a space in my search term then postgres hangs for an indefinite period: eg:

SELECT count(*)
FROM object o, object_version v, object_type ot
where v.id = o.active_versionid and ot.id = o.object_typeid and o.is_active ='t' and (o.is_archived = 'f' or o.is_archived is null)
and o.is_published = 't' and ot.object_type_typeid <> 1

and exists (
select ova.object_versionid from attribute_value av, object_version_attribute ova where ova.attribute_valueid=av.id and object_versionid = v.id
and (upper(av.text_val) like '%KIWI FRUIT%') )


Yet, if I modify the "exists" to an "in" all works well , as follows

SELECT count(*)
FROM object o, object_version v, object_type ot
where v.id = o.active_versionid and ot.id = o.object_typeid and o.is_active ='t' and (o.is_archived = 'f' or o.is_archived is null)
and o.is_published = 't' and ot.object_type_typeid <> 1

and v.id in (
select ova.object_versionid from attribute_value av, object_version_attribute ova where ova.attribute_valueid=av.id
and (upper(av.text_val) like '%KIWI FRUIT%') )


So my question is why would a space character cause postgres to hang when using the exists clause????

I have tested this on several different servers and mostly get the same result (v8.08 and v8.1.11) , when I check the execution plan for either query (space or no space) they are identical.

An upgrade to 8.3 fixes this, but I am still curious as to what could cause such bizarre behavior.

Thanks
Hans