Re: Warts with SELECT DISTINCT

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> ... it would be OK to rewrite
> SELECT DISTINCT x ORDER BY foo(x)
> as
> SELECT DISTINCT ON (foo(x), x) x ORDER BY foo(x)

This assumes that x = y implies foo(x) = foo(y), which is something
that's not necessarily the case, mainly because a datatype's "="
function need not have a lot to do with the behavior of arbitrary
functions foo(), especially if foo() yields a different datatype.
The citext datatype is an easy counterexample: it thinks "foo" = "Foo",
but md5() of those values will not yield the same answers.

The bottom line here is that this sort of deduction requires more
understanding of the properties of datatypes and functions than
our existing catalogs allow the planner to obtain.

regards, tom lane