Re: Removing unneeded self joins

On Tue, Apr 30, 2024 at 9:00 AM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> 23.10.2023 12:47, Alexander Korotkov wrote:
> > I think this patch makes substantial improvement to query planning.
> > It has received plenty of reviews. The code is currently in quite
> > good shape. I didn't manage to find the cases when this optimization
> > causes significant overhead to planning time. Even if such cases will
> > be spotted there is a GUC option to disable this feature. So, I'll
> > push this if there are no objections.
>
> I've discovered another failure, introduced by d3d55ce57.
> Please try the following:
> CREATE TABLE t (a int unique, b float);
> SELECT * FROM t NATURAL JOIN LATERAL
> (SELECT * FROM t t2 TABLESAMPLE SYSTEM (t.b)) t2;

I think we should just forbid SJE in case when relations to be merged
have cross-references with lateral vars. The draft patch for this is
attached. I'd like to ask Alexander to test it, Richard and Andrei to
review it. Thank you!

------
Regards,
Alexander Korotkov