The following bug has been logged online:

Bug reference: 2549
Logged by: Kayteck
Email address: kayteck_master (AT) o2 (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Kayteck" <kayteck_master (AT) o2 (DOT) pl> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jul 25, 2006 at 12:58:55AM +0000, Kayteck wrote:
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/i...l#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/i....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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend