I have a DBISAM table with 54 records (attached) and the dataset has the following query:-

select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
from gltran a
where
(a.tranno like {cFilter1='%'}
or upper(a.payee) like {cFilter2='%'}
or upper(a.desc) like {cFilter3='%'})
and a.trandate>={dDatefrom='2018-01-01'}
order by a.trandate, a.tranno

In a button click, I have the following code:-

  with database.dataset do begin
       AfterLoad:=AfterLoadds;
       params.clear;
       params.Add('cFilter1='+quotedstr('%%'));
       params.Add('cFilter2='+quotedstr('%%'));
       params.Add('cFilter3='+quotedstr('%%'));
       params.Add('dDatefrom='+quotedstr('2014-01-01'));
       database.LoadRows(database.dataset);
  end;

In the AfterLoadDS procedure, I display the number of rows in a TLabel:-

Label1.Caption:=IntToStr(database.dataset.RowCount);

If the table has 54 records and above, the RowCount will show 0 records. If I delete any record from the table to bring the record count to 53 records and below, RowCount will show the exact number of records in the table.

Is there an EWB anomaly happening here?

--
Frederick

I tried it here and it returned 54 records (55 when I added another one)

Chris Holland

[Team Elevate]


On 22/06/2018 09:52, Frederick Chin wrote:
> I have a DBISAM table with 54 records (attached) and the dataset has the following query:-
>
> select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
>   from gltran a
>   where
>   (a.tranno like {cFilter1='%'}
>   or upper(a.payee) like {cFilter2='%'}
>   or upper(a.desc) like {cFilter3='%'})
>   and a.trandate>={dDatefrom='2018-01-01'}
>   order by a.trandate, a.tranno
>
> In a button click, I have the following code:-
>
>     with database.dataset do begin
>          AfterLoad:=AfterLoadds;
>          params.clear;
>          params.Add('cFilter1='+quotedstr('%%'));
>          params.Add('cFilter2='+quotedstr('%%'));
>          params.Add('cFilter3='+quotedstr('%%'));
>          params.Add('dDatefrom='+quotedstr('2014-01-01'));
>          database.LoadRows(database.dataset);
>     end;
>
> In the AfterLoadDS procedure, I display the number of rows in a TLabel:-
>
> Label1.Caption:=IntToStr(database.dataset.RowCount);
>
> If the table has 54 records and above, the RowCount will show 0 records. If I delete any record from the table to bring the record count to 53 records and below, RowCount will show the exact number of records in the table.
>
> Is there an EWB anomaly happening here?
>
> --
> Frederick
>

Chris Holland wrote:

/*
I tried it here and it returned 54 records (55 when I added another one)
*/

I have included the project with the data files (GLTRAN54.* for 54 records and GLTRAN53.* for 53 records) and a video (TEST5.MP4) of the running program.

--
Frederick

On 6/22/2018 11:31 AM, Frederick Chin wrote:
> I have included the project with the data files (GLTRAN54.* for 54 records and GLTRAN53.* for 53 records) and a video (TEST5.MP4) of the running program.
>

I'm seeing it here as well and it does appear to be a dbisam related
somehow.

I tried query below with DBSYS 4.45 Build 3 and it returns 0 records (i
assume this is same version built into ewb hence the 0).

I had older dbsys 4.42 Build 2 around and that one returns 54 records.

Query:
select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
from gltran54 a
where (a.tranno like '%' or upper(a.payee) like '%' or upper(a.desc)
like '%') and a.trandate >= '2014-01-01'
order by a.trandate, a.tranno


This is definitely something Tim needs to look at

Raul

Frederick,

Raul is correct - it's an optimization bug in DBISAM 4.45.  The issue is with how DBISAM is handling the wildcards combined with a performance optimization added in 4.45 for making ANDs more efficient with set processing.  Luckily, the fix doesn't affect the performance optimization.

I'll have a fix for it out by tomorrow in a 4.45 Build 4.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

/*
I'll have a fix for it out by tomorrow in a 4.45 Build 4.
*/

Thanks.

I suppose that EWB also needs to be updated because the same query in the Preview tab of the Database Manager also returns 0 records.

--
Frederick

Frederick,

<< I suppose that EWB also needs to be updated because the same query in the Preview tab of the Database Manager also returns 0 records. >>

Yes, EWB will need updated, also.

Tim Young
Elevate Software
www.elevatesoft.com