Len,

Courtesy of Anders Altberg, SET ANSI ON will give the same results for each pair of queries.


>My apologies to those who have already read this, and my thanks to Tamar for her responses in another thread. I thought I would repost this in a more appropriate category & hope some more people will look at it & give me more advice.
>
>Since Foxpro 2.6 (DOS & Windows), I have found some oddities in a very simple VfP select statement, involving a join between two tables & a filter condition.
>
>I get different results from seemingly identical queries, depending on whether the tables are joined first. In some cases the order of expesssions in the select statement can make a difference to the result set, even though it logically should not (examples 2 & 3) in particular.
>
>Below is the code used to generate the results. The table structures & field names have been arranged to simplify the queries. The problems occur regardless of whether or not the tables are indexed, so I haven't indexed. In the original tables, the fld1b & fld2b fields were key fields on which the join was made.
>
>In each instance, I would expect goodtbl{n} & badtbl{n} to contain the same results.
>
>I would expect examples 2 & 3 should give identical results, the only difference being the swapping of the numeric comparisons in the query. I would also expect examples 4 & 5 should give identical results, the only difference being a duplicate join expression.
>
>
>

>set safety off
>create table tbla (fld1a C(10), fld2a N(5,0))
>insert into tbla values ("ABCD", 1)
>insert into tbla values ("ABC", 2)
>use
>create table tblb (fld1b C(10), fld2b N(5,0))
>insert into tblb values ("ABC", 1)
>insert into tblb values ("ABC", 2)
>use
>
>select * from tbla, tblb into cursor tblc
>
>select * from tblc where (trim(fld1a)=trim(fld1b)) into cursor goodtbl1
>select * from tbla, tblb where (trim(fld1a)=trim(fld1b)) into cursor badtbl1
>
>select * from tblc where (fld2a=2) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor goodtbl2
>select * from tbla, tblb where (fld2a=2) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor badtbl2
>
>select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=2) into cursor goodtbl3
>select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=2) into cursor badtbl3
>
>select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor goodtbl4
>select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor badtbl4
>
>select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) into cursor goodtbl5
>select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) into cursor badtbl5
>