BUG: Queries Return Incorrect Number of Records

• Microsoft FoxPro for UNIX, version 2.6

SYMPTOMS

Queries that run correctly in FoxPro on the MS-DOS, Windows, and Macintosh platforms might not return the same results in FoxPro for UNIX.

The problem occurs while querying records that have been indexed in a .CDX file using queries with AND or OR. When the size of the database in number of records is 0, 1, or 2 modulo 32 and the right side of the AND or OR in the query expression matches the last, next to last, or third to the last record respectively, then the query could return erroneous data in one of two ways:

• If the operator is OR, records at the end of the database that match the query may not be caught by the query; some records that match the WHERE condition are left out.
• If the operator is AND, records at the end of the database that match the left side of the AND may be selected even when the right side is false; some records that do not match the WHERE condition would be selected.

CAUSE

This problem happens only with .CDX indices and affects only the last one to three records in the file when the database contains 0 to 2 modulo 32 records and is queried with the AND or OR operators. The problem does not occur if the database is not indexed on the fields being queried or if it is indexed with an .IDX file.

RESOLUTION

The following patches may be applied to the FoxPro for UNIX binary (FoxPro.pr) or the FoxPro for UNIX runtime binary (foxr.pr) as appropriate. Make sure you back up your existing FoxPro binary file before applying either of these patches. It is very important that you apply the correct patch to the appropriate binary. Failure to apply the patch correctly could result in a serious problem with your FoxPro binary.

Script to patch FoxPro for UNIX binary:

: # # This script patches the foxpro.pr binary and must be executed as root # in the same directory as the binary, e.g.

#         su
#         cd /usr/lib/foxpro
#         cp foxpro.pr foxpro.pr.00
#         sh <whatever_you_name_this_file>
#

Script to patch FoxPro for UNIX runtime binary:

: # # This script patches the foxr.pr runtime binary and must be executed as # root in the same directory as the binary, e.g.

#         su
#         cd /usr/lib/foxpro
#         cp foxr.pr foxr.pr.00
#         sh <whatever_you_name_this_file>
#

MORE INFORMATION

The following examples use the CUSTOMER database from the /usr/lib/foxpro/tutorial directory. Twelve records have been added to the database to make the number of records in the database mod 32 equal zero.

The STATE field in the last record, for the purposes of this example, should contain CO. For testing, you could append the first twelve records in the database to the end of the database. This was the method used to obtain these results.

The first query works correctly, and returns 27 records:

   SELECT * FROM Customer WHERE state = "CO" OR state = "NC"

   SELECT * FROM Customer WHERE state = "NC" OR state = "CO"

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.