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...
Best regards,
Alexander
