Re: lead() with arrays - strange behaviour

Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> David Rowley schrieb am 08.08.2019 um 13:03:
>> 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.

> id_list is a column in the table and as you can see in the output
> lead(id_list) most definitely returns the array from the next row.
> and "select unnest(some_array)" works just fine as you can see
> when "next_list" is taken from the derived table.

David's point is that the two occurrences of lead() don't mean the
same thing. A window function is directly tied to the SELECT that
it is in the select-list of, and its notion of next and previous
rows is concerned with the set of rows that that SELECT's FROM-clause
generates. In this example, the inner SELECT has an empty FROM that
returns one row, so the lead() in that SELECT doesn't do anything
useful.

You could probably get where you want to go with something along
the lines of

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
) ss;

regards, tom lane