>>well if you're getting deleted records sometimes and not other times, I'd smell bug too.
>>
>>As you probably know, even when SQL result sets are returned that include DELETED() records, the delete mark is OFF. That is, a result set including deleted() records has NO DELETED RECORDS IN IT - the formerly deleted records are there in the result set but are not "deleted" any more.
>>
>>What version of VFP by the way?
>
>Version 9, SP1. I guess that means then that the bug is that with ANSI and EXACT set OFF, the deleted flag stays set in the returned record set. This means then that there is no way to do what I'm trying to do, and if I do it anyway, it will all come crashing down around my head when the bug gets fixed.
Probably so, but one additional qualifier needs to be added.
What do you get when you include NOFILTER, as SergeyB suggested, in your SELECT statements?
The real bug here may have more to do with it being a filtered result set than it being a general bug with SELECT.
You may not have noticed, but I've said all along that filteed results sets ought never to have been invented because they cause numerous other semi-related MIS-behaviours in VFP and they buy virtually nothing in return. My *guess* is that if you use NOFILTER you will get the documented behaviour.
cheers
>
>>
>>
>>
>>>>Not trying the code (yet)... until you try it with SET DELETED ON
>>>>
>>>>SET DELETED OFF means you GET deleted records included.
>>>
>>>Yes, but they only come back as 'deleted', if I set both ANSI and EXACT to OFF, and I can't make any sense out of why ANSI and EXACT control this behaviour. Is this documented anywhere?
>>>
>>>In this case I actually want deleted records returned to the cursor as deleted records, and it was happening sometimes and not others, so through experiment and comparing by DISPLAY STATUS, I discovered that ANSI and EXACT were causing the issue. Still sounds like a bug to me.
>>>
>>>>
>>>>
>>>>
>>>>>See line 12 in the code. Deleted is set off. Did you try running the code below? I'd appreciate it, and if you get different results, please let me know.
>>>>>
>>>>>>Well you do have SET DELETED OFF in your code.
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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
>>>>>>>CLOSE DATABASES all
>>>>>>>SET EXACT ON
>>>>>>>
>>>>>>>WAIT WINDOW "Ansi off, Exact ON" NoWait
>>>>>>>SELECT * FROM test
>>>>>>>CLOSE DATABASES all
>>>>>>>SET EXACT OFF
>>>>>>>SET ANSI ON
>>>>>>>
>>>>>>>WAIT WINDOW "Ansi ON, Exact off" NoWait
>>>>>>>SELECT * FROM test
>>>>>>>CLOSE DATABASES all
>>>>>>>SET EXACT OFF
>>>>>>>SET ANSI OFF
>>>>>>>
>>>>>>>WAIT WINDOW "Ansi off, Exact off" NoWait
>>>>>>>SELECT * FROM test
>>>>>>>
