Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

LEFT JOIN with VIEWs [CORE350] #690

Closed
firebird-automations opened this issue Jan 25, 2002 · 3 comments
Closed

LEFT JOIN with VIEWs [CORE350] #690

firebird-automations opened this issue Jan 25, 2002 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Mustafa YILMAZ (myilmaz)

Assigned to: @ArnoBrinkman

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10374 ] => Firebird [ 14633 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant