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

BAD PLAN with using LEFT OUTER JOIN in SUBSELECT [CORE3283] #3651

Closed
firebird-automations opened this issue Dec 19, 2010 · 10 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alexander Ryabushkin (aryab)

Is related to CORE3103
Is related to QA258

CREATE TABLE TREE_SUBJ (
ID_FOLDER INTEGER NOT NULL PRIMARY KEY);

INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (0);
INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (1);
INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (2);
INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (3);
INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (4);
INSERT INTO TREE_SUBJ (ID_FOLDER) VALUES (5);
commit;

select T1.ID_FOLDER from TREE_SUBJ T1
where T1.ID_FOLDER =
(select first 1 T3.ID_FOLDER from TREE_SUBJ T2
LEFT OUTER JOIN TREE_SUBJ T3 on( T3.ID_FOLDER > T2.ID_FOLDER)
where (T2.ID_FOLDER = 3) order by T3.ID_FOLDER)
/* FB 2.5 BAD PLAN with LEFT
PLAN SORT (JOIN (T2 INDEX (RDB$PRIMARY1), T3 INDEX (RDB$PRIMARY1)))
PLAN (T1 NATURAL) */

/* FB 2.1 OK PLAN with LEFT
PLAN SORT (JOIN (T2 INDEX (RDB$PRIMARY1), T3 INDEX (RDB$PRIMARY1)))
PLAN (T1 INDEX (RDB$PRIMARY1)) */

select T1.ID_FOLDER from TREE_SUBJ T1
where T1.ID_FOLDER =
(select first 1 T3.ID_FOLDER from TREE_SUBJ T2
JOIN TREE_SUBJ T3 on( T3.ID_FOLDER > T2.ID_FOLDER)
where (T2.ID_FOLDER = 3) order by T3.ID_FOLDER)
/* FB 2.5, 2.1 OK PLAN with JOIN
PLAN SORT (JOIN (T2 INDEX (RDB$PRIMARY1), T3 INDEX (RDB$PRIMARY1)))
PLAN (T1 INDEX (RDB$PRIMARY1))
*/

Commits: 676b99c a70ec41 8815c71 5c3894e

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE3103 [ CORE3103 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.0.7 [ 10390 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA258 [ QA258 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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