bug report:


Description from developer:

- Fix another semijoin-ordering bug. We already knew that we couldn't reorder a semijoin into or out of the righthand side of another semijoin, but actually it doesn't work to reorder it into or out of the righthand side of a left or antijoin, either. Per bug #4906 from Mathieu Fenniak. This was sloppy thinking on my part. This identity does work: ( A left join B on (Pab) ) semijoin C on (Pac) == ( A semijoin C on (Pac) ) left join B on (Pab) but I failed to see that that doesn't mean this does: ( A left join B on (Pab) ) semijoin C on (Pbc) != A left join ( B semijoin C on (Pbc) ) on (Pab)

How it is diagnosed:


How to reproduce:

Simply create necessary tables  & insert data using the script provided in the bug report. “psql -p 5555 foo < [script name]”

Then execute the sql statements in the bug report.


Postgres returns wrong result when left join is used together with ‘semijoin’.

(A left join B on (Pab) ) semijoin C on (Pbc)

returns different results from

A left join ( B semijoin C on (Pbc) ) on (Pab)

where they should return the same results.

Root cause:
The query execution plan logic is flawed:

static SpecialJoinInfo *

make_outerjoininfo(... ...)


    foreach(l, root->join_info_list)


        … ...

        if (bms_overlap(right_rels, otherinfo->syn_righthand))


            if (bms_overlap(clause_relids, otherinfo->syn_righthand) ||

                jointype == JOIN_SEMI ||

+        otherinfo->jointype == JOIN_SEMI ||

                otherinfo->jointype == JOIN_ANTI ||

                !otherinfo->lhs_strict || otherinfo->delay_upper_joins)


                min_righthand = bms_add_members(min_righthand,


                min_righthand = bms_add_members(min_righthand,






The bug only occurs when “jointype == JOIN_LEFT” and “otherinfo->jointype == JOIN_SEMI”!

Is there any log message?:


Can ErrLog help?

No.  Condition variable only.Since the error only happens when “jointype == JOIN_LEFT” and “otherinfo->jointype == JOIN_SEMI”, so invariant checking on single variable values is unlikely to work.  Maybe key value solution, as long as it can record the above two variable values.