At 12:10 PM 2/7/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>SQL> select distinct x,y+1 from foo order by x+y+1
>> *
>> ERROR at line 1:
>> ORA-01791: not a SELECTed expression
>
>Actually, that was a little unfair, since their parser no doubt parsed
>"x+y+1" as (x+y)+1, leaving no common subexpression visible. Do they
>accept
>
> select distinct x,y+1 from foo order by x+(y+1)
Yes, it does. So, they must be doing some level of common expression
analysis, for real.
>>>> At least, the rule is simple if you can compare expression trees.
>
>>> I think we have something pretty similar for GROUP BY, actually,
>>> so it may not be hard to make this work.
>
>On further thought, I think the real implementation issue is that
>doing SELECT DISTINCT ORDER BY requires either two sorting steps
>(sort by DISTINCT fields, "uniq" filter, sort again by ORDER BY fields)
Yes.
>or else some very hairy logic to figure out that ORDER BY x+1
>"implies" ORDER BY x. In fact I'm not sure it does imply it
>in the general case. In your original example, the requested sort
>was ORDER BY upper(x), but that doesn't guarantee that the tuples
>will be ordered adequately for duplicate-x elimination.
I realize that. I would assume that a double-sort penalty might
be incurred, i.e. the select distinct ... is executed followed by
the order by.
>Possibly we could make this work by implicitly expanding the ORDER BY
>to "ORDER BY upper(x), x" which would ensure that the duplicate x's
>are brought together.
That would be another approach, too, if it works for all cases...
> I am not sure this will give the right results
>always, but it seems promising. We are assuming here that upper(x)
>gives equal outputs for equal inputs, so it would fall down on random(x)
>--- I suppose we could refuse to do this if we see a function that is
>marked non-constant-foldable in pg_proc...
Something like that, yes.
I just checked Date while off having coffee, and it is clear that the
SQL standard specifies that ORDER BY operates on COLUMNS, not expressions.
So the restriction that's now imposed is indeed standard compliant. However,
some level of extension in this area would be very useful, and my guess is
that examples like the one that started this discussion are very common.
- 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.