<span id="intelliTxt">Hi,<br /><br /> I have come across a weird bug (i think) in postgres 8.1.11 (possibly others)<br
/><br/> Without going into my table structure detail I will demonstrate the problem by showing the select
statements:<br/><br /> The following statement:<br /> SELECT count(*)<br /> FROM object o, object_version v,
object_typeot <br /> 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) <br /> and o.is_published = 't' and ot.object_type_typeid <> 1 <br
/><br/> and exists (<br /> select ova.object_versionid from attribute_value av, object_version_attribute ova where
ova.attribute_valueid=av.idand object_versionid = v.id <br /> and (upper(av.text_val) like <b>'%KIWI%'</b>) )<br /><br
/><br/> runs fine and executes with success.<br /> BUT now this is the strange bit, if I have a space in my search term
thenpostgres hangs for an indefinite period: eg:<br /><br /> SELECT count(*)<br /> FROM object o, object_version v,
object_typeot <br /> 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) <br /> and o.is_published = 't' and ot.object_type_typeid <> 1 <br
/><br/> and exists (<br /> select ova.object_versionid from attribute_value av, object_version_attribute ova where
ova.attribute_valueid=av.idand object_versionid = v.id <br /> and (upper(av.text_val) like <b>'%KIWI FRUIT%'</b>) )<br
/><br/><br /> Yet, if I modify the "exists" to an "in" all works well , as follows<br /><br /> SELECT count(*)<br />
FROMobject o, object_version v, object_type ot <br /> 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) <br /> and o.is_published = 't' and
ot.object_type_typeid<> 1 <br /><br /> and v.id in (<br /> select ova.object_versionid from attribute_value av,
object_version_attributeova where ova.attribute_valueid=av.id <br /> and (upper(av.text_val) like <b>'%KIWI
FRUIT%'</b>))<br /><br /><br /> So my question is why would a space character cause postgres to hang when using the
existsclause????<br /><br /> 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.<br /><br /> An
upgradeto 8.3 fixes this, but I am still curious as to what could cause such bizarre behavior.<br /><br /> Thanks<br />
Hans</span>
