Subject Possible Bug in Parser: Sub-Selects with Integer and Date Fields In Firebird
Author Eduardo Jedliczka
Delphi 6 SP 2, IBO 4.2Ib, Firebird 1.03.972 and Firebird 1.5 RC 4, Win 98

Hello to All. Greetings.

We have this "very simple" select with one subselect.

Select Count(Distinct NumLote) as Total,
(Select Count(distinct NumLote) from Co_Lct Where ((CodEmpresa=:CodEmpresa)
and (Identificacao starting with 'LA'))) as TotLote
from Co_Lct where ((CodEmpresa=:CodEmpresa) and (Data between :DataInicial
and :DataFinal))

Have only one table used: Co_Lct with this structure:
CodEmpresa SmallInt,
NumLote Integer,
Data Date,
Identificacao Varchar(18)

With no Primary Key, and various (non-unique) Index or Foreign Keys.

Some times, this select not work in IBO (checking this query in the
queryeditor - Params) it shows parameters as:
CodEmpresa Date,
DataInicial smallint,
DataFinal Date,
CodEmpresa smallint.

And the most strange is: when we do a database.disconnect, followed by
database.connect, it works fine!

Fragment of Delphi Code:

With DM.QyAux do
begin
If Active then Close;
UnPrepare;
OldParamValueLinks.Clear;
KeyLinksAutoDefine := True;
FetchWholeRows := True;
OrderingItems.Clear;
OrderingLinks.Clear;
KeyLinks.Clear;
SQL.Clear;
Sql.Add( 'Select Count(Distinct NumLote) as Total,');
Sql.Add( '(Select Count(distinct NumLote) from Co_Lct Where
((CodEmpresa=:CodEmpresa) and (Identificacao starting with ''LA''))) as
TotLote');
Sql.Add( 'from Co_Lct where ((CodEmpresa=:CodEmpresa) and (Data between
:DataInicial and :DataFinal))');
Prepare;
ParamByName('CodEmpresa').AsInteger := VarPub.CodEmpresa; // <---
Error message ('14' is not a valid date ...) at this point.
ParamByName('DataInicial').AsDate := StrToDate(EdDataInicial.Text);
ParamByName('DataFinal').AsDate := StrToDate(EdDataFinal.Text);
Open;
First;
end;

How resolve it ?

Thanks for All

=======================
Eduardo Jedliczka
Gerasoft - Informática
Apucarana - PR - Brasil
=======================