Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

Hi Samuel,

Thanks for the detailed report and the reproduction.

I can reproduce it on master (45277ca0d1): Q1 returns (2, f), Q2
returns (2, t), and the plan has the same hashed SubPlan in both the
t_channels scan filter and the join filter, with loops=3 on the
t_follows scan.

Your reading of the executor is right; I confirmed it by adding
temporary logging to ExecHashSubPlan().  Both references get their
own SubPlanState and hash table, but share the subplan's PlanState.
For the second outer row (viewer 502):

  scan filter:  has a hash table, chgParam set   -> rebuilds it
  join filter:  has a hash table, chgParam clear -> keeps the table
                built for viewer 501, so channel 2 isn't found

buildSubPlanHash() calls ExecReScan() on the shared PlanState, which
clears its chgParam, so the join filter's SubPlanState never sees
that the parameter changed.

This isn't specific to the EXISTS-to-ANY conversion.  A similar query
with a plain correlated IN gives the same wrong result on master:

  select s.id,
    exists (select 1 from t_posts p
              join t_channels c on c.id = p.channel_id
             where p.id = s.post_id
               and (c.owner_id = s.viewer_id
                    or (p.status = 'published'
                        and c.id in (select f.channel_id
                                       from t_follows f
                                      where f.user_id = s.viewer_id))))
    from t_sessions s order by s.id;

The attached patch gives each SubPlanState its own flag marking its
hash table as stale.  ExecReScan() sets it where it propagates
chgParam to the node's subplans, ExecHashSubPlan() rebuilds the
table when it is set, and buildSubPlanHash() clears it.  With the
patch, both queries return (2, t) and the t_follows scan shows
loops=4.  The patch adds a test to subselect.sql that fails without
the fix, and the regression, isolation and contrib tests pass (I
haven't run the TAP tests).

I went for an executor fix rather than changing
extract_restriction_or_clauses(), because the same subplan being
referenced from more than one place is already expected elsewhere:
ExplainSubPlans() notes that several SubPlan nodes can reference the
same subplan from different plan nodes, e.g. a bitmap index scan's
indexqual and its parent heap scan's recheck qual.  Giving each
reference its own PlanState would also work, but seems much more
invasive.

The new bool goes into the alignment padding after havenullrows, so
sizeof(SubPlanState) and the offsets of the other fields don't change
(checked on a 64-bit build of master; the layout around it is the same
in 14-18), which should keep it safe to back-patch.  The code changes
apply to REL_19_STABLE as is; 14-18 need small adjustments, so I'll
post tested back-branch versions next.

Regards,
Rahul Yadav