BUG #4921: ltree @> ltree[] operator shouldn't fail if ltree[] is empty

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

Responses

Browse pgsql-bugs by date

  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