Hi, Surinder! Thank you for pointing out to the problem. Seems we have a bug in subquery rewriter. I've filed a ticket for this case. [1]
As a possible workaround you could disable join rewriting by setting the system property IGNITE_ENABLE_SUBQUERY_REWRITE_OPTIMIZATION to false. [1] https://issues.apache.org/jira/browse/IGNITE-17131 -- Regards, Konstantin Orlov > On 8 Jun 2022, at 09:08, Surinder Mehra <redni...@gmail.com> wrote: > > Hi, > Could you please provide an update on this. > > On Mon, Jun 6, 2022 at 11:48 AM Zhenya Stanilovsky <arzamas...@mail.ru > <mailto:arzamas...@mail.ru>> wrote: > > Hi ! thanks for example, i hope some updates will be here in a short time. > > > > 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 > <x-msg://e.mail.ru/compose/?mailto=mailto%3aredni...@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 > <x-msg://e.mail.ru/compose/?mailto=mailto%3aarzamas...@mail.ru>> wrote: > Hi, Surinder Mehra ! I check your sql and it work correct for me. > You no need to define AffinityKeyMapped for Key, check additionally [1], you > can simple modify [2] according to your case > 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 > > <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 > > <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 > > > > > > > >
