Thread: BUG #2549: problem with NATURAL JOIN

The following bug has been logged online:

Bug reference:      2549
Logged by:          Kayteck
Email address:      kayteck_master@o2.pl
PostgreSQL version: 8.1.3
Operating system:   Fedora Core 4
Description:        problem with NATURAL JOIN
Details:

I have two tables joined by foreign key id_przelewu, and for some rows
results of queries with NATURAL JOIN and JOIN ... USING (...) differs ! I've
readed that NATURAL JOIN is only shorthand for the second method, but this
results shows that's not true:
select id_zamowienia,id_przelewu from zamowienia where id_klienta=4999;
 id_zamowienia | id_przelewu
---------------+-------------
          7504 |        3095
          7503 |        3095
          7408 |        3095
(3 rows)

select id_przelewu from przelew where id_przelewu=3095;
 id_przelewu
-------------
        3095
(1 row)

select id_przelewu,id_zamowienia from zamowienia natural join przelew where
id_klienta=4999;
 id_przelewu | id_zamowienia
-------------+---------------
(0 rows)

select id_przelewu,id_zamowienia from zamowienia join przelew using (id_p
rzelewu) where id_klienta=4999;
 id_przelewu | id_zamowienia
-------------+---------------
        3095 |          7504
        3095 |          7503
        3095 |          7408
(3 rows)

It seems for an error of postgresql

On Tue, 2006-07-25 at 00:58 +0000, Kayteck wrote:

> I have two tables joined by foreign key id_przelewu, and for some rows
> results of queries with NATURAL JOIN and JOIN ... USING (...) differs ! I've
> readed that NATURAL JOIN is only shorthand for the second method, but this
> results shows that's not true:

NATURAL JOIN is shorthand for a USING () clause that mention *all*
columns with matching names, so the meaning is slightly different.

> select id_przelewu,id_zamowienia from zamowienia natural join przelew where
> id_klienta=4999;
>  id_przelewu | id_zamowienia
> -------------+---------------
> (0 rows)
>
> select id_przelewu,id_zamowienia from zamowienia join przelew using (id_p
> rzelewu) where id_klienta=4999;
>  id_przelewu | id_zamowienia
> -------------+---------------
>         3095 |          7504
>         3095 |          7503
>         3095 |          7408
> (3 rows)

We cannot see whether this is a bug or not, since you have not provided
the full descriptions of the two tables involved. Without those we
cannot tell whether the NATURAL JOIN isn't shorthand for this...

select id_przelewu,id_zamowienia from zamowienia join przelew using
(id_przelewu, matching_name_col1, ...) where id_klienta=4999;

and could therefore provide a different answer.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com

"Kayteck" <kayteck_master@o2.pl> writes:
> I have two tables joined by foreign key id_przelewu, and for some rows
> results of queries with NATURAL JOIN and JOIN ... USING (...) differs ! I've
> readed that NATURAL JOIN is only shorthand for the second method, but this
> results shows that's not true:

Your two examples are not necessarily equivalent --- in particular, if
id_przelewu isn't the only common column name in the two tables then
NATURAL JOIN isn't equivalent to a join USING only id_przelewu.
I would guess that there's some other column the NATURAL JOIN is trying
to match, and there are no matches.

If you think there's an actual bug here, you need to exhibit a complete
test case, which this most certainly wasn't.

            regards, tom lane

On Tue, Jul 25, 2006 at 12:58:55AM +0000, Kayteck wrote:
> I have two tables joined by foreign key id_przelewu, and for some rows
> results of queries with NATURAL JOIN and JOIN ... USING (...) differs ! I've
> readed that NATURAL JOIN is only shorthand for the second method, but this
> results shows that's not true:
[...]
> select id_przelewu,id_zamowienia from zamowienia natural join przelew where
> id_klienta=4999;
>  id_przelewu | id_zamowienia
> -------------+---------------
> (0 rows)

Do zamowienia and przelew have column names in common in addition
to id_przelewu?  NATURAL JOIN uses all common column names, not
just those specified in a foreign key constraint.

http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-FROM

"Finally, NATURAL is a shorthand form of USING: it forms a USING
list consisting of exactly those column names that appear in both
input tables."

http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FROM

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

--
Michael Fuhr