ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 胡永亮/Bob <hu...@neusoft.com>
Subject Re: Re: SQL join query return different result under the same data when having different ignite instance.
Date Fri, 22 Jul 2016 02:00:09 GMT
Hi, all

    In Ignite1.6 java doc, I found some description about @AffinityKeyMapped:

    "Optional annotation to specify custom key-to-node affinity. Affinity key is a key which
will be used to determine a node on which given cache key will be stored. This annotation
allows to mark a field or a method in the cache key object that will be used as an affinity
key (instead of the entire cache key object that is used for affinity by default). Note that
a class can have only one field or method annotated with @AffinityKeyMapped annotation.  "

    See the bold text,  so, when one table will join another table with two columns, how to
make affinity?

    The actual scenario like this:
     "select * FROm Kc21, \"Kc24Cache\".Kc24"
+ ", \"Ka06Cache\".Ka06, \"Kc60Cache\".Kc60 "
+ " WHERE Kc21.akb020 = Kc24.akb020"
+ " AND Kc21.akc190 = Kc24.akc190"
+ " AND Kc24.akb020 = Kc60.akb020"
+ " AND Kc24.akc190 = Kc60.akc190"
+ " AND Kc24.aae072 = Kc60.aae072"
+ " AND Kc24.bka135 = Kc60.bka135"
+ " AND COALESCE (Kc24.bkc380, '0') = '0'"
+ " AND Kc24.ake010 BETWEEN '2014-1-1' AND '2015-1-1'"
+ " AND Kc21.akc193 = Ka06.akc193";

Thanks everyone.



Bob
 
From: 胡永亮/Bob
Date: 2016-07-21 17:07
To: user@ignite.apache.org
Subject: Re: Re: SQL join query return different result under the same data when having different
ignite instance.
Hi, Alexey:

    First, thank you.

    But, I don't know how to config the affinity for my sql, because it is too complex.

    My sql is:
    "select * FROm Kc21, \"Kc24Cache\".Kc24"
+ ", \"Ka06Cache\".Ka06, \"Kc60Cache\".Kc60 "
+ " WHERE Kc21.akb020 = Kc24.akb020"
+ " AND Kc21.akc190 = Kc24.akc190"
+ " AND Kc24.akb020 = Kc60.akb020"
+ " AND Kc24.akc190 = Kc60.akc190"
+ " AND Kc24.aae072 = Kc60.aae072"
+ " AND Kc24.bka135 = Kc60.bka135"
+ " AND COALESCE (Kc24.bkc380, '0') = '0'"
+ " AND Kc24.ake010 BETWEEN '2014-1-1' AND '2015-1-1'"
+ " AND Kc21.akc193 = Ka06.akc193";

    I have one question:
    For object Kc21, its columns  akb020 and akc190 will join with columns akb020 and akc190
of object Kc24. 
        And besides, the column akc193 of object Kc21 also will join with the column akc193
of object Ka06. 
        According to doc, I don't know how to config two affinitys. 

    Thanks.



Bob
 
From: Alexey Goncharuk
Date: 2016-07-21 16:00
To: user@ignite.apache.org
Subject: Re: SQL join query return different result under the same data when having different
ignite instance.
Hi,

Ignite 1.6 requires data to be properly collocated in order for joins to work correctly. Namely,
data being joined from tables Kc21 and Kc24 must be collocated. See [1] for more details on
affinity collocation and [2] for more details on how SQL queries work. Also, take a look at
org.apache.ignite.examples.datagrid.CacheQueryExample for correct collocation example.

There is a ticket [3] which will remove this restriction, and hopefully, in will get to Ignite
1.7. You can watch this ticket for progress.

Hope this helps,
AG

---
[1] https://apacheignite.readme.io/docs/affinity-collocation
[2] https://apacheignite.readme.io/docs/sql-queries
[3] https://issues.apache.org/jira/browse/IGNITE-1232​

---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)

is intended only for the use of the intended recipient and may be confidential and/or privileged
of 
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication
is 
not the intended recipient, unauthorized use, forwarding, printing,  storing, disclosure or
copying 
is strictly prohibited, and may be unlawful.If you have received this communication in error,please

immediately notify the sender by return e-mail, and delete the original message and all copies
from 
your system. Thank you. 
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged
of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication
is
not the intended recipient, unauthorized use, forwarding, printing,  storing, disclosure or
copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies
from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Mime
View raw message