array_agg and/or =ANY doesn't appear to be functioning as I expect

From: "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: array_agg and/or =ANY doesn't appear to be functioning as I expect
Date: 2018-01-21 01:42:24
Message-ID: CACg0vT=6WT93R3Utbhtc0KetQCXpuJqi08nE_xm1Ag0iwsv2BQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings All,
I'm having an issue which is very perplexing. The having clause in a
query doesn't appear to be working as I expect it. Either that or my
understanding of array_agg() is flawed.

I'm using PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit.
The table in question looks like:

CREATE TABLE confounded.dataset
(
seq integer,
path_seq integer,
start_vid bigint,
end_vid bigint,
node bigint,
edge bigint,
cost double precision,
agg_cost double precision,
st_length double precision,
truth boolean
);

The following query returns 3 rows:

with listing as (
select start_vid, end_vid, array_agg(node order by path_seq)
node_array, array_agg(edge order by path_seq) edge_array
from confounded.dataset
group by start_vid,end_vid
having true =ALL (array_agg(truth))
)

select * from listing;

The issue is, if I unnest the node_array column from the listing cte
and do a subselect on confounded.dataset I get back rows where truth =
false.

with listing as (
select start_vid, end_vid, array_agg(node order by path_seq)
node_array, array_agg(edge order by path_seq) edge_array
from confounded.dataset
group by start_vid,end_vid
having true =ALL (array_agg(truth))
)
select count(*) from confounded.dataset
where node in (select distinct unnest(node_array) from listing) and
truth = false;

I would expect the above query to return 0 rows.

the dataset can be found at
https://gist.github.com/rhysallister/59239c76e8ec265b81777038bf272879

Rhys
Peace & Love|Live Long & Prosper

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Erven 2018-01-21 04:00:29 Re: array_agg and/or =ANY doesn't appear to be functioning as I expect
Previous Message Bjørn T Johansen 2018-01-20 15:20:40 Re: pgaudit for PostgreSQL 10?