pgsql-5263

Version:

8.4.2

How it is diagnosed:

Source analysis.

Links:

Bug report: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg25479.html

Symptom:

Postgres reject valid SELECT 0 full join 1, when it proved 0 != 1.  It is a meaningless input, but valid.

How it is diagnosed:

Reproduced.

How to reproduce:

Execute the following query:

SELECT *
FROM (SELECT id, 0 AS value
     FROM test
     WHERE description = 'abc'
    ) t1
    FULL OUTER JOIN
    (SELECT id, 1 AS value
     FROM test
     WHERE description = 'def'
    ) t2 USING (id, value);

You'll get the "ERROR:  FULL JOIN is only supported with merge-joinable join
conditions"

Although this query would definitely return empty results (since without even executing postgres can deduce that 0!=1), but postgres shouldn’t reject the input!

Root cause:
Postgres simply did not support full join on FALSE condition.

select_mergejoin_clauses (...)

  … …

  984         if (!restrictinfo->can_join || // Since postgres already deduced it cannot be joined...

    985             restrictinfo->mergeopfamilies == NIL)

    986         {

    987             have_nonmergeable_joinclause = true;

    989         }

  1053     if (have_nonmergeable_joinclause)

   1054     {

   1055         switch (jointype)

   1056         {

   1057             case JOIN_RIGHT:

   1058                 return NIL;     /* not mergejoinable */

   1059             case JOIN_FULL:

   1060                 ereport(ERROR,

   1061                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

   1062                          errmsg("FULL JOIN is only supported with merge-joinable join conditions")));

   1063                 break;

Is there any log message?:

Yes.

Can we automatically anticipate?

Yes. Learn frequent pattern. Here, the pattern is jointype == JOIN_FULL with nonmergeable join clauses. So to learn this pattern, we need to consider ‘multiple conditions’ -- two level conditions: have_nonmergeable_joinclause && jointype == JOIN_FULL.