Issue Details (XML | Word | Printable)

Key: CORE-3283
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alexander Ryabushkin
Votes: 0
Watchers: 2
Operations

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

BAD PLAN with using LEFT OUTER JOIN in SUBSELECT

Created: 19/Dec/10 04:08 AM   Updated: 23/Feb/11 10:18 AM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.0.6, 2.5.0
Fix Version/s: 2.5.1, 2.0.7, 3.0 Alpha 1

Time Tracking:
Not Specified

Environment: Win XP, Firebird-2.5.0.26074
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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))
*/


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.