Re: Equivalence Classes when using IN

On 10 October 2017 at 02:51, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>> It's pretty bad practice to have ORDER BY in views. I kinda wish we
>> didn't even allow it, but that ship sailed many years ago...
>
> I think it's actually disallowed by the SQL spec (although so are
> many other things we support). IMO it's a useful facility to have
> for views that are meant for direct presentation to clients ---
> but if you'd like joins to the view to be optimized, you don't
> want an ORDER BY in there.

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.

Something like the attached?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services