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

I try to port application to PostgreSQL 8.1+

The following query runs OK in VFP but causes error in Postgres

FULL JOIN is only supported with merge-joinable join conditions

How to fix ?

Andrus.


SELECT
  ametikoh.Nimetus as ametikoht,
  Isik.nimi,
  Isik.eesnimi,
  koosseis.kogus,
  COALESCE( iandmed.Kuluobj, koosseis.objekt1) as osakond

FROM iandmed
  FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
    AND  iandmed.Kuluobj= koosseis.objekt1
    AND iandmed.AmetiKoht is not null
  JOIN ametikoh ON COALESCE(iandmed.ametikoht,koosseis.ametikoht)=
ametikoh.AmetiKoht
  LEFT JOIN isik ON iandmed.isik=isik.isik
WHERE true
ORDER BY 1,2


Revelant pars of table structures are:

CREATE TABLE iandmed
(
  reanr integer NOT NULL DEFAULT nextval('iandmed_reanr_seq'::regclass),
  isik character(10) NOT NULL,
  miskuup date,
  plopp date,
  summavrt numeric(12,2),
  kuluobj character(10),
  ametikoht numeric(7),
  CONSTRAINT iandmed_pkey PRIMARY KEY (reanr)
) ;


CREATE TABLE koosseis
(
  id numeric(7) NOT NULL,
  ametikoht numeric(7) NOT NULL,
  objekt1 character(10) NOT NULL,
  kogus numeric(4) NOT NULL DEFAULT 0,
  algus date,
  lopp date,
  CONSTRAINT koosseis_pkey PRIMARY KEY (id)
);

"Andrus" <kobruleht2@hot.ee> writes:
> I try to port application to PostgreSQL 8.1+
> The following query runs OK in VFP but causes error in Postgres
> FULL JOIN is only supported with merge-joinable join conditions

> SELECT
> ...
> FROM iandmed
>   FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
>     AND  iandmed.Kuluobj= koosseis.objekt1
>     AND iandmed.AmetiKoht is not null

Uh, can't you just drop the "iandmed.AmetiKoht is not null" condition?
It seems redundant considering that "iandmed.ametikoht=koosseis.ametikoht"
isn't going to succeed when ametikoht is null.

In the long run we should teach hash join to support full-join behavior,
which would allow cases like this one to work; but it seems not very
high priority, since I've yet to see a real-world case where a
non-merge-joinable full-join condition was really needed.  (FULL JOIN
being inherently symmetric, the join condition should usually be
symmetric as well...)

            regards, tom lane

"Andrus" <kobruleht2@hot.ee> writes:
>> I've yet to see a real-world case where a
>> non-merge-joinable full-join condition was really needed.

> I need to eliminate rows containing null value in left side table in full
> join.

> create table iandmed ( ametikoht integer );
> insert into iandmed values(1);
> insert into iandmed values(null);
> create table koosseis (ametikoht integer );
> insert into koosseis values(2);

> SELECT *
>  FROM iandmed
>    FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
>      AND iandmed.ametikoht IS NOT NULL

> Required result:

> 1 null
> null 2

Well, if we did support that query as written, it would not produce the
result you want.  With or without the IS NOT NULL part, the
null-containing row of iandmed will fail to join to every row of
koosseis, and will therefore produce a single output row with nulls for
the koosseis field(s).  If you get a different result in some other
database, it's broken (nonstandard handling of NULL comparison maybe?).

I think the way to get the result you want is to suppress the
null-containing rows before they get to the FULL JOIN, like so:

regression=# SELECT *
 FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed
   FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht;
 ametikoht | ametikoht
-----------+-----------
         1 |
           |         2
(2 rows)


            regards, tom lane

> I think the way to get the result you want is to suppress the
> null-containing rows before they get to the FULL JOIN, like so:
>
> regression=# SELECT *
> FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed
>   FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht;

Thank you.
In my case koosseis.ametikoht column does not contain null values.
Si I fixed this in WHERE clause

WHERE (iandmed.ametikoht is not null or koosseis.ametikoht is not null)

I hope this   produces same result in my case.

Andrus.