With Firebird 2.1.3

With the select:

select
(select first 1
P1.ARTICULO
from GES_LIN_ROLLOS L1
inner join EMP_LOTES P1 on
P1.EMPRESA = L1.EMPRESA and
P1.NUMERO = L1.PIEZA
where
L1.EMPRESA = R.EMPRESA and
L1.ROLLO = R.ROLLO and
P1.ARTICULO = 'ABCDEFGHI') as ARTICULO ,
R.EMPRESA,
R.ROLLO
from GES_CAB_ROLLOS R
inner join EMP_ARTICULOS A on
A.EMPRESA = R.EMPRESA and
A.ARTICULO = (select first 1
P2.ARTICULO
from GES_LIN_ROLLOS L2
inner join EMP_LOTES P2 on
P2.EMPRESA = L2.EMPRESA and
P2.NUMERO = L2.PIEZA
where
L2.EMPRESA = R.EMPRESA and
L2.ROLLO = R.ROLLO and
P2.ARTICULO = 'ABCDEFGHI')
where
R.EMPRESA = 1 and
exists (select 1 from GES_LIN_ROLLOS L3
inner join EMP_LOTES P3 on
P3.EMPRESA = L3.EMPRESA and
P3.NUMERO = L3.PIEZA
where
L3.EMPRESA = R.EMPRESA and
L3.ROLLO = R.ROLLO and
P3.ARTICULO = 'ABCDEFGHI')
order by R.EMPRESA, 1, R.ROLLO

the plan is:

PLAN JOIN (L2 INDEX (FK_GES_LIN_ROLLOS_ROLLO), P2 INDEX
(IDX_EMP_LOTES_NUMERO_DESC))
PLAN JOIN (L3 INDEX (FK_GES_LIN_ROLLOS_ROLLO), P3 INDEX
(IDX_EMP_LOTES_NUMERO_DESC))
PLAN JOIN (L1 INDEX (FK_GES_LIN_ROLLOS_ROLLO), P1 INDEX
(IDX_EMP_LOTES_NUMERO_DESC))
PLAN JOIN (L1 INDEX (FK_GES_LIN_ROLLOS_ROLLO), P1 INDEX
(IDX_EMP_LOTES_NUMERO_DESC))
PLAN SORT (JOIN (R INDEX (PK_GES_CAB_ROLLOS), A INDEX (PK_EMP_ARTICULOS)))

Double L1,P1 by order, but good index in all plans.

With derived tables the select:

select
R.ARTICULO,
R.EMPRESA,
R.ROLLO
from
(select
(select first 1
P1.ARTICULO
from GES_LIN_ROLLOS L1
inner join EMP_LOTES P1 on
P1.EMPRESA = L1.EMPRESA and
P1.NUMERO = L1.PIEZA
where
L1.EMPRESA = R.EMPRESA and
L1.ROLLO = R.ROLLO and
P1.ARTICULO = 'ABCDEFGHI') as ARTICULO ,
R.EMPRESA,
R.ROLLO
from GES_CAB_ROLLOS R) as R
inner join EMP_ARTICULOS A on
A.EMPRESA = R.EMPRESA and
A.ARTICULO = R.ARTICULO
where
R.EMPRESA = 1 and
exists (select 1 from GES_LIN_ROLLOS L3
inner join EMP_LOTES P3 on
P3.EMPRESA = L3.EMPRESA and
P3.NUMERO = L3.PIEZA
where
L3.EMPRESA = R.EMPRESA and
L3.ROLLO = R.ROLLO and
P3.ARTICULO = 'ABCDEFGHI')
order by R.EMPRESA, R.ARTICULO, R.ROLLO

the plan is:

PLAN JOIN (R L1 INDEX (FK_GES_LIN_ROLLOS_ROLLO), R P1 INDEX
(IDX_EMP_LOTES_NUMERO_DESC))
PLAN JOIN (L3 NATURAL, P3 INDEX (IDX_EMP_LOTES_NUMERO_DESC))
PLAN SORT (JOIN ((R R INDEX (PK_GES_CAB_ROLLOS)), A INDEX
(PK_EMP_ARTICULOS)))

L1 is using index but L3 is natural.

If I try to force the plan with:

select
R.ARTICULO,
R.EMPRESA,
R.ROLLO
from
(select
(select first 1
P1.ARTICULO
from GES_LIN_ROLLOS L1
inner join EMP_LOTES P1 on
P1.EMPRESA = L1.EMPRESA and
P1.NUMERO = L1.PIEZA
where
L1.EMPRESA = R.EMPRESA and
L1.ROLLO = R.ROLLO and
P1.ARTICULO = 'ABCDEFGHI') as ARTICULO ,
R.EMPRESA,
R.ROLLO
from GES_CAB_ROLLOS R) as R
inner join EMP_ARTICULOS A on
A.EMPRESA = R.EMPRESA and
A.ARTICULO = R.ARTICULO
where
R.EMPRESA = 1 and
exists (select 1 from GES_LIN_ROLLOS L3
inner join EMP_LOTES P3 on
P3.EMPRESA = L3.EMPRESA and
P3.NUMERO = L3.PIEZA
where
L3.EMPRESA = R.EMPRESA and
L3.ROLLO = R.ROLLO and
P3.ARTICULO = 'ABCDEFGHI'
plan join (L3 index (FK_GES_LIN_ROLLOS_ROLLO), P3 index
(IDX_EMP_LOTES_NUMERO_DESC)))
order by R.EMPRESA, R.ARTICULO, R.ROLLO

I get the error:

index FK_GES_LIN_ROLLOS_ROLLO cannot be used in the specified plan.

Is it a bug?

Changing all "inner join" to "left join" is same result.

Thank you.
Ruben Marti.


[Non-text portions of this message have been removed]