Hi, Just wondering if you had an opportunity to look into this. On Thu, Jun 2, 2022 at 2:52 PM Surinder Mehra <redni...@gmail.com> wrote:
> 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 >> >> >> >> >> >> >
