BUG: COUNT(*) Returns Unexpected Results When Used w/ SELECT

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

Last Reviewed: June 27, 1995