DISTINCT ON

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

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

Greg Stark <gsstark@mit.edu> writes:

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

Tom Lane wrote:

Greg Stark <gsstark@mit.edu> writes:

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