Re: DISTINCT ON changes sort order

Alexander Reichstadt <lxr(at)mac(dot)com> wrote:

> SELECT
>   DISTINCT ON (msgid)
>     msgid
>   FROM (
>          SELECT refid_messages as msgid
>            FROM messagehistorywithcontent
>            WHERE 1=1
>              AND (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' ')) LIKE '%gg%')
>            ORDER BY messagekind DESC
>        ) as foo

> [does not return results in the order of in subquery]

> Which is wrong.

No, your query is underspecified.  The subquery in the FROM clause
is an input relation to the top-level query, which then does a form
of aggregation on that.  There are no guarantees that a SELECT
statement will return values in an order based on any of its input
relations, unless you specify it that way.

All the top-level SELECT sees is a list of msgid values, and you
are asking it to eliminate duplicates to return a set of distinct
values.  It is doing exactly that in the way which it estimates
will be the fastest.  If you actually want something different from
that, you must specify it.

I'm not any more sure than the planner what it is that you *really*
want, but maybe this?:

SELECT msgid
  FROM (
         SELECT distinct messagekind, refid_messages as msgid
           FROM messagehistorywithcontent
           WHERE (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' ')) LIKE '%gg%')
       ) as foo
  ORDER BY
    messagekind DESC,
    msgid;

If that doesn't give you what you're after, we need a better
explanation of what you want.  A self-contained test case, where
you create a table and load it with data and show a query, would be
best.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company