Re: Equivalence Classes when using IN

On Mon, Oct 09, 2017 at 07:44:50PM -0400, Tom Lane wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > If the only reason that is_simple_subquery() rejects subqueries with
> > ORDER BY is due to wanting to keep the order by of a view, then
> > couldn't we make is_simple_subquery() a bit smarter and have it check
> > if the subquery is going to be joined to something else, which likely
> > would destroy the order, or at least it would remove any guarantees of
> > it.
>
> I'm not on board with this. The assumption is that if the user put an
> ORDER BY there, that means they want that subquery to be computed in that
> order. It's not for us to decide they didn't mean what they said.
>
> Moreover, there are cases where the ORDER BY would be semantically
> significant, eg if there's a LIMIT or volatile functions or tSRFs
> involved.

Or where the order is meaningful to an aggregate function applied to
columns of a view result set. I'm not sure what the full set of cases
where the ORDER BY on the inner query is meaningful, but I'm sure there
are cases it is not.

If there are no such constraints on dropping the ORDER BY, then the it
could be dropped, making the view query simpler.

Nico
--