Re: [HACKERS] DISTINCT and ORDER BY bug?

At 01:36 AM 2/7/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza(at)pacifier(dot)com> writes:
>> At 12:26 AM 2/7/00 -0500, Tom Lane wrote:
>>> 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.
>
>Well, try these on for size:

Here's what the Oracle proclaims:

select distinct x from foo order by x+1;
no rows selected

select distinct x+1 from foo order by x+1;
no rows selected

select distinct x+1 from foo order by x;
SQL> select distinct x+1 from foo order by x
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

select distinct x+1 from foo order by x+2;
SQL> select distinct x+1 from foo order by x+2
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

select distinct x+y from foo order by x+y;
SQL>
no rows selected

I also tried: select distinct x+y from foo order by y+x,
which fails.

select distinct x,y from foo order by x+y;
SQL>
no rows selected

select distinct x+y from foo order by x,y;
SQL> select distinct x+y from foo order by x,y
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

select distinct x+y from foo order by x-y;
SQL> select distinct x+y from foo order by x-y
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

My first thought is that it is following a simple rule:

For arithmetic "order by" expressions, either:

1. The exact expression must also appear in the "select" list,
and it must be exact, not just an expression that computes
the same value as the "order by" expression

or

2. all of the variables used by the expression must be listed
in the "select" list as simple column names, not as part of
an expression.

Must be true.

At least, the rule is simple if you can compare expression trees.

At this point I still am clueless regarding the standard, I think I'll
make Date my morning coffee date again.

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