QueryRecCount gives incorrect recordcount

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
kneighbour
Posts: 71
Joined: Wed 08 Oct 2008 04:55

QueryRecCount gives incorrect recordcount

Post by kneighbour » Fri 30 Aug 2019 04:12

I use Firebird 3, Delphi XE7 and UniDac 8.0.1. I have just found one certain query (and possibly others) that returns a totally incorrect recordcount

I drop on a TUniQuery and set Options.QueryRecCount:=true and Readonly:=true;
I get the query.recordcount = 88,349

If I then do a While not query.EOF do
begin
query.next;
end;

This loop will occur 1,050 times, the actual recordcount. So internally the dataset is actually 1050 records.
I can also load the query Options.QueryRecCount:=false and then
query.Last;
query.First;
I get the correct query.recordcount = 1050.

This is the query

Code: Select all

SELECT DISTINCT
   INVDTBL.*,
   INVHTBL.*
FROM
  INVDTBL
INNER JOIN INVHTBL ON (INVDTBL.INVD_ORDER_NO = INVHTBL.INVH_ORDER_NO)
INNER JOIN ITEMTBL ON (INVDTBL.INVD_ITEM_NUMBER = ITEMTBL.ITEM_NUMBER)
INNER JOIN LOCATIONITEMS ON (LOCATIONITEMS.ITEMID = ITEMTBL.ITEM_NUMBER)
WHERE
 (INVD_ITEM_NUMBER <>'') 
 AND (INVH_INV_DATE >='2017-03-01' and INVH_INV_DATE <='2017-03-24')
 AND ((INVHTBL.INVH_STATUS= 'CrNote') OR (INVHTBL.INVH_STATUS= 'Invoice'))
AND  (ITEMTBL.ITEM_ACTIVE_YN='Yes') AND (LOCATIONITEMS.ACTIVE_YN=1)
ORDER BY
  INVD_ITEM_NUMBER ASC
I understand that you cannot duplicate this problem using this query. I present it here as a possible bug in this version of UniDac. If I ever want to determine a recordcount, I generally use the "QueryRecCount=True" setting as that seems to be faster than doing the Last/First thing to determine the recordcount.

It seems that this method is now for some reason unreliable. This is a serious problem as the recordcount can be used to set progressbars, warning messages, etc. If QueryRecCount is not reliable, can you suggest a better/faster way to get this count?

ViktorV
Devart Team
Posts: 2856
Joined: Wed 30 Jul 2014 07:16

Re: QueryRecCount gives incorrect recordcount

Post by ViktorV » Fri 30 Aug 2019 10:38

Unfortunately, we cannot reproduce the problem in our environment based on the data you provide.
Please, try to compile and send us using the contact form https://devart.com/company/contactform.html an example of a reproducing error, including scripts for creating and populating database objects.

Post Reply