Hi,
I tried DISTINCT MAX() on the 8.2 and 8.3 as below.
8.2.6
==============================
yamaguti=# create table t1 (i int, j int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t1_pkey" for table "t1" CREATE TABLE
yamaguti=# insert into t1 select g, g from generate_series(1, 100) as g;
INSERT 0 100
yamaguti=# select distinct max(i) from t1;
max
-----
100
(1 row)
yamaguti=# select distinct max(j) from t1;
max
-----
100
(1 row)
==============================
8.3.0
==============================
yamaguti=# create table t1 (i int, j int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t1_pkey" for table "t1" CREATE TABLE
yamaguti=# insert into t1 select g, g from generate_series(1, 100) as g;
INSERT 0 100
yamaguti=# select distinct max(i) from t1;
max
-----
100
(1 row)
yamaguti=# select distinct max(j) from t1;
ERROR: could not find pathkey item to sort
==============================
If a column has a primary-key constraint, DISTINCT MAX() fails on 8.3.
I know that the DISTINCT in this query has no meanings, but I wasn't
certain that this was an intended behaviour or not (a bug). If it was a
bug, would it be fixed in the future?
Best regards,
--
Taiki Yamaguchi
<yamaguchi@sraoss.co.jp>