From: | "Alan Pinstein" <apinstein(at)mac(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4921: ltree @> ltree[] operator shouldn't fail if ltree[] is empty |
Date: | 2009-07-15 04:54:00 |
Message-ID: | 200907150454.n6F4s0Hp085163@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4921
Logged by: Alan Pinstein
Email address: apinstein(at)mac(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Marek Lewczuk | 2009-07-15 10:08:49 | Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4 |
Previous Message | Tom Lane | 2009-07-14 16:54:17 | Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4 |