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