NATURAL JOIN Probleme

Hallo zusammen.

wenn ich 2 Tabellen habe deren Spalten gleich heissen und Referenzen auf eine 3. Tabelle sind, liefert ein NATURAL JOIN Kein Ergebnis weil noch ein Join Filter vom Query Planer läuft.

Das ganze kann mit folgendem SCript getestet und nachvollzogen werden.

CREATE DATABASE jointest;

CREATE SEQUENCE users_uid_seq;
CREATE TABLE users (
    uid bigint DEFAULT nextval('users_uid_seq') PRIMARY KEY,
    login text UNIQUE NOT NULL
);

CREATE SEQUENCE combonodes_cnid_seq;
CREATE TABLE combonodes (
    cnid bigint DEFAULT nextval('combonodes_cnid_seq') PRIMARY KEY,
    comboname text UNIQUE NOT NULL,
    lastaccessedby bigint REFERENCES users(uid) NOT NULL
);

CREATE SEQUENCE searchkeys2combonodes_skid_seq;
CREATE TABLE searchkeys2combonodes (
    skid bigint DEFAULT nextval('searchkeys2combonodes_skid_seq') PRIMARY KEY,
    cnid bigint REFERENCES combonodes(cnid) NOT NULL,
    lastaccessedby bigint REFERENCES users(uid) NOT NULL
);

insert into users(login) VALUES('testuser1');
insert into users(login) VALUES('testuser2');
insert into users(login) VALUES('testuser3');

insert into combonodes(comboname, lastaccessedby) VALUES('combo1', 1);
insert into combonodes(comboname, lastaccessedby) VALUES('combo2', 2);
insert into combonodes(comboname, lastaccessedby) VALUES('combo3', 3);

insert into searchkeys2combonodes(cnid, lastaccessedby) VALUES(1, 3);
insert into searchkeys2combonodes(cnid, lastaccessedby) VALUES(2, 2);
insert into searchkeys2combonodes(cnid, lastaccessedby) VALUES(3, 1);

select * from searchkeys2combonodes natural join combonodes where skid = 1;
select * from searchkeys2combonodes natural join combonodes where skid = 2;
select * from searchkeys2combonodes natural join combonodes where skid = 3;

 select * from searchkeys2combonodes left join combonodes using(cnid) where skid = 1;
 select * from searchkeys2combonodes left join combonodes using(cnid) where skid = 2;
 select * from searchkeys2combonodes left join combonodes using(cnid) where skid = 3;
 

select * from searchkeys2combonodes natural join combonodes where skid = 1; und select * from searchkeys2combonodes natural join combonodes where skid = 3; liefern nun leere Ergebnisse.

Ein Explain zeigt mir folgendes

jointest=# explain select * from searchkeys2combonodes natural join combonodes where skid = 3;
                                                  QUERY PLAN                        
---------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.55 rows=1 width=56)
   Join Filter: (searchkeys2combonodes.lastaccessedby = combonodes.lastaccessedby)
   ->  Index Scan using searchkeys2combonodes_pkey on searchkeys2combonodes  (cost=0.00..8.27 rows=1 width=24)
         Index Cond: (skid = 3)
   ->  Index Scan using combonodes_pkey on combonodes  (cost=0.00..8.27 rows=1 width=48)
         Index Cond: (combonodes.cnid = searchkeys2combonodes.cnid)

 

das fiese ist nun der Join Filter: (searchkeys2combonodes.lastaccessedby = combonodes.lastaccessedby).

left join spuckt diese zeile einfach mit aus (ist ja eigentlich auch mit dem tabellenname als prefix behaftet). Natural Join aber baut diesen join filter ein.

da der natural join an sich aber sehr charmant ist ist meine frage ob ich diesen join filter irgendwie umgehen kann ?

EDIT: PG 8.3.8

So ganz versteh ich Dein Problem nicht: ein natural join verbindet Tabellen gemäß gleichlautender Spalten. Das sind hier cnid und lastaccessedby, und es gibt exakt nur eine Kombination, wo diese Spalten in beiden Tabellen dieselben Werte aufweisen. Von daher stimmt das Resultat mit dem überein, was man erwarten darf.


Andreas

ich hatte natural join so verstanden, das er anhand der tatsache das searchkeys2combonodes.cnid eine reference auf combonodes.cnid ist die beiden tabellen nur anhand von cnid joined und nicht zusätzlich noch nach lastaccessedby (ist ja eine reference auf eine tabelle die im join nicht erwähnt wird)

Im JOIN selbst ist exakt keine Spalte erwähnt.

test=# select * from searchkeys2combonodes natural join  combonodes where skid = 3;
 cnid | lastaccessedby | skid | comboname
------+----------------+------+-----------
(0 rows)

test=*# alter TABLE combonodes rename column lastaccessedby to lastaccessedby2;
ALTER TABLE
test=*# select * from searchkeys2combonodes natural join  combonodes where skid = 3;
 cnid | skid | lastaccessedby | comboname | lastaccessedby2
------+------+----------------+-----------+-----------------
    3 |    3 |              1 | combo3    |               3
(1 row)

Andreas

ja eben. da ja im natural join keine genaueren angaben stehen, ging ich davon aus das er zuerst auf die references schaut und danach erst auf spaltennamen gleichheit.

hab mich da ja offensichtlich geirrt und muss die queries auf left joins ändern.

danke dir