Re: EquivalenceClasses vs volatile functions

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

>     select random() as a, random() as b from foo order by b, a;
>
> And it definitely seems the wrong thing for the last one.

Ouch.

> What I'm thinking of doing is adding a field to EquivalenceClass that
> carries the ressortgroupref of the originating ORDER BY key's targetlist
> entry, in the case where the EquivalenceClass came from building a
> PathKey for ORDER BY.  Then, if the EquivalenceClass is ec_volatile,
> we insist on matching that rather than matching the expression tree.
> This wouldn't affect the behavior for ordinary non-volatile sort keys,
> for which expression equivalence is valid.

What if the equivalence class is in more than one place in the ORDER BY? 

I suppose GROUP BY has the same problem?

And what would happen when you pull up subqueries?

> If we wanted cases 1 and 2 to behave differently, it'd be better to have the
> parser always generate resjunk columns for ORDER BY expressions, and get the
> planner to collapse out columns that are redundant. But that seems too big a
> change to contemplate for 8.3.

That clearly sounds right. 

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!