BAD PLAN with using LEFT OUTER JOIN in SUBSELECT [CORE3283] #3651
Description
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))
*/