Hi Helen, Andrew, Sven et al,

I've gone through the Sourceforge bug tracker and I've
found a bug submitted by Mustafa Yilmaz. He describes
quite the same behaviour, except that he joins two views:

---------------------------------------------------------
[ 508594 ] LEFT JOIN with VIEWs
Date:
2002-01-25 10:29 Priority:
5
Submitted By:
Mustafa YILMAZ (myilmaz) Assigned To:
Nobody/Anonymous (nobody)
Category:
Core Engine Status:
Open
Summary:
LEFT JOIN with VIEWs
bad plan with view
test case

CREATE TABLE TABLE1
(
T1F1 INTEGER NOT NULL PRIMARY KEY,
T1F2 VARCHAR(40)
);

CREATE TABLE TABLE2
(
T2F1 INTEGER NOT NULL PRIMARY KEY,
T2F2 INTEGER,
T2F3 VARCHAR(40),
FOREIGN KEY (T2F2) REFERENCES TABLE1(T1F1)
);

CREATE VIEW VIEW1
AS
SELECT * FROM TABLE1;

CREATE VIEW VIEW2
AS
SELECT * FROM TABLE2;

SELECT *
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.T1F1 = T2.T2F2
/*
USED PLAN JOIN (T1 NATURAL,T2 INDEX (RDB$FOREIGN10))
*/

SELECT *
FROM VIEW1 V1
LEFT JOIN VIEW2 V2 ON V1.T1F1 = V2.T2F2
/*
USED PLAN JOIN (V1 TABLE1 NATURAL,V2 TABLE2 NATURAL)
*/
---------------------------------------------------------

If Helen is d'accord, I would like to write a second comment
to this bug stating that I encountered the problem, too.

I could also give some additional information which might
help the FB programmers to track this bug down (eg. that
FB refuses the index even when you specify it in a plan).


Christian