There appears to have been a change in the behaviour of SQL - SELECT when using a WHERE clause in a Left/Right outer join. An example is shown below. Surpringly, this behaviour change is not noted in the "What's new in Visual FoxPro" documentation released with VFP7. Does anyone know if this was an intentional but undocumented change or not? The new behaviour seems to make sense but I imagine it would break some existing code as we have unfortunately discovered.

Many thanks
Paul Dorrington
Melbourne, Australia

Table_1
tbl1key label
A00001 Test1
A00002 Test2
A00003 Test3

Table_2
tbl2key data
A00001
A00003

SELECT tbl1.*, tbl2.data, tbl2.size ;
FROM tbl1 LEFT JOIN tbl2 ;
ON tbl1.tbl1key = tbl2.tbl2key ;
WHERE EMPTY(tbl2.data) ;
INTO CURSOR r_result

In VFP6 resuilt is
tbl1key label data
A00001 Test1
A00002 Test2 .NULL.
A00003 Test3

In VFP7 result is
tbl1key label data
A00001 Test1
A00003 Test3

In VFP7 you can achieve the VFP6 result with the following amended SELECTs
SELECT tbl1.*, tbl2.data, tbl2.size ;
FROM tbl1 LEFT JOIN tbl2 ;
ON tbl1.tbl1key = tbl2.tbl2key ;
WHERE (EMPTY(tbl2.data) ;
OR ISNULL(tbl2.data)) ;
INTO CURSOR r_result

OR

SELECT tbl1.*, tbl2.data, tbl2.size ;
FROM tbl1 LEFT JOIN tbl2 ;
ON tbl1.tbl1key = tbl2.tbl2key ;
AND EMPTY(tbl2.data) ;
INTO CURSOR r_result