Re: lead() with arrays - strange behaviour

On Thu, 8 Aug 2019 at 21:06, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> 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.

I think you're confused with what the SELECT with the empty FROM
clause does here. In your subquery "id_list" is just a parameter from
the outer query. LEAD(id_list) OVER (ORDER BY id) is never going to
return anything since those are both just effectively scalar values,
to which there is no "next" value.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services