Re: [HACKERS] DISTINCT and ORDER BY bug?

Don Baccus <dhogaza(at)pacifier(dot)com> writes:
> The following used to work in 6.5, works in Oracle, and is
> very useful:

> donb=# select distinct c from foo order by upper(c);
> ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list

Well, it's not a bug --- it was an entirely deliberate change. It
might be a misfeature though. The case we were concerned about was

select distinct x from foo order by y;

which produces ill-defined results. If I recall the thread correctly,
Oracle and a number of other DBMSs reject this. I think your point is
that

select distinct x from foo order by f(x);

*is* well-defined, and useful. I think you are right, but how
far should we go in detecting common subexpressions? You might
want to contemplate the difference in these examples:

select distinct sin(x) from foo order by abs(sin(x));

select distinct random(x) from foo order by abs(random(x));

It would be interesting to poke at Oracle to find out just what they
consider a legitimate ORDER BY expression for a SELECT DISTINCT.

regards, tom lane