PG18 - wrong result in join between distributed and local table

[colm-mchugh]

Seen in local_dist_join_mixed regress test. Repro:

CREATE TABLE distributed (id bigserial PRIMARY KEY, name text, created_at timestamptz DEFAULT now());
CREATE TABLE local (id bigserial PRIMARY KEY, title text);

SELECT create_distributed_table('distributed', 'id');

INSERT INTO distributed SELECT i,  i::text, now() FROM generate_series(0,100)i;
INSERT INTO local SELECT i,  i::text FROM generate_series(0,100)i;

SET client_min_messages TO DEBUG1; -- show worker queries

-- Returns 0, should return 101
SELECT count(*) FROM distributed INNER
JOIN local u1 USING (id) INNER
JOIN local u2 USING (id);

-- Returns 0, should return 101
SELECT count(*) FROM distributed d INNER
JOIN local u1 ON (d.id=u1.id) INNER
JOIN local u2 ON  (u1.id=u2.id);

The problem appears to be that Citus is generating a bad query for the subquery on the local table;

SELECT NULL::integer AS "dummy-1" FROM local u1 WHERE true;

In Citus 13.x on PG17, the corresponding generated query is:

SELECT id FROM local u1 WHERE true;

Suspect a PG18 change to the Query data structure is being overlooked by Citus, and confuses ruleutils.

[colm-mchugh]

The root cause is upstream commit fc069a3a6; disabling its GUC enables the correct result:

set enable_self_join_elimination to 0;

 select count(*) from distributed INNER
JOIN local u1 USING (id) INNER
JOIN local u2 USING (id);
┌───────┐
│ count │
├───────┤
│   101 │
└───────┘

Need to understand how the self-join elimination feature of PG18 influences the Postgres Query data structure and then (probably) teach citus ruleutils how to deal with in order to produce correct shard queries.

[colm-mchugh]

With the problem query, self join elimination (fc069a3a6) removes u1 from the query, which involves replacing all its var references with u2. However, the RTE for u1 is not removed from the Query tree's rtable list and this causes problems in Citus deparsing; FilterRelationRestrictionContext() returns an empty relation restriction list for u1, (there are no restrictions involving u1 after sef join elimination) so there's an empty required attributes set for u1, and WrapRteRelationIntoSubquery() generates a dummy target list, but using that in the join means zero results. Ideally the relation u1 should be ignored, but its not yet obvious how to do that; one idea is that CreateConversionCandidates() could, for each RTE it looks at, ignore the RTE if the PlannerInfo's simple_rte_array entry for that RTE is NULL. But this (and other options) need to be explored.