join question

classic Classic list List threaded Threaded
8 messages Options
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

join question

Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: join question

Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: join question

No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: join question

It turned out that I'd get partial results in some cases, when joining partitioned caches. But I still don't understand why I am not getting all the rows that the joined query should return.
My assumption is that if you have caches with primary keys, containing the affinity key, then the related entries  (by affinity key) in these caches should be collocated and a join among these caches based on the leading part of the primary keys (including the affinity key) which is shared across all the keys, should return all the rows which satisfy the where clause. Even if this is not the case, a distributed join should be possible and I still should get all the rows. But this is not happening either.
What could be the issue here? What am I missing here?

On Mon, May 18, 2020 at 9:30 AM narges saleh <[hidden email]> wrote:
No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: join question

It seems the issue exist only if one uses data streamer with binaryobject builder. If I use straight JDBC to insert data, the issue goes away. Any idea what one needs to do to get this working with binary objects? Everything else is the same between the two scenarios.

On Mon, May 18, 2020 at 4:39 PM narges saleh <[hidden email]> wrote:
It turned out that I'd get partial results in some cases, when joining partitioned caches. But I still don't understand why I am not getting all the rows that the joined query should return.
My assumption is that if you have caches with primary keys, containing the affinity key, then the related entries  (by affinity key) in these caches should be collocated and a join among these caches based on the leading part of the primary keys (including the affinity key) which is shared across all the keys, should return all the rows which satisfy the where clause. Even if this is not the case, a distributed join should be possible and I still should get all the rows. But this is not happening either.
What could be the issue here? What am I missing here?

On Mon, May 18, 2020 at 9:30 AM narges saleh <[hidden email]> wrote:
No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: join question

Hello!

It is possible that Data Streamer is not actually aware of affinity column of your table. Can you try marking it with @AffinityKeyMapped?

Regards,
--
Ilya Kasnacheev


вт, 19 мая 2020 г. в 01:48, narges saleh <[hidden email]>:
It seems the issue exist only if one uses data streamer with binaryobject builder. If I use straight JDBC to insert data, the issue goes away. Any idea what one needs to do to get this working with binary objects? Everything else is the same between the two scenarios.

On Mon, May 18, 2020 at 4:39 PM narges saleh <[hidden email]> wrote:
It turned out that I'd get partial results in some cases, when joining partitioned caches. But I still don't understand why I am not getting all the rows that the joined query should return.
My assumption is that if you have caches with primary keys, containing the affinity key, then the related entries  (by affinity key) in these caches should be collocated and a join among these caches based on the leading part of the primary keys (including the affinity key) which is shared across all the keys, should return all the rows which satisfy the where clause. Even if this is not the case, a distributed join should be possible and I still should get all the rows. But this is not happening either.
What could be the issue here? What am I missing here?

On Mon, May 18, 2020 at 9:30 AM narges saleh <[hidden email]> wrote:
No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: join question

Sorry for the late reply.
I have defined the affinity via cacheKeyConfiguration in ignite config file and it is working fine, if I use JDBC connection.
But it does seem the issue is with the affinity key population, not definition, in case of binary objects. I must be doing something wrong. I am trouble shooting.

thanks.

On Tue, May 19, 2020 at 3:02 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

It is possible that Data Streamer is not actually aware of affinity column of your table. Can you try marking it with @AffinityKeyMapped?

Regards,
--
Ilya Kasnacheev


вт, 19 мая 2020 г. в 01:48, narges saleh <[hidden email]>:
It seems the issue exist only if one uses data streamer with binaryobject builder. If I use straight JDBC to insert data, the issue goes away. Any idea what one needs to do to get this working with binary objects? Everything else is the same between the two scenarios.

On Mon, May 18, 2020 at 4:39 PM narges saleh <[hidden email]> wrote:
It turned out that I'd get partial results in some cases, when joining partitioned caches. But I still don't understand why I am not getting all the rows that the joined query should return.
My assumption is that if you have caches with primary keys, containing the affinity key, then the related entries  (by affinity key) in these caches should be collocated and a join among these caches based on the leading part of the primary keys (including the affinity key) which is shared across all the keys, should return all the rows which satisfy the where clause. Even if this is not the case, a distributed join should be possible and I still should get all the rows. But this is not happening either.
What could be the issue here? What am I missing here?

On Mon, May 18, 2020 at 9:30 AM narges saleh <[hidden email]> wrote:
No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks
 
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: join question

Hello!

Can you please share a runnable reproducer project exposing this issue?

Regards,
--
Ilya Kasnacheev


пт, 22 мая 2020 г. в 19:54, narges saleh <[hidden email]>:
Sorry for the late reply.
I have defined the affinity via cacheKeyConfiguration in ignite config file and it is working fine, if I use JDBC connection.
But it does seem the issue is with the affinity key population, not definition, in case of binary objects. I must be doing something wrong. I am trouble shooting.

thanks.

On Tue, May 19, 2020 at 3:02 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

It is possible that Data Streamer is not actually aware of affinity column of your table. Can you try marking it with @AffinityKeyMapped?

Regards,
--
Ilya Kasnacheev


вт, 19 мая 2020 г. в 01:48, narges saleh <[hidden email]>:
It seems the issue exist only if one uses data streamer with binaryobject builder. If I use straight JDBC to insert data, the issue goes away. Any idea what one needs to do to get this working with binary objects? Everything else is the same between the two scenarios.

On Mon, May 18, 2020 at 4:39 PM narges saleh <[hidden email]> wrote:
It turned out that I'd get partial results in some cases, when joining partitioned caches. But I still don't understand why I am not getting all the rows that the joined query should return.
My assumption is that if you have caches with primary keys, containing the affinity key, then the related entries  (by affinity key) in these caches should be collocated and a join among these caches based on the leading part of the primary keys (including the affinity key) which is shared across all the keys, should return all the rows which satisfy the where clause. Even if this is not the case, a distributed join should be possible and I still should get all the rows. But this is not happening either.
What could be the issue here? What am I missing here?

On Mon, May 18, 2020 at 9:30 AM narges saleh <[hidden email]> wrote:
No error. Just no records is returned, as opposed to the join between the replicated and partitioned cache which returns ass applicable rows. Sorry, for not being clear.

On Mon, May 18, 2020 at 9:00 AM Ilya Kasnacheev <[hidden email]> wrote:
Hello!

Fails how? Is the result set incorrect? Any specific error message? Please share details.

Regards,
--
Ilya Kasnacheev


пн, 18 мая 2020 г. в 16:49, narges saleh <[hidden email]>:
Hi All,
I have encountered a puzzling join case.
I have 3 tables on a cluster of two ignite server nodes:
table-A (id + org = primary), replicated
id
org. <-- affinity
other fields

table-B (id, org, add-id=primary key), partitioned
id
org <- affinity
addr-id
other fields

table-C (id, org, comp-id=primary key), partitioned
id
org <- affinity
comp-id
other fields

joins between table-A and table-B (on id, and org) succeeds.
joins between table-A and table-C (of id and org) succeeds.
joins between table-B and table-C (on id and org) fails.

all three joins succeed if the cluster has only one server node.
Why the join between the partitioned caches fail in a distributed mode?

I am using JDBC connection for select statements. The join fails whether dealing with thick or thin client.

thanks