pgsql-4906

Version:

8.4.0

Links:

bug report: http://archives.postgresql.org/pgsql-bugs/2009-07/msg00244.php

Patch: http://archives.postgresql.org/pgsql-bugs/2009-07/msg00252.php

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:

Reproduced.

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.

Symptom:

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,

                                                otherinfo->syn_lefthand);

                min_righthand = bms_add_members(min_righthand,

                                                otherinfo->syn_righthand);

            }

        }

    }

}

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

Is there any log message?:

No.

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.