Skip site navigation (1) Skip section navigation (2)

DISTINCT MAX() results mismatch on 8.2 and 8.3

From: Taiki Yamaguchi <yamaguchi(at)sraoss(dot)co(dot)jp>
To: pgsql-bugs(at)postgresql(dot)org
Subject: DISTINCT MAX() results mismatch on 8.2 and 8.3
Date: 2008-03-27 02:23:08
Message-ID: 20080327112308.b1e9a692.yamaguchi@sraoss.co.jp (view raw or flat)
Thread:
Lists: pgsql-bugs
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>

Responses

pgsql-bugs by date

Next:From: Nahum CastroDate: 2008-03-27 03:08:43
Subject: BUG #4065: Error on psql windows client
Previous:From: Dr.D.D.KarunarathneDate: 2008-03-27 01:24:52
Subject: postgres geometry column data visibility

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group