Re: ORDER BY and DISTINCT ON

Neil Conway <neilc(at)samurai(dot)com> writes:
> Does the non-determinism you're referring to result from an ORDER BY
> on a non-deterministic expression, or the non-determinism that results
> from picking an effectively random row because the ORDER BY isn't
> sufficient?

The latter --- you don't know which row you'll get, because it depends
on what the sorting procedure does with equal keys. (I think. This
argument was a few years ago and I've not bothered to review the
archives.) With ordinary DISTINCT this does not matter because you
can't tell the difference between "equal" rows anyway --- but with
DISTINCT ON, you can tell the difference.

> Which seems like an unconvincing justification for rejecting the
> query: we accept DISTINCT ON with no ORDER BY clause at all, for
> example.

Well, we invent an ORDER BY clause matching the DISTINCT ON in that
case. The rationale for doing so is weak, I agree, but since you have
not specified a sort order, you can hardly argue that the result is
wrong.

I think you are correct that this restriction is essentially an
efficiency hack. But DISTINCT ON is in itself an efficiency hack.
I'm not sure I see the point of allowing a less-efficient variation
of the efficiency hack, which is what we'd have if we supported
DISTINCT ON with a non-matching ORDER BY. Certainly it doesn't seem
important enough to expend significant implementation effort on.

regards, tom lane