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