Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Marek LewczukDate: 2009-07-15 10:08:49
Subject: Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Previous:From: Tom LaneDate: 2009-07-14 16:54:17
Subject: Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group