DISTINCT ON

[Emmanuel Cecchet-7]

Hi all,

It looks like Postgres has a restriction in DISTINCT ON queries where the DISTINCT ON expressions must match the left side of the ORDER BY list. The issue is that if a DISTINCT ON ... has multiple instances of a particular expression, this check doesn't seem to fire correctly.

For example, this query returns an error (but I guess it shouldn't):

SELECT DISTINCT ON ('1'::varchar,  '1'::varchar) a FROM (SELECT 1 AS a) AS a ORDER BY '1'::varchar, '1'::varchar, '2'::varchar;

And this query doesn't return an error (but I guess it should):

SELECT DISTINCT ON ('1'::varchar, '2'::varchar, '1'::varchar) a FROM (SELECT 1 AS a) AS a ORDER BY '1'::varchar, '2'::varchar, '2'::varchar;


Am I misunderstanding something or is there a bug?

Thanks for the help
Emmanuel

--
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Greg Stark-5]

On Wed, Nov 4, 2009 at 3:17 AM, Emmanuel Cecchet <manu@...> wrote:

> For example, this query returns an error (but I guess it shouldn't):
>
> SELECT DISTINCT ON ('1'::varchar,  '1'::varchar) a FROM (SELECT 1 AS a) AS a
> ORDER BY '1'::varchar, '1'::varchar, '2'::varchar;

This sounds familiar.  What version of Postgres are you testing this on?

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Nov 3, 2009, at 10:17 PM, Emmanuel Cecchet <manu@...>  
wrote:

> Hi all,
>
> It looks like Postgres has a restriction in DISTINCT ON queries  
> where the DISTINCT ON expressions must match the left side of the  
> ORDER BY list. The issue is that if a DISTINCT ON ... has multiple  
> instances of a particular expression, this check doesn't seem to  
> fire correctly.
>
> For example, this query returns an error (but I guess it shouldn't):
>
> SELECT DISTINCT ON ('1'::varchar,  '1'::varchar) a FROM (SELECT 1 AS  
> a) AS a ORDER BY '1'::varchar, '1'::varchar, '2'::varchar;
>
> And this query doesn't return an error (but I guess it should):
>
> SELECT DISTINCT ON ('1'::varchar, '2'::varchar, '1'::varchar) a FROM  
> (SELECT 1 AS a) AS a ORDER BY '1'::varchar, '2'::varchar,  
> '2'::varchar;
>
>
> Am I misunderstanding something or is there a bug?

I'm guessing this is the result of some subtly flakey equivalence  
class handling.  On first glance ISTM that discarding duplicates is  
legit and therefore both examples ought to work...

...Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Greg Stark <gsstark@...> writes:
> On Wed, Nov 4, 2009 at 3:17 AM, Emmanuel Cecchet <manu@...> wrote:
>> SELECT DISTINCT ON ('1'::varchar,  '1'::varchar) a FROM (SELECT 1 AS a) AS a
>> ORDER BY '1'::varchar, '1'::varchar, '2'::varchar;

> This sounds familiar.  What version of Postgres are you testing this on?

Presumably something before this bug
http://archives.postgresql.org/pgsql-sql/2008-07/msg00123.php
got fixed
http://archives.postgresql.org/pgsql-committers/2008-07/msg00341.php

                        regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Emmanuel Cecchet-7]

Tom Lane wrote:

> Greg Stark <gsstark@...> writes:
>  
>> On Wed, Nov 4, 2009 at 3:17 AM, Emmanuel Cecchet <manu@...> wrote:
>>    
>>> SELECT DISTINCT ON ('1'::varchar,  '1'::varchar) a FROM (SELECT 1 AS a) AS a
>>> ORDER BY '1'::varchar, '1'::varchar, '2'::varchar;
>>>      
>
>  
>> This sounds familiar.  What version of Postgres are you testing this on?
>>    
>
> Presumably something before this bug
> http://archives.postgresql.org/pgsql-sql/2008-07/msg00123.php
> got fixed
> http://archives.postgresql.org/pgsql-committers/2008-07/msg00341.php
>  

I am using 8.3.6 and it looks like the fix was only integrated in 8.4.
So using 8.4 should solve the problem.

Thanks
Emmanuel

--
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers