From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | manvendra2525(at)gmail(dot)com |
Subject: | BUG #16528: Analytical function with Over clause for ARRAY datatype is not working properly |
Date: | 2020-07-06 12:28:29 |
Message-ID: | 16528-8ddc2c3ee591a2bb@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16528
Logged by: Manvendra Panwar
Email address: manvendra2525(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-07-06 13:48:49 | Re: BUG #16528: Analytical function with Over clause for ARRAY datatype is not working properly |
Previous Message | Dmitry Dolgov | 2020-07-05 16:58:15 | Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table |