Re: BUG #3778: Natural join with filter problem

[Heikki Linnakangas <heikki(at)enterprisedb(dot)com>]

Please keep the list CC'd.

Laurent HERVE wrote:
> in fact, i don't know why the column "code_document" is used because it
> is not in the primary key of the tables.
> So as this column is nullable, it gives unexpected results.
>
> I thought only the columns on the primary key are taken into account
> when building a join. Maybe i misunderstood something in how postgresql
> builds the join.

Ah, no. According to the docs:

    NATURAL is shorthand for a USING list that mentions all columns in 
the two tables that have the same names.

Because there's a column called code_document in both tables, you'll 
have to use an INNER JOIN to get what you want. If you want to eliminate 
the duplicate columns from the result set like a NATURAL JOIN does, you 
can use INNER JOIN ... USING (list of columns).

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com