>>SELECT-SQL command have different results in VFP8 and VFP9 Thread #1010955 Message #1010955
>
>It looks like VFP9 bug to me. The query #1 should return only 3 records as in VFP8. Notice that field2 in the result is Null instead of blank for the records that shouldn't be included.
>The query #2 correctly returns 3 records.
>
>

CREATE CURSOR taba ( ;
>		ID C(4), ;
>		field1 C(10), ;
>		field2 C(1) ;
>	)
>
>CREATE CURSOR tabb ( ;
>		ID C(6), ;
>		field3 C(1) ;
>	)
>
>INSERT INTO taba VALUES( "0101",  "desc 1", "")
>INSERT INTO taba VALUES( "0201",  "desc 2", "X")
>
>INSERT INTO tabb VALUES( "010101" ,"1")
>INSERT INTO tabb VALUES( "010102" ,"2")
>INSERT INTO tabb VALUES( "010103" ,"3")
>INSERT INTO tabb VALUES( "020101", "1")
>INSERT INTO tabb VALUES( "020102", "2")
>INSERT INTO tabb VALUES( "020103", "3")
>
># 1
>select * from tabb ;
>	LEFT join taba on left(tabb.id,4)=taba.id ;
>		WHERE not empty(taba.field2)
># 2
>select * from tabb ;
>	LEFT join taba on left(tabb.id,4)=taba.id ;
>		WHERE EMPTY(taba.field2)
>

I post to you because you are out of the forward copy list

This is a optimization bug,
but to use EMPTY() with OUTER JOIN it is a risk:

* the bug
- taba is filtered with the where condition NOT EMPTY(taba.field2) before to the join
- the left join return taba.field2 = NULL for the filtered rows
- now NOT EMPTY(taba.field2  ( =NULL ) ) is true.

* the risk
CREATE CURSOR taba ( ;
		ID C(4), ;
		field1 C(10), ;
		field2 C(1) ;
	)

CREATE CURSOR tabb ( ;
		ID C(6), ;
		field3 C(1) ;
	)

INSERT INTO taba VALUES( "0101",  "desc 1", "") 
INSERT INTO taba VALUES( "0201",  "desc 2", "X")


INSERT INTO tabb VALUES( "010101" ,"1")  
INSERT INTO tabb VALUES( "010102" ,"2")  
INSERT INTO tabb VALUES( "010103" ,"3")  
INSERT INTO tabb VALUES( "020101", "1")  
INSERT INTO tabb VALUES( "020102", "2")  
INSERT INTO tabb VALUES( "020103", "3") 

* this show best the issue
INSERT INTO tabb VALUES( "020003", "1") 

SET ANSI ON
SET NULLDISPLAY TO '!NULL!'

select *,NOT EMPTY(taba.field2) whereCondition from tabb ;
	LEFT join taba on taba.id=left(tabb.id,4) ;
		WHERE NOT EMPTY(taba.field2)

* THE HAVING IS NOT OPTIMIZED, THEN IT RETURN CORRECT RESULTS
select * from tabb ;
	LEFT join taba on left(tabb.id,4)=taba.id ;
		HAVING NOT EMPTY(taba.field2)

select * from tabb ;
	LEFT join taba on .F. ;
		HAVING NOT EMPTY(taba.field2)

* the correct query
select * from tabb ;
	LEFT join taba on left(tabb.id,4)=taba.id ;
		WHERE NOT taba.field2=''