Re: Missing MaterialPath support in reparameterize_path_by_child

On Fri, Dec 2, 2022 at 10:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I traced that to the fact that reparameterize_path_by_child()
> omits support for MaterialPath, so that if the only surviving
> path(s) for a child join include materialization steps, we'll
> fail outright to produce a plan for the parent join.


Yeah, that's true.  It's weird we neglect MaterialPath here.


> Unfortunately, I don't have an example that produces such a
> failure against HEAD.  It seems certain to me that such cases
> exist, though, so I'd like to apply and back-patch the attached.


I tried on HEAD and got one, which leverages sampled rel to generate the
MaterialPath and lateral reference to make it the only available path.

SET enable_partitionwise_join to true;

CREATE TABLE prt (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE prt_p1 PARTITION OF prt FOR VALUES FROM (0) TO (10);

CREATE EXTENSION tsm_system_time;

explain (costs off)
select * from prt t1 left join lateral (select t1.a as t1a, t2.a as t2a
from prt t2 TABLESAMPLE system_time (10)) ss on ss.t1a = ss.t2a;
ERROR:  could not devise a query plan for the given query

Thanks
Richard