| 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: | Whole Thread | Raw Message | 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
	
              
| 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? |