I have simple case where firebird is doing a natural scan when it imo
should use an index.
(Firebird 2.1.2 RC2 Superserver on Windows Vista 64bit)

Query is:
SELECT apos.f_nr FROM t_lieferscheinposition pos join t_apos apos on
pos.f_id_apos = apos.id_apos where pos.f_id_lieferschein = 100;
Plan
PLAN JOIN (APOS NATURAL, POS INDEX (FK_LP_APOS, FK_LP_LI))
Adapted Plan
PLAN JOIN (APOS NATURAL, POS INDEX (FK_LP_APOS, FK_LP_LI))

DDL is (simplified):
CREATE TABLE T_LIEFERSCHEINPOSITION (
ID_LIEFERSCHEINPOSITION INTEGER NOT NULL,
F_ID_LIEFERSCHEIN INTEGER NOT NULL,
F_ID_APOS INTEGER NOT NULL
);

ALTER TABLE T_LIEFERSCHEINPOSITION ADD CONSTRAINT
PK_LIEFERSCHEINPOSITION PRIMARY KEY (ID_LIEFERSCHEINPOSITION);

ALTER TABLE T_LIEFERSCHEINPOSITION ADD CONSTRAINT FK_LP_APOS FOREIGN KEY
(F_ID_APOS) REFERENCES T_APOS (ID_APOS) ON DELETE CASCADE ON UPDATE NO
ACTION;
ALTER TABLE T_LIEFERSCHEINPOSITION ADD CONSTRAINT FK_LP_LI FOREIGN KEY
(F_ID_LIEFERSCHEIN) REFERENCES T_LIEFERSCHEIN (ID_LIEFERSCHEIN) ON
DELETE CASCADE ON UPDATE NO ACTION;

CREATE TABLE T_APOS (
ID_APOS INTEGER NOT NULL,
F_NR VARCHAR(25)
);

ALTER TABLE T_APOS ADD UNIQUE (F_NR);

ALTER TABLE T_APOS ADD PRIMARY KEY (ID_APOS);


When i change the query to use a left join no natural scan is used:
SELECT apos.f_nr FROM t_lieferscheinposition pos left join t_apos apos
on pos.f_id_apos = apos.id_apos where pos.f_id_lieferschein = 100;
Plan
PLAN JOIN (POS INDEX (FK_LP_LI), APOS INDEX (RDB$PRIMARY36))
Adapted Plan
PLAN JOIN (POS INDEX (FK_LP_LI), APOS INDEX (INTEG_534))

Can someone please explain me this behaviour?
Why does the left join make the difference?
For every value in t_lieferscheinposition there must be a value in
t_apos because of the foreign key.
So imo the result when using an inner join should always be the same as
when i use a left join.

best regards,
Carsten











[Non-text portions of this message have been removed]