The following bug has been logged online:
Bug reference: 4921
Logged by: Alan Pinstein
Email address: apinstein@mac.com
PostgreSQL version: 8.3.6
Operating system: linux/centos 5.3
Description: ltree @> ltree[] operator shouldn't fail if ltree[] is
empty
Details:
The following query:
select
feature_id,hierarchy,description,category,ok_community,ok_property,ok_land,o
k_structure,ok_level,ok_room,
(select count(*) from feature where f.hierarchy =
subpath(hierarchy,0,-1) and ok_property = true) as count
from
feature f
where
ok_property = true and hierarchy @> ARRAY(select hierarchy from
feature where description ilike '%pool%this%') and nlevel(hierarchy) = 1
order by hierarchy asc
NOTES:
- hierarchy is an ltree in the feature table
- this query finds all root items in tree which contain any nodes whose
description matches "%pool%this%"
- the subquery returns 0 rows (there are no matching items in the error case
being reported)
EXPECTED BEHAVIOR:
- return 0 rows
ACTUAL BEHAVIOR:
ERROR: array must be one-dimensional
Possibly from:
https://projects.commandprompt.com/public/replicator/browser/trunk/contrib/l
tree/_ltree_op.c?rev=1905 line 46
NOTES:
This query worked in 8.1.x and started failing in 8.3.6 (only 2 versions I
tested).
I was able to hack around the issue with:
... hierarchy @> nullif(ARRAY(select hierarchy from feature where
description ilike '%pool%'),'{}') ...
Thank you very much for ltree, it rocks.
Feel free to contact me if you have further questions.
Alan