[firebird-support] How to specify manual plan for this kind of query?

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi
 
i need to modify plan generated by Firebird and specify manual plan.
I use Firebird 3.
 
example on employee.fdb (sample from taken from 
README.common_table_expressions.txt)
--------------------------------------------------------
WITH RECURSIVE
  DEPT_YEAR_BUDGET AS
  (
    SELECT B.FISCAL_YEAR, B.DEPT_NO, SUM(B.PROJECTED_BUDGET) AS BUDGET
      FROM PROJ_DEPT_BUDGET B
    GROUP BY B.FISCAL_YEAR, B.DEPT_NO
  ),
 
  DEPT_TREE AS 
  (
    SELECT D1.DEPT_NO, D1.HEAD_DEPT, D1.DEPARTMENT, CAST('' AS VARCHAR(255)) AS 
INDENT
      FROM DEPARTMENT D1
     WHERE HEAD_DEPT IS NULL
 
    UNION ALL
 
    SELECT D2.DEPT_NO, D2.HEAD_DEPT, D2.DEPARTMENT, H.INDENT || '  '
      FROM DEPARTMENT D2 JOIN DEPT_TREE H
        ON D2.HEAD_DEPT = H.DEPT_NO
  )
 
SELECT D3.DEPT_NO, 
 D3.INDENT || D3.DEPARTMENT AS DEPARTMENT,
 B_1993.BUDGET AS B_1993
 
  FROM DEPT_TREE D3
       LEFT JOIN DEPT_YEAR_BUDGET B_1993
    ON D3.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
 
--------------------------------------------------------
 
it generate plan
PLAN JOIN (DEPT_TREE D1 INDEX (RDB$FOREIGN6), DEPT_TREE D2 INDEX 
(RDB$FOREIGN6), SORT (B_1993 B INDEX (RDB$FOREIGN18, RDB$PRIMARY17)))
 
but if i put this plan witgout any modification to the query 
it say: 
 
--------------------------------------------------
Dynamic SQL Error
SQL error code = -104
Invalid command
there is no alias or table named D1 at this scope level.
--------------------------------------------------
 
 
How to put manual plan for this query?
 
In my real situation query plan looks like
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), JOIN (CTE 
T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
and i need to change it to 
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), HASH (CTE 
T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
 
regards,
Karol Bieniaszewski