Re: Equivalence Classes when using IN

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.

BTW, I noticed that I was wrong upthread about ORDER BY in subqueries
being disallowed by spec --- that was true originally, but they allow
it as of SQL:2008 or thereabouts. It might be interesting to see if
the spec says anything concrete about the semantics of that.

regards, tom lane