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

Re: LEFT, RIGHT JOIN not working

[Surinder Mehra]

hello,
Just wondering if you got time to look into this one

On Wed, Jun 1, 2022 at 12:45 PM Surinder Mehra  wrote:

> 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

[Zhenya Stanilovsky]

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

[Surinder Mehra]

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 
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

Re: LEFT, RIGHT JOIN not working

[Surinder Mehra]

Hi,
Just wondering if you had an opportunity to look into this.

On Thu, Jun 2, 2022 at 2:52 PM Surinder Mehra  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 
> 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
>>
>>
>>
>>
>>
>>
>

Re: LEFT, RIGHT JOIN not working

[Konstantin Orlov]

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  wrote:
> 
> Hi,
> Could you please provide an update on this.
> 
> On Mon, Jun 6, 2022 at 11:48 AM Zhenya Stanilovsky  > 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  > 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  > 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[2]: LEFT, RIGHT JOIN not working

[Zhenya Stanilovsky]

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 > 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.
>>>*  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: Re[2]: LEFT, RIGHT JOIN not working

[Surinder Mehra]

Hi,
Could you please provide an update on this.

On Mon, Jun 6, 2022 at 11:48 AM Zhenya Stanilovsky 
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  > 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  > 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
>
>
>
>
>
>
>
>
>
>
>