You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Zhenya Stanilovsky <ar...@mail.ru> on 2022/06/06 06:18:34 UTC
Re[2]: LEFT, RIGHT JOIN not working
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 < rednirus@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 < arzamas123@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
>>>[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
>>>
>>>
>>>
>>>
Re: LEFT, RIGHT JOIN not working
Posted by Konstantin Orlov <ko...@gridgain.com>.
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 <re...@gmail.com> wrote:
>
> Hi,
> Could you please provide an update on this.
>
> On Mon, Jun 6, 2022 at 11:48 AM Zhenya Stanilovsky <arzamas123@mail.ru <ma...@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 <rednirus@gmail.com <x-...@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 <arzamas123@mail.ru <x-...@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
>
>
>
>
>
>
>
>
Re: Re[2]: LEFT, RIGHT JOIN not working
Posted by Surinder Mehra <re...@gmail.com>.
Hi,
Could you please provide an update on this.
On Mon, Jun 6, 2022 at 11:48 AM Zhenya Stanilovsky <ar...@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 <rednirus@gmail.com
> <//...@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 <arzamas123@mail.ru
> <//...@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
>
>
>
>
>
>
>
>
>
>
>