Substring search using "exists" with a space in the search term
Substring search using "exists" with a space in the search term
От:
Hans Liebenberg <hans@cambrient.com>
Дата:
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