>>>I tested this out and the two SQL statements return the same set of values on my machine.
>>
>>
Oh far worse, machine or OS dependant ? I tested it with many simple tables with values ranging from 1 to just 10. I use under w2k, maybe ? Speed (Athlon650 with 196Mb)? Thanks for trying.
>>Cetin
>
>I should have specified!
>
>I used tables with 1000 records - that shouldn' make a difference.
>
>Environment is VFP 6.0 SP3 in W2K, Pentium II-300 w 128MB RAM. I can't think of anything about the environment (except possibly version of VFP) that could make a difference. Maybe a Fox setting that's causing the difference?
Hi Brien, George and Bill,
I came to an interesting point and I think everybody should be aware of this situation (BTW I added it to my list of BUGS). Before I forget Bill, everything including set collate are at their VFP default (machine).
-If right side has a regular index or no index then both SQLs return the same n records set.
-If right side has a candidate or primary index on that field then second SQL returns 0 records (if you make it 0x7FFFFFFF then both return the same set)
Here is a test.prg to show it :
Clear all
Close all
Create cursor test1 (pkid i, square b)
For ix=1 to 10
Insert into test1 values (ix,ix^2)
Endfor
Create cursor test2 (pkid i, cube b)
For ix=1 to 10
Insert into test2 values (ix,ix^3)
Endfor
Index on pkid TAG pkid candidate
Select * from test1 a ;
inner join test2 b ;
on a.pkid = b.pkid ;
where ;
a.pkid between 1 and 0xFFFFFFFF
Select * from test1 a ;
inner join test2 b ;
on a.pkid = b.pkid ;
where ;
b.pkid between 1 and 0xFFFFFFFF
Commenting index line or changing 0xFFFFFFFF to 0x7FFFFFFF in second return correct set.
Cetin