LEFT, RIGHT JOIN not working

[Surinder Mehra]

Hi,
I have the following sample code to demo issue in SQL joins. I have created
an affinity key and value as shown below and added some sample data to it.
When I try LEFT self join on this table it always gives me common rows
 irrespective of LEFT or RIGHT JOIN
Could you please help me find what am I doing wrong here.

cache Key :

public class OrderAffinityKey {
    Integer id;
    @AffinityKeyMapped
    Integer customerId;
}


cache value:

public class Order implements Serializable {
    @QuerySqlField
    Integer id;

    @AffinityKeyMapped
    @QuerySqlField Integer customerId;
    @QuerySqlField String product;
}


Table C: (select customerID, product FROM "orderCache"."ORDER" WHERE
CUSTOMERID IN ( 1, 2))

1 keyboard
2 Laptop


Table O: (select customerID, product FROM "orderCache"."ORDER" WHERE
CUSTOMERID IN ( 3, 2))

2 laptop
3 mouse



JOIN:

Query :
select DISTINCT C.customerID, C.product, O.customerID
FROM
 (select customerID, product FROM "orderCache"."ORDER" WHERE CUSTOMERID IN
( 1, 2)) C
 LEFT JOIN
(select customerID, product FROM "orderCache"."ORDER" WHERE CUSTOMERID IN (
3, 2)) O
ON
C.customerId = O.customerId


Output:

2 laptop   2
3 mouse   3

Expected output:

1 keyboard   null
2 laptop   2
3 mouse   3