Re: [HACKERS] DISTINCT and ORDER BY bug?

Don Baccus <dhogaza@pacifier.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