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

I intentintionally removed my name from CC list so people don't cc me needlessly. I read most messages in VFP and SQl forums anyway.

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

I don't see why EMPTY() function should be a risk unless there's a bug in SQL Engine.

>

>* 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=''
>