Re: DISTINCT MAX() results mismatch on 8.2 and 8.3

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(at)sraoss(dot)co(dot)jp>

Taiki Yamaguchi <yamaguchi(at)sraoss(dot)co(dot)jp> writes:
> 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?

It's certainly a bug and should be fixed ... I'm too tired to look into
the cause right now, though.

regards, tom lane

Taiki Yamaguchi <yamaguchi(at)sraoss(dot)co(dot)jp> writes:
> yamaguti=# select distinct max(j) from t1;
> ERROR: could not find pathkey item to sort

Fixed in HEAD and 8.3. Thanks for the report!

regards, tom lane

>>> On Wed, Mar 26, 2008 at 9:23 PM, in message
<20080327112308(dot)b1e9a692(dot)yamaguchi(at)sraoss(dot)co(dot)jp>, Taiki Yamaguchi
<yamaguchi(at)sraoss(dot)co(dot)jp> wrote:

> 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
> ==============================

For the benefit of anyone searching the archives for the problem we
just hit, this message also occurs in 8.3.1 and also occurs against
the above test table for this statement:

test=# select max(j) as "maxj" from t1 order by "maxj";
ERROR: could not find pathkey item to sort

Neither statement causes the error when run against a build from
REL8_3_STABLE from 35 minutes ago (2008-04-22 10:15 CDT).

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> yamaguti=# select distinct max(j) from t1;
>> ERROR: could not find pathkey item to sort

> For the benefit of anyone searching the archives for the problem we
> just hit, this message also occurs in 8.3.1 and also occurs against
> the above test table for this statement:

> test=# select max(j) as "maxj" from t1 order by "maxj";
> ERROR: could not find pathkey item to sort

> Neither statement causes the error when run against a build from
> REL8_3_STABLE from 35 minutes ago (2008-04-22 10:15 CDT).

Yeah, this was repaired here:
http://archives.postgresql.org/pgsql-committers/2008-03/msg00563.php

The fix will be in 8.3.2.

regards, tom lane