Hi Fabio,

This is not by design and it is a known issue. An incorrect result is reported when engine decides to not use existing or temporary index to optimize FULL JOIN operation.

Thanks,
Aleksey.




>Hi,
>
>FULL JOIN is a symmetric join,
>then
>

> ... T1 FULL JOIN T2 ....
>* is equal to
> ... T2 FULL JOIN T1 ....
>

>
>On VFP this is not true:
>Example 1 ( test symmetric )
>

>CREATE CURSOR T1 (F1 I )
>CREATE CURSOR T2 (F2 I )
>INSERT INTO T1 VALUES (1)
>INSERT INTO T2 VALUES (2)
>SELECT * FROM T1 FULL JOIN T2 ON .T.
>* ---------
>*   1     2
>* NULL    2  this is bad records
>SELECT * FROM T2 FULL JOIN T1 ON .T.
>* ---------
>*   2     1
>* NULL    1 this is bad records
>

>
>Example 2
>( asymmetry with cardinal loop optimization -> result change )
>

>CREATE CURSOR T1 (F1 I )
>CREATE CURSOR T2 (F2 I )
>INSERT INTO T1 VALUES (1)
>INSERT INTO T2 VALUES (2)
>SELECT F1,F2 FROM T1 FULL JOIN T2 ON .T.
>* -F1---F2--
>*   1     2
>* NULL    2  nulls are on F1 fields
>
>&& next force VFP to change loop join pivot to T2
>INSERT INTO T1 VALUES (3) ble
>
>SELECT F1,F2 FROM T1 FULL JOIN T2 ON .T.
>* -F1---F2--
>*   1     2
>*   3     2
>*   1    NULL null are on F2 fields
>*   3    NULL null are on F2 fields
>
>* if i force pivot looping to T1, result is obvious now
>SELECT F1,F2 FROM FORCE T1 FULL JOIN T2 ON .T.
>* -F1---F2--
>*   1     2
>*   3     2
>* NULL    2  nulls are on F1 fields
>

>
>With 3 tables results is more clear:
>

>
>CREATE CURSOR T1 (F1 I )
>CREATE CURSOR T2 (F2 I )
>CREATE CURSOR T3 (F3 I )
>INSERT INTO T1 VALUES (1)
>INSERT INTO T2 VALUES (2)
>INSERT INTO T3 VALUES (3)
>SELECT F1,F2,F3 FROM T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T.
>* 4 Records
>
>INSERT INTO T1 VALUES (3) && this force VFP to change loop join pivot table
>
>SELECT F1,F2,F3 FROM T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T.
>*  8 records
>
>SELECT F1,F2,F3 FROM FORCE T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T.
>*  4 records
>

>
>If this is by design, then it is very different of SQL Server FULL JOIN.
>
>Fabio