Hi, Please find the attached java file which reproduces the issue. As you can see, the cache key is used as a join condition but LEFT join is still giving only common values.
output: [2, Keyboard, 2] Size of actual output 1 Expected size 3 is not equal to Actual size 1 On Thu, Jun 2, 2022 at 11:48 AM Zhenya Stanilovsky <arzamas...@mail.ru> wrote: > Hi, Surinder Mehra ! I check your sql and it work correct for me. > > 1. You no need to define AffinityKeyMapped for Key, check additionally > [1], you can simple modify [2] according to your case > 2. I problem still exist somehow, plz attach some code example. > > thanks ! > > [1] > https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/affinity/AffinityKeyMapped.html > [2] > https://github.com/apache/ignite/blob/master/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheJoinPartitionedAndReplicatedTest.java#L160 > > > 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 > > > > > >
JoinQueryTest.java
Description: Binary data
