lead() with arrays - strange behaviour

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: lead() with arrays - strange behaviour
Date: 2019-08-08 09:06:11
Message-ID: bd1a59b7-0e3d-0a37-fe2d-f60e00dbb050@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Consider the following dummy table (this is a simplified example from a bigger query):

create table sample_data (id int, id_list int[]);
insert into sample_data (id, id_list)
values
(1, array[1,2,3]),
(2, array[2,3,4]),
(3, array[4,5,6]);

The following statement tries to find the overlapping values in id_list between the current row and the next row:

select id,
id_list,
lead(id_list) over (order by id) as next_list,
array(select unnest(id_list) intersect select unnest(lead(id_list) over (order by id))) as common_ids
from sample_data;

The above returns:

id | id_list | next_list | common_ids
---+---------+-----------+-----------
1 | {1,2,3} | {2,3,4} | {}
2 | {2,3,4} | {4,5,6} | {}
3 | {4,5,6} | | {}

The empty array for "common_ids" is obviously incorrect.

However, when the evaluation of the "next_list" is put into a derived table, then this works as expected:

select id, id_list, next_list,
array(select unnest(id_list) intersect select unnest(next_list)) as common_ids
from (
select id,
id_list,
lead(id_list) over (order by id) as next_list
from sample_data
) t

returns:

id | id_list | next_list | common_ids
---+---------+-----------+-----------
1 | {1,2,3} | {2,3,4} | {2,3}
2 | {2,3,4} | {4,5,6} | {4}
3 | {4,5,6} | | {}

This is with Postgres 11.4

Is this a bug or simply not supported?

It does work correctly with intarray's "intersect" operator:

select id,
id_list,
id_list & lead(id_list) over (order by id) as next_list
from sample_data;

However, the actual data uses a bigint, so intarray isn't an option.

Thomas


Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Clarke 2019-08-08 09:16:48 Re: Recomended front ends?
Previous Message Luca Ferrari 2019-08-08 08:19:59 Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?