Microsoft KB Archive/109841

{| = BUG: COUNT(*) Returns Unexpected Results When Used w/ SELECT =
 * width="100%"|

ID: Q109841

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5x, 2.6, 2.6a
 * Microsoft FoxPro for MS-DOS, versions 2.5x, 2.6, 2.6a

SYMPTOMS
When the COUNT(*) function is used with the SELECT command, it produces unexpected results when an index tag exists on the field being extracted from the table.

CAUSE
The SET DELETED command is OFF. Note that this is the default setting.

WORKAROUND
CAUTION: Refer to the description of the SET DELETED command in the printed documentation or online Help before changing its setting.

Change the SET DELETED command to ON when using the COUNT(*) function.

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.

NOTE: This problem does not occur in FoxPro 2.0 for MS-DOS or FoxPro 2.5b for Macintosh.

MORE INFORMATION
The following sample code, without an index, produces the correct results:

CREATE TABLE xdata1 (fld C(2))

FOR i = 1 TO 4 INSERT INTO xdata1 (fld) VALUES ('AA') NEXT

* COUNT(*) returns 4, as expected

SELECT COUNT(*) FROM xdata1 WHERE fld = 'AA'

* COUNT(*) returns 4, as expected

SELECT COUNT(*) FROM xdata1 WHERE fld = 'AA' OR fld = 'AA' The following sample code, with an index, produces the incorrect results: SELECT xdata1 INDEX ON fld TAG fld

* COUNT(*) returns 4, as expected

SELECT COUNT(*) FROM xdata1 WHERE fld = 'AA'

* COUNT(*) returns 5, not expected

SELECT COUNT(*) FROM xdata1 WHERE fld = 'AA' OR fld = 'AA' Additional reference words: FoxDos FoxWin 2.50 2.50a 2.50b 2.60 2.60a buglist2.50 buglist2.50a buglist2.50b buglist2.60 buglist2.60a select count KBCategory: kbprg kbbuglist KBSubcategory: FxprgSql
 * }