Re: [HACKERS] DISTINCT and ORDER BY bug?

At 12:26 AM 2/7/00 -0500, Tom Lane wrote:

>Well, it's not a bug --- it was an entirely deliberate change.  It
>might be a misfeature though.

Ahhh...getting subtle, are we? :)

>  The case we were concerned about was
>
>    select distinct x from foo order by y;

Yes...I remember some discussion regarding this.

>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?

Not sure...having not been into that part of the code (and busy at
the moment testing my rewrites of small portions of RI trigger
code I rewrote at Jan's request, after our "dispute" [which was more
or less "I'm 50% certain you're right!" "No! I'm 50% you're right!"
until I found the paragraph in Date's book which proved we were both
just about 50% right]) I can't really say.  

I was hoping the standard might give some guidance?

>  You might
>want to contemplate the difference in these examples:
>
>    select distinct sin(x) from foo order by abs(sin(x));

I'm not sure I see a problem here.  My (brief) reading of the
standard tells me that "order by" follows everything else, 
in other words, you get

select ... arbitrary complexity, with group by and all sorts of
cruft ...

then you take that result and apply the "order by" clause.

You'd get all the negative values followed by the positive
values, but you'd also get -1.0 and 1.0 if the database had
those values.  Because they're distinct, and therefore live to
be ordered.

But I'm not sure about it...if you push me, I'll probably go dig
into the standard again (I was so successful with referential
"NO ACTION" last time, yeah, right, I sleep with Date's book under
my pillow at the moment!)

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

Of course, real compiler systems (like I've spent my life working
on) have heuristic or, more modernly, other ways of deciding if a
function returns different values depending on when it is called.
In such systems, you only have to guarantee the correct answer, so
choosing wrong simply means the code runs slower.  

"upper(column_value)" does not within a specific select.  Column
value won't change.  I can think of rules to think of but the
simplest might be that internal functions that are invariant when
their parameters are unchanged might be considered safe.  Others,
not.

Also, the standard might simply say the result is implementation
dependent or (slightly worse) defined if the function returns
different values for a call with the same parameter list in a
single query.  I don't know...it's an interesting question.

The other approach is to simply state that the function has one
and only one value during statement (SQL-statement, in this case)
execution, and yank the sucker out of there, execute it, and stuff
it in a temp variable.  But that's probably too naive.  Still, the
standard might say it is implementation defined as to whether or
not the function will be called once or more than once.  The standard
only cares about embedded SQL but it might give guidance...

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

I have full-time access to an Oracle installation, so fire away
regarding examples and questions.

Not just on this narrow subject, but in general.  I'm probably not the
ONLY person here with Oracle access, but I do have it, and my poking
at it won't hurt anything but Oracle's pride...



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.