Обсуждение: ERROR: FULL JOIN is only supported with merge-joinable join conditions

Hi all,

I am porting my application from Ingres to Postgres, and I have the
following problem. I am not sure if this is a known limitation of
Postgresql or a bug. My code works under Ingres but fails in Postgres
with the following error:

ERROR:  FULL JOIN is only supported with merge-joinable join conditions

My table contain temporal data e.g.

Table A:
f1 | f2 | modtime | exptime
--------------------------
A | B | t0 | t2   <= historical record
A | C | t2 | t6    <= historical record
A | D | t6 | NULL   <= live record

Table B:
f1 | f2 | modtime | exptime
--------------------------
F | G | t1 | t3 <= historical record
F | H | t3 | t5 <= historical record
F | I | t5 | NULL <= live record

All queries on live data are of the form: select * from a where f1 = xx
and exptime is NULL

A full outer join on two tables with temporal data looks like this:

select *
from A
full outer join B on A.f1 = B.f1  and  ((A.ExpTime IS NULL AND B.ExpTime
IS NULL) OR (A.ModTime <= B.ExpTime AND (B.ExpTime > A.ExpTime OR
B.ExpTime IS NULL)))

The primary keys of A and B are (f1, exptime).


Postgres's problem is with the <=, > and is null conditions in the full
outer join. These are probably not 'merge-joinable', so the query fails.
Shouldn't it try a different method instead of failing??

I cannot move the conditions on exptime to the where clause, because
that would introduce (outer join) extra records with historical data in
B that are not in the lifetime span of records in A.

Any suggestions or is this a show stopper?

Thanks,

Harco

* Harco de Hilster (harcoh@ATConsultancy.nl) wrote:
> ERROR:  FULL JOIN is only supported with merge-joinable join conditions

I'm not a big fan of that error either, honestly.

> select *
> from A
> full outer join B on A.f1 = B.f1  and  ((A.ExpTime IS NULL AND B.ExpTime
> IS NULL) OR (A.ModTime <= B.ExpTime AND (B.ExpTime > A.ExpTime OR
> B.ExpTime IS NULL)))

What about:

inner join
union all
left join
union all
right join

?  You'd need to add a couple where clauses to make the left/right joins
only return rows not returned by the inner join.  Honestly, that setup
is pretty ugly though and you might consider trying to figure out a way
to clean it up.

It'd be nice if Postgres would basically just do this for you. :/
Perhaps one of the other folks can explain why it doesn't just work.

    Enjoy,

        Stephen