Re: lead() with arrays - strange behaviour

On Thu, 8 Aug 2019 at 21:06, Thomas Kellerer <spam_eater@gmx.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