Hi:

I believe that I found a BUG in firebird 1,03 and 1,5 and it seems to me
enough serious because affects the yield of certain type of querys

Bug would be sgte.

I have a table head and another detail

When I make a query that takes part columns type to integer (in the head
and the detail) the query works OK.

The problem this when in the detail the query with a condition is made
that includes a column type to char.

Example:

select
cab.asiento,det.importe_movimiento,det.movimiento,det.codigo_cuenta
from fcaja_cab cab
inner join fcaja_det det on det.asiento=cab.asiento
where cab.fecha_asiento = '11/02/2002' and det.nro_compra=1

The plan that uses is:

Plan
PLAN JOIN (CAB INDEX (FCAJA_CAB_IDX_FECHA),DET INDEX
(RDB$PRIMARY33,FCAJA_DET_IDX1))

Adapted Plan
PLAN JOIN (CAB INDEX (FCAJA_CAB_IDX_FECHA),DET INDEX
(PK_FCAJA_DET,FCAJA_DET_IDX1))


As they see first looks for in the head and soon it looks for in the
detail.

Ok Performance!!!!!!!!


If change the query:

select
cab.asiento,det.importe_movimiento,det.movimiento,det.codigo_cuenta
from fcaja_cab cab
inner join fcaja_det det on det.asiento=cab.asiento
where cab.fecha_asiento = '11/02/2002' and
det.codigo_cuenta='010102010003'


The plan that uses is:

Plan
PLAN JOIN (DET INDEX (RDB$FOREIGN2),CAB INDEX
(RDB$PRIMARY31,FCAJA_CAB_IDX_FECHA))

Adapted Plan
PLAN JOIN (DET INDEX (FK_FCAJA_DET_FCUENTAS),CAB INDEX
(PK_FCAJA_CAB,FCAJA_CAB_IDX_FECHA))


As they see first looks for in the DETAIL!!!!! And soon looks for in
the head which causes that the query takes very slow because it are
first all registry of the detail that umplan with the condition and soon
leak by the condition of the head. Very bad performance!!!!!!!!!!!


In the first case read 1 row in head and 1 row in detail. Result 1 row
(according to IBexpert)

In the second case read 5789 rows in detail and 1 in head. Result 1 row.

As they imagine as it grows the detail more takes the query.

Somebody knows where I must report this Bug and preferably in Spanish my
then English is very bad

Thanks

Edgar Brítez
Sebaot Software
Asunción - Paraguay