On Mon, Mar 13, 2006 at 11:02:35AM +0100, Harco de Hilster wrote:
> 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
Ouch! You've got yourself a nasty one.
The technical reason why this doesn't work is because full outer joins
in postgres are implemented by a merge join. Basically sort the two
tables and then compare them record by record, emitting null columns
when necessary. However, this only works when the condition is
merge-joinable, usually on ordered datatypes with an equality operator.
I think the reason it hasn't been done for general join conditions is
because we havn't thought of an efficient algorithm. Basically, for
each row in the left table you need to find all matching rows in the
right table. Afterwards you have to find all the rows in the right
table which havn't been used.
i.e. you can emulate it by doing a:
SELECT * FROM A LEFT JOIN B ON (X)
UNION ALL
SELECT * FROM A RIGHT JOIN B ON (X) WHERE A.id IS NULL;
But this is double joining and so not terribly efficient. And if the
tables were subqueries it would be worse and quite possibly wrong if
the output isn't constant.
However, I wonder if youre case couldn't be handled with a
time-interval datatype such that you condition is merge-joinable on
that type. I can't quite see it though...
> My tables 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)))
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.