Issue Details (XML | Word | Printable)

Key: CORE-350
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Arno Brinkman
Reporter: Mustafa YILMAZ
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

LEFT JOIN with VIEWs

Created: 25/Jan/02 12:00 AM   Updated: 14/Jun/06 09:38 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 1.5.0

Time Tracking:
Not Specified

SF_ID: 508594


 Description  « Hide
SFID: 508594#
Submitted By: myilmaz

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)
*/

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:38 AM
Date: 2002-01-25 23:30
Sender: nobody
Logged In: NO

We ran into similar issue, this time with just tables. It
seems that doing a mix of inner and left outer joins on the
same table have often broken plan.

And the best part of it, if we try to force the correct plan
(by using PLAN clause), Firebird just crashes :-(

If someone is actually interested, I can mail entire DB
along with the SQL used.