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