Re: Does anybody use ORDER BY x USING y?

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Mon, Sep 19, 2005 at 06:26:10AM +1000, John Hansen wrote:
>> I was actually of the impression that that was exacty what it was for:
>> specifying what op(class) to use for the sort in case you wanted to use
>> a non-default opclass for the type, and/or if the less-than operator
>> wasn't called '<'.

> That's my thought. However, the code doesn't seem to restrict you to
> that so I was wondering if there was any other use out there that we
> should consider supporting...

One of the half-baked ideas about operator classes that I mentioned a
few days ago was to either redesign or reinterpret USING in a way that
would make it easier to associate a btree opclass with a requested
ordering. I'm not sure that we want to *require* there to be a btree
opclass matching any ORDER BY request, but it's something to consider.
(There are some examples in the regression tests of ORDER BY using
operators that aren't in any btree opclass, but I'm not sure any of
them represent useful real-world cases. In principle, if the operator
represents a self-consistent ordering at all, then a btree opclass
could be built with it. So it could be argued that we're just
supporting programmer laziness to not require one.)

Right now we use some heuristics to try to identify an opclass
containing the mentioned operator, but this is pretty unreliable
and would become more so if reverse-sort opclasses became standard
equipment. Another thing that's flaky in the current treatment is
the question of whether NULLs sort before or after ordinary values.
We've essentially tried to force NULLs to sort "high" (as if they
compare greater than all ordinary values), so that ASC and DESC
orderings can be obtained from forward and backwards scans of an
ordinary btree index. This is going to break entirely in the
presence of reverse-sort opclasses --- given the current btree code,
such an opclass would cause NULLs to appear to sort "low". I suspect
we have to bring out the NULL sort behavior as an explicit property
of opclasses, but I'm not sure just how to do that. A related point
is that we not infrequently get requests for a way to make ORDER BY
sort nulls low; it'd be nice if we could actually support that,
rather than going in the direction of making sure it can't happen.

regards, tom lane