I just discovered that SET EXACT and SET ANSI affect whether or not a SELECT SQL statement will return deleted records as deleted or not.

Am I the only person who didn't know this?

According to the VFP9 help file in fact, SET EXACT is supposed to have no effect on a SELECT SQL statement at all. SET ANSI is supposed to affect how strings are compared in a SELECT statement. And I can't find anything in the help file to explain how deleted records are treated by EXACT and ANSI.

So why do they both affect how deleted records are retrieved?

Here is an example of what I'm seeing.

CLOSE DATABASES all

CREATE TABLE test (field1 c(1))
FOR x = 1 TO 10
  INSERT INTO test (field1) VALUES (chr(x+64))
ENDFOR

GOTO 8
DELETE

CLOSE DATABASES all
SET DELETED OFF
SET ANSI OFF
SET EXACT OFF

WAIT WINDOW "Ansi off, Exact off" NoWait
SELECT * FROM test	&& browse should show record 8 as deleted
CLOSE DATABASES all
SET EXACT ON

WAIT WINDOW "Ansi off, Exact ON" NoWait
SELECT * FROM test	&& browse should show no deleted records
CLOSE DATABASES all
SET EXACT OFF
SET ANSI ON

WAIT WINDOW "Ansi ON, Exact off" NoWait
SELECT * FROM test	&& browse should show no deleted records
CLOSE DATABASES all
SET EXACT OFF
SET ANSI OFF

WAIT WINDOW "Ansi off, Exact off" NoWait
SELECT * FROM test	&& browse should show record 8 deleted again