VERY Strange Problem with Select in VFP 9

[Demetrios Panayotakopoulos]

Hi,

This had never happened me again!! using VFP 9.

The table had about 200.000 records

Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records

2) Select * from Table where (...a...) into cursor c1
Returned 250 records

3) Select * from Table where (...b...) into cursor c2
Returned 180 records

250+180 <> 400 !!!!!


OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record.
Nothing common.

I lost about an hour to understand what the h..l was going on.

I quit Fox and started again. The problem was there.

Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).

What was that?

I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the memory
limits.

Demetrios, Greece

[tom knauf]

Hello,

first question :

what is the correct answer ?
If its 1) like you wrote, there MUST be "wrong records" or "doubles" in 2)
or 3),
otherwise it could not be more hits then 1)

Did you try with select COUNT(pk..) where ....

Best regards
tom

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record.
Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told me
that he had faced similar problems when he was working to the memory
limits.
Demetrios, Greece

[Demetrios Panayotakopoulos]

Hi Tom,

Recs (1) ( <--this was the correct) = (should be -->) Recs (2)+ Recs (3)

It was OK after logout & Login

As I first wrote, I lost about an hour, before exiting and reentering my
account.
I had done all tests.
The (a) and (b) expressions were very clear.

Let me put it this way.
It's like you had persons devided to males an females.
The value of sex can only be 'M' or 'F'.
(Of course the expression wasn't that simple)


I, did the test again a few minutes ago. Everything went OK.
The correct number of results were returned.

I also issued the followin as Tushar suggested
" what does WHERE (...a....) AND (...b....) give ?"

(
http://social.msdn.microsoft.com/Forums/en-US/visualfoxprogeneral/thread/b1691bce-acb3-4c51-88f1-564f63d4478a
)


and returned 0 records.

Demetrios, Greece

Post by tom knauf
Hello,
what is the correct answer ?
If its 1) like you wrote, there MUST be "wrong records" or "doubles" in
2) or 3),
otherwise it could not be more hits then 1)
Did you try with select COUNT(pk..) where ....
Best regards
tom

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record.
Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece

[Tony W.]

Hi,

If c0 == c1 + c2,

I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).

For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.

Since your problem cannot be resembled, I think you can forget about it.


Tony

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record. Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told me that he had faced similar problems when he was
working to the memory limits.
Demetrios, Greece

[Demetrios Panayotakopoulos]

Hi Tony

I had and have done all the tests.
The expressions had no problem.
They should return the correct number of recorde and they didn't for
some reason. And that scared me, since for the same unknown reason this
may happen again and nobody may notice it.

Demetrios, Greece

Post by Tony W.
Hi,
If c0 == c1 + c2,
I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).
For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.
Since your problem cannot be resembled, I think you can forget about it.
Tony

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record. Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece

[Al Marino]

it would help if you gave the actual select conditions, particularly if it
involved udf's
otherwise, I would suggest you turn off windows write cache and test again
I have seen incorrect results with windows write cache on

al

Post by Demetrios Panayotakopoulos
Hi Tony
I had and have done all the tests.
The expressions had no problem.
They should return the correct number of recorde and they didn't for some
reason. And that scared me, since for the same unknown reason this may
happen again and nobody may notice it.
Demetrios, Greece

Post by Tony W.
Hi,
If c0 == c1 + c2,
I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).
For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.
Since your problem cannot be resembled, I think you can forget about it.
Tony

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record. Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece

[Demetrios Panayotakopoulos]

Hi Al,

That might be a solution.
BUT, suppose that you sell an application you cant't tell your customer
that database engine may fail if you have the write cache on.

Demetrios, Greece

Post by Al Marino
it would help if you gave the actual select conditions, particularly if
it involved udf's
otherwise, I would suggest you turn off windows write cache and test again
I have seen incorrect results with windows write cache on
al

Post by Demetrios Panayotakopoulos
Hi Tony
I had and have done all the tests.
The expressions had no problem.
They should return the correct number of recorde and they didn't for
some reason. And that scared me, since for the same unknown reason
this may happen again and nobody may notice it.
Demetrios, Greece

Post by Tony W.
Hi,
If c0 == c1 + c2,
I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).
For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.
Since your problem cannot be resembled, I think you can forget about it.
Tony

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record. Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece

[Al Marino]

if disable write cache works, then you can
1. make sure you use flush() after updates

2. attached is what we send our customers, when necessary

al

Post by Demetrios Panayotakopoulos
Hi Al,
That might be a solution.
BUT, suppose that you sell an application you cant't tell your customer
that database engine may fail if you have the write cache on.
Demetrios, Greece

Post by Al Marino
it would help if you gave the actual select conditions, particularly if
it involved udf's
otherwise, I would suggest you turn off windows write cache and test again
I have seen incorrect results with windows write cache on
al

Post by Demetrios Panayotakopoulos
Hi Tony
I had and have done all the tests.
The expressions had no problem.
They should return the correct number of recorde and they didn't for
some reason. And that scared me, since for the same unknown reason
this may happen again and nobody may notice it.
Demetrios, Greece

Post by Tony W.
Hi,
If c0 == c1 + c2,
I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).
For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.
Since your problem cannot be resembled, I think you can forget about it.
Tony

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record.
Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece

[Demetrios Panayotakopoulos]

Very Interesting!
Thank you Al

Post by Al Marino
if disable write cache works, then you can
1. make sure you use flush() after updates
2. attached is what we send our customers, when necessary
al

Post by Demetrios Panayotakopoulos
Hi Al,
That might be a solution.
BUT, suppose that you sell an application you cant't tell your
customer that database engine may fail if you have the write cache on.
Demetrios, Greece

Post by Al Marino
it would help if you gave the actual select conditions, particularly if
it involved udf's
otherwise, I would suggest you turn off windows write cache and test again
I have seen incorrect results with windows write cache on
al

Post by Demetrios Panayotakopoulos
Hi Tony
I had and have done all the tests.
The expressions had no problem.
They should return the correct number of recorde and they didn't for
some reason. And that scared me, since for the same unknown reason
this may happen again and nobody may notice it.
Demetrios, Greece

Post by Tony W.
Hi,
If c0 == c1 + c2,
I suggest to select records from Table where NOT (...a...)
and compare those records selected from Table where (...b...).
For debugging purposes, you had better output c0, c1 and c2
into tables instead of cursors.
Since your problem cannot be resembled, I think you can forget about it.
Tony
容主

Post by Demetrios Panayotakopoulos
Hi,
This had never happened me again!! using VFP 9.
The table had about 200.000 records
Example
1) Select * from Table where (...a...) or (...b...) into cursor c0
Returned 400 records
2) Select * from Table where (...a...) into cursor c1
Returned 250 records
3) Select * from Table where (...b...) into cursor c2
Returned 180 records
250+180 <> 400 !!!!!
OK, everyone would think that somehow there were some common records.
I new that they weren't.
I compared c1 and c2, was easy since I had unique IDs on each record.
Nothing common.
I lost about an hour to understand what the h..l was going on.
I quit Fox and started again. The problem was there.
Finally, I quit Fox. Logged Out and started again and everything was OK.
Commands (2) and (3) returned the correct number of records == (1).
What was that?
I described this to a colleague who uses Microsoft Access, and he told
me that he had faced similar problems when he was working to the
memory limits.
Demetrios, Greece