Tuesday, May 01, 2007

VFP9 bug with _TALLY, RECCOUNT, DELETED, and an INDEX

Maybe I'm missing something (and I hope I am). The following condition exists in VFP7 through to VFP9SP1 (and maybe earlier than 7). I don't remember ever reading anything about it either.

This one is hard to explain (and believe), so you may need to run the example code to see what I mean. And right off the bat: This issue has nothing to do with the RECCOUNT/_TALLY results we all know about regarding deleted records (RECCOUNT always returns the number of rows in the table regardless of the SET DELETED setting). No, this issue has something to do with an index and the current setting of DELETED. When an index is set on a column involved in a WHERE clause of an SQL statement, RECCOUNT will return the total number of records from the 'FROM' table instead of the results from the cursor when SET DELETED is OFF! It seems as though the resulting cursor has a bunch of deleted records (the scrollbar in the browse window only goes half-way up (if you know what I mean)), but they can't be accessed regardless of the SET DELETED setting.

The problem is with code like this:


SET DELETED OFF
SELECT sometable
SET INDEX ON somefield && can exist in a CDX too
SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
? RECCOUNT('crTemp') && wrong! RECCOUNT returns the wrong reccount!
? _TALLY && correct results


Here's a complete example, using a table called 'name_list', which has a column for name c(30) and some deleted records. In the first example, the table name_list does NOT have an index on 'name'. RECCOUNT and _TALLY return expected results.


USE name_list IN 0 EXCL

SET DELETED OFF

SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF: "
?? _TALLY && 81,019
?? RECCOUNT('crName') && 81,019

SET DELETED ON

SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY && 80,951
?? RECCOUNT('crName') && 80,951


Now, add an index to the name column and be amazed:


USE name_list IN 0 EXCL
INDEX ON name TAG name

SET DELETED OFF

SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF: "
?? _TALLY && 81,019
?? RECCOUNT('crName') && 126,607 -- the actual number of records in name_list!?!

SET DELETED ON

SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY && 80,951
?? RECCOUNT('crName') && 80,951


In the above example, it doesn't matter if the index is set to name (SET ORDER TO name) or not! Just the fact that the index exists throws off RECCOUNT(). If you do a count on crName with DELETED OFF, you get the expected results:


USE name_list IN 0 EXCL
INDEX ON name TAG name

SET DELETED OFF

SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF: "
?? _TALLY && 81,019
?? RECCOUNT('crName') && 126,607 -- the actual number of records in name_list!?!
COUNT TO ln
?? ln && 81,019



The moral is not to use RECCOUNT() to get the total number of records from
an SQL statement. Always use _TALLY. But unfortunately, this undocumented behavior has certainly crept into someone's code somewhere. Consider a situation where you want to test to see if the SQL returns any records. You might do something like this:


SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
IF RECCOUNT() > 0
...
ENDIF


This code is fatal if in fact there happens to be an index on somefield and SET DELETED is OFF! If anyone has some insight, I'd love to hear it...

6 comments:

Sergey said...

Hi Tod,

See explanation at http://www.berezniker.com/display/VFP/Queries+and+Filtered+Cursors

Sergey

Tod McKenna said...

Wow Sergey, thank you. This snippet explains it all: "VFP creates the filtered cursor by opening the source table similar to USE AGAIN command, applying SET FILTER to it and limiting the list of fields similar to SET FIELDS. It could be much faster than creating actual cursor because no data transfer takes place but it comes with a price. The filtered cursors have following limitations/side effects"

So certainly commands like RECCOUNT and RECNO will be affected!

Thanks again Sergey, you've always been a great help to the community.

Tod McKenna said...

Even though this can be explained, is it still a bug? When I SET OPTIMIZE OFF, I still get the same behavior. It seems that setting optimize off (i.e. disable rushmore) should prevent this from working this way.

Randy Jean said...

I was bit by this years ago, I think back in Foxpro 2.0 - thankfully they added the NOFILTER clause. It was a pain to have to query to a temp dbf just to avoid this before.

Tod McKenna said...

I'm not totally surprised that I haven't hit this before. As far back as I can remember, I've always relied on _TALLY.

Goran said...

The NOFILTER clause is truly the only way to be sure that this problem is not impacting your results. We use NOFILTER everywhere in fact our code reviews pick up any SQL that does not include NOFILTER. The performance hit it causes is less of an impact than the potential for error if it weren't there.