Обсуждение: BUG #16528: Analytical function with Over clause for ARRAY datatype is not working properly

The following bug has been logged on the website:

Bug reference:      16528
Logged by:          Manvendra Panwar
Email address:      manvendra2525@gmail.com
PostgreSQL version: 11.8
Operating system:   Ubuntu 18.04.1 LTS
Description:

postgres=# create table array_order (a int[], b float[], c bool[]);
CREATE TABLE
postgres=# insert into array_order values
(array[935,181,326,103],array[38766.318,54516.453,9546.79,276.63],array[false,true]);
INSERT 0 1
postgres=# insert into array_order values
(array[935,181,326,103],array[38767.318,54517.453,9540.79,270.63],array[false,true]);
INSERT 0 1
postgres=# insert into array_order values
(array[935,181,326,104],array[38767.348,54517.443,9540.49,270.43],array[true]
     );
INSERT 0 1
postgres=# commit;

postgres=# select * from array_order;
         a         |                  b                   |   c
-------------------+--------------------------------------+-------
 {935,181,326,103} | {38766.318,54516.453,9546.79,276.63} | {f,t}
 {935,181,326,103} | {38767.318,54517.453,9540.79,270.63} | {f,t}
 {935,181,326,104} | {38767.348,54517.443,9540.49,270.43} | {t}
(3 rows)

-- WORKING FINE with ARRAY element
postgres=# SELECT a , b, RANK() OVER (PARTITION BY c[0] ORDER BY a) Rank,
DENSE_RANK() OVER (PARTITION BY c[0] ORDER BY a) "Dense Rank"  FROM
array_order order by 3;
         a         |                  b                   | rank | Dense
Rank
-------------------+--------------------------------------+------+------------
 {935,181,326,103} | {38766.318,54516.453,9546.79,276.63} |    1 |
1
 {935,181,326,103} | {38767.318,54517.453,9540.79,270.63} |    1 |
1
 {935,181,326,104} | {38767.348,54517.443,9540.49,270.43} |    3 |
2
(3 rows)

-- WITH ARRAY in PARTITION BY CLAUSE
postgres=# SELECT a , b, RANK() OVER (PARTITION BY a ORDER BY a) Rank,
DENSE_RANK() OVER (PARTITION BY a ORDER BY a) "Dense Rank"  FROM array_order
order by 3;
         a         |                  b                   | rank | Dense
Rank
-------------------+--------------------------------------+------+------------
 {935,181,326,103} | {38766.318,54516.453,9546.79,276.63} |    1 |
1
 {935,181,326,103} | {38767.318,54517.453,9540.79,270.63} |    1 |
1
 {935,181,326,104} | {38767.348,54517.443,9540.49,270.43} |    1 |
1
(3 rows)

PG Bug reporting form <noreply@postgresql.org> writes:
> -- WORKING FINE with ARRAY element
> postgres=# SELECT a , b, RANK() OVER (PARTITION BY c[0] ORDER BY a) Rank,
> DENSE_RANK() OVER (PARTITION BY c[0] ORDER BY a) "Dense Rank"  FROM
> array_order order by 3;

Note that "c[0]" is a constant NULL in this example, so all the
rows fall into the same partition.

> -- WITH ARRAY in PARTITION BY CLAUSE
> postgres=# SELECT a , b, RANK() OVER (PARTITION BY a ORDER BY a) Rank,
> DENSE_RANK() OVER (PARTITION BY a ORDER BY a) "Dense Rank"  FROM array_order
> order by 3;

In this case, rows with distinct "a" values belong to different
partitions.  Every row in a particular partition has the same "a"
value, so it's expected that they all have rank 1.

In short, I see no bug here.  "PARTITION BY a ORDER BY a" just
isn't a very useful window specification (independently of what
"a" is ...)

            regards, tom lane