Re: DISTINCT ON

On Nov 3, 2009, at 10:17 PM, Emmanuel Cecchet <manu@asterdata.com>  
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