>Hi Cetin,
>
>Just one question: What is the value of Set("Collate")?
>There have been various confusing results with queries when it is not set to MACHINE.
>HTH,
Hi Brien, George and Bill,
I came to an interesting point and I think everybody should be aware of this situation (By the way 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.
PS:Sorry I forgot to CC in first send and doubled this message.
Cetin