You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by 胡永亮/Bob <hu...@neusoft.com> on 2016/07/21 00:41:11 UTC

SQL join query return different result under the same data when having different ignite instance.

hi, everyone:

    I am using the ignite 1.6.

    I have the sql query: 
    String querySql = "select count(*) 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";

logger.info(">>>>>>>>>>>>>>>>>>>>>> start to sql query."); 

SqlFieldsQuery qry = new SqlFieldsQuery(querySql);
try(QueryCursor<List<?>> cursor = cache.query(qry)){

for (List<?> row : cursor){
System.out.println(row.get(0));
} 
}

    When I make the cluster with only one instance, the result of the query is 2, this is right.
    When cluster with two instances, the result is 1, this is wrong.
    When cluster with four instances, the result is 0, this is wrong.

    Where is the point for this problem? I don't know what happened. 

    Hope to get help, thanks very much.



Bob


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

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by 胡永亮/Bob <hu...@neusoft.com>.
Val, 
    
    I am very happy to unstand this.  



Bob
 
From: vkulichenko
Date: 2016-07-28 12:42
To: user@ignite.apache.org
Subject: Re: Re: SQL join query return different result under the same data when having different ignite instance.
Bob,
 
With the new schema a class can belong only to one school, so there is no
much sense to join student with class. Just join both Class and Student with
School by schoolID and filter by any criteria you want.
 
-Val
 
 
 
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6580.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.


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

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by vkulichenko <va...@gmail.com>.
Bob,

With the new schema a class can belong only to one school, so there is no
much sense to join student with class. Just join both Class and Student with
School by schoolID and filter by any criteria you want.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6580.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by 胡永亮/Bob <hu...@neusoft.com>.
Hi  Val,

        Yes, I do the change as you suggest.
        Now, the tables is the following:
        
School:
    
id 
 name
 address
 1
 xxx
 aaa

  Class:
 id
 name
 number
 schoolID
 1
 yyy
 bbb
 1

   Student:
id 
 name
 sex
 schoolID
 classID
 1
 zzz
 male
 1
 1


    And the cache keys are the following:
    SchoolKey
    {
        private int id;
    }

    ClassKey
    {
        private int id;
        @AffinityKeyMapped
        private int schoolID;
    }

    StudentKey
    {
        private int id;
        private int classID;
        @AffinityKeyMapped
        private int schoolID;
    }

    Now, This guarantee
    that class data and student data linked to a particular school resides on the same node.

    But, I want to query like this:
    select * from school, class, student where student.id = school.id and student.id = class.id and school.id = 1 and class.id = 1;

    This query want to find all the students in school 1 and class 1.
    In Ignite, the data linked to school 1 resides on the same node, but the students in class 1 may not be in the same node,
    because the classID is not affinity key.

    So, can the query work right?

    Thanks for reply again.






Bob
 
From: vkulichenko
Date: 2016-07-28 08:01
To: user@ignite.apache.org
Subject: Re: Re: SQL join query return different result under the same data when having different ignite instance.
Hi Bob,
 
In this example Class table should also have schoolId field and schoolId
should be used as the affinity key for all three types. This will guarantee
that all data linked to a particular school resides on the same node.
 
-Val
 
 
 
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6575.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.


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

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by vkulichenko <va...@gmail.com>.
Hi Bob,

In this example Class table should also have schoolId field and schoolId
should be used as the affinity key for all three types. This will guarantee
that all data linked to a particular school resides on the same node.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6575.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by 胡永亮/Bob <hu...@neusoft.com>.
Hi  Val,

    My query is complex, I use another example to descripte my scenario.

    There are three objects: School, Class, Student.

    School:
    
id 
 name
 address
 1
 xxx
 aaa

  Class:
id 
 name
 number
 1
 yyy
 bbb

   Student:
id 
 name
 sex
 schoolID
 classID
 1
 zzz
 male
 1
 1

Now, the question is coming:
I want to query as the following sql: 
select * from school, class, student where student.id = school.id and student.id = class.id;

It means, the object student produced two affinity column: schoolID and classID, but as you said:
"Generally that's right, you can collocate by a single field only, otherwise
the data will end up on different nodes"

How to do for this scenario? 



Bob
 
From: vkulichenko
Date: 2016-07-23 03:08
To: user@ignite.apache.org
Subject: Re: Re: SQL join query return different result under the same data when having different ignite instance.
Hi Bob,
 
It's hard to tell how to collocate your data because it's not clear what
this query is doing. Can you describe?
 
Generally that's right, you can collocate by a single field only, otherwise
the data will end up on different nodes. If the data in two caches can't be
collocated, you can also switch one of the caches to REPLICATED mode.
Replicated caches don't need collocation, but they replicate all the data to
all nodes.
 
-Val
 
 
 
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6478.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.


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

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by vkulichenko <va...@gmail.com>.
Hi Bob,

It's hard to tell how to collocate your data because it's not clear what
this query is doing. Can you describe?

Generally that's right, you can collocate by a single field only, otherwise
the data will end up on different nodes. If the data in two caches can't be
collocated, you can also switch one of the caches to REPLICATED mode.
Replicated caches don't need collocation, but they replicate all the data to
all nodes.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-join-query-return-different-result-under-the-same-data-when-having-different-ignite-instance-tp6433p6478.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by 胡永亮/Bob <hu...@neusoft.com>.
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.
---------------------------------------------------------------------------------------------------

Re: Re: SQL join query return different result under the same data when having different ignite instance.

Posted by 胡永亮/Bob <hu...@neusoft.com>.
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.
---------------------------------------------------------------------------------------------------

Re: SQL join query return different result under the same data when having different ignite instance.

Posted by Alexey Goncharuk <al...@gmail.com>.
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​