On Wed, May 1, 2024 at 2:00 PM Alexander Lakhin <exclus...@gmail.com> wrote: > 30.04.2024 13:20, Alexander Korotkov wrote: > > On Tue, Apr 30, 2024 at 9:00 AM Alexander Lakhin <exclus...@gmail.com> > > wrote: > >> 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! > > Beside LATERAL vars, it seems that SJR doesn't play well with TABLESAMPLE > in general. For instance: > CREATE TABLE t (a int unique); > INSERT INTO t SELECT * FROM generate_series (1,100); > > SELECT COUNT(*) FROM (SELECT * FROM t TABLESAMPLE BERNOULLI(1)) t1 > NATURAL JOIN (SELECT * FROM t TABLESAMPLE BERNOULLI(100)) t2; > returned 100, 100, 100 for me, though with enable_self_join_removal = off, > I got 4, 0, 1...
Right, thank you for reporting this. BTW, I found another case where my previous fix doesn't work. SELECT * FROM t NATURAL JOIN LATERAL (SELECT * FROM t t2 TABLESAMPLE SYSTEM (t.b) NATURAL JOIN LATERAL(SELECT * FROM t t3 TABLESAMPLE SYSTEM (t2.b)) t3) t2; I think we probably could forbid SJE for the tables with TABLESAMPLE altogether. Please, check the attached patch. ------ Regards, Alexander Korotkov
sje_tablesample.patch
Description: Binary data
