You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Anil <an...@gmail.com> on 2017/01/04 11:06:08 UTC
NOT IN in ignite
HI,
As per ignite documentation, IN operator does not use index and has to be
use as *join table.*
Hoping this holds good for* NOT IN *operator as well.
String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
?) n on n.joinId <> p.name";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setFetchSize(100);
statement.setObject(1, new String[]{"100", "200", "300"});
statement.setObject(2, new String[]{"Name100", "Name200"});
Expected results are Name300
Actual output :
Name100
Name200
Name300
Name300
Did you see any issue with the query ? please help.
thanks.
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
I meant that you CAN set this flag per query when using IgniteCache API, but
not JDBC. And I don't know any way to do this in JDBC, API just doesn't
provide it. So the limitation you mention before makes sense, but it more
cause by JDBC rather than Ignite. If you have ideas on how to overcome this,
please share with us.
It would be great if query executor could detect correct value for
collocated flag automatically, but I'm afraid there is no technical way to
do this. As I mentioned before, this is a performance optimization which
basically removes part of execution flow. In some cases this causes
incorrect result, so you have to be sure that you're using it correctly.
With collocated=false you will ALWAYS get correct results.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10814.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
I agree with you.
Controlling query execution plan as per query is useful in this case.
collocation = true does not make sense for queries without join though
caches are collocated. what do you say ?
i feel query executor must be intelligent enough to use collection as per
query.
Thanks.
On 22 February 2017 at 06:09, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> OK, so you're talking about setting collocated flag on per query level in
> JDBC driver, right? This makes sense, but it seems to be a limitation of
> JDBC API rather than Ignite implementation. How would you provide a
> parameter when creating a statement and/or executing a query? Do you have
> any ideas how to do this?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10777.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
OK, so you're talking about setting collocated flag on per query level in
JDBC driver, right? This makes sense, but it seems to be a limitation of
JDBC API rather than Ignite implementation. How would you provide a
parameter when creating a statement and/or executing a query? Do you have
any ideas how to do this?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10777.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Team,
I just would like to check if there is a plan to relook into these behavior
?
Thanks.
On 13 February 2017 at 09:29, Anil <an...@gmail.com> wrote:
> Hi Val,
>
> When two cache's require a join, collocation must be true in jdbc
> connection and then group by queries on individual caches will not return
> aggregated results. you mean this is not limitation ? if Yes, i am sorry,
> i may not agree on this :(
>
> in this case, to make sql queries work, two jdbc client must be created..
> one for queries of individual cache and other for join queries.
>
> Thanks
>
>
>
> On 13 February 2017 at 07:56, vkulichenko <va...@gmail.com>
> wrote:
>
>> Anil,
>>
>> I don't see any limitations (except IGNITE-3860). Aggregation without
>> collocation works properly and return correct result unless collected flag
>> is set to true (doing so in this scenario is a misuse). As for
>> performance,
>> collocated execution will always be faster than non-collocated. That's
>> true
>> for any distributed system and there is no magic, you know :)
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10582.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
When two cache's require a join, collocation must be true in jdbc
connection and then group by queries on individual caches will not return
aggregated results. you mean this is not limitation ? if Yes, i am sorry,
i may not agree on this :(
in this case, to make sql queries work, two jdbc client must be created..
one for queries of individual cache and other for join queries.
Thanks
On 13 February 2017 at 07:56, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> I don't see any limitations (except IGNITE-3860). Aggregation without
> collocation works properly and return correct result unless collected flag
> is set to true (doing so in this scenario is a misuse). As for performance,
> collocated execution will always be faster than non-collocated. That's true
> for any distributed system and there is no magic, you know :)
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10582.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
I don't see any limitations (except IGNITE-3860). Aggregation without
collocation works properly and return correct result unless collected flag
is set to true (doing so in this scenario is a misuse). As for performance,
collocated execution will always be faster than non-collocated. That's true
for any distributed system and there is no magic, you know :)
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10582.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Thanks Andrey.
Ideally Affinity must be used to find the node of an entry and for joins to
reduce the data transfer between nodes. With the current implementation,
group by queries of a cache (with no joins) on non affinity key will not
work :( . Cout queries are also behaving in the same way. This is limiting
usage of data grid feature. (this is just my view and may be wrong )
if i remember it correctly, Sergi said that Query 2 should work for one of
previous questions.
Thanks,
Anil
On 11 February 2017 at 22:39, Andrey Mashenkov <an...@gmail.com>
wrote:
> Hi Anil,
>
> Query 1 results looks ok. You got different results as affinity key is
> equipmentId, but not serialnumber.
> Query 2 has aggregates in subquery that is not supported yet [1].
>
> [1] https://issues.apache.org/jira/browse/IGNITE-3860.
>
>
> On Sat, Feb 11, 2017 at 7:18 PM, Anil <an...@gmail.com> wrote:
>
>>
>> Hi team,
>>
>> i fee this is a bug. i have loaded cache with affinity key and group by
>> queries on non affinity key returning results per node with both collocate
>> = true/false.
>>
>> i have any created INSTALL_BASE cache with key as AffinityKey<String> and
>> value is InstallBase pojo. and affinity key is equipmentId
>>
>>
>>
>> *Query 1 -*
>>
>> SELECT count (*) as count, serialnumber FROM InstallBase where
>> serialnumber= '0000031438' group by serialnumber= '0000031438'
>>
>> Results - on 4 node cluster
>> *With collocated = true* :
>>
>> 1 - 0000031438
>> 3 - 0000031438
>>
>> *With collocated = false* :
>>
>> 4 - 0000031438
>>
>> *Query 2 -*
>>
>> Select ib.*, p.count from installbase ib join (SELECT serialnumber ,
>> count (*) as count FROM InstallBase group by serialnumber) p on
>> ib.serialnumber = p.serialnumber and ib.serialnumber = '0000031438'
>>
>> *With collocated = true* :
>>
>> 1 - 0000031438
>> 3 - 0000031438
>> 3 - 0000031438
>> 3 - 0000031438
>>
>> *With collocated = false* :
>>
>> 1 - 0000031438
>> 3 - 0000031438
>> 3 - 0000031438
>> 3 - 0000031438
>>
>>
>> I see similar behavior with count queries as well.
>>
>> i strongly feel this is not correct behavior. Group by query on non
>> affinity field is very common usecase. please share your view on this.
>>
>> Thanks
>>
>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
Re: NOT IN in ignite
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Anil,
Query 1 results looks ok. You got different results as affinity key is
equipmentId, but not serialnumber.
Query 2 has aggregates in subquery that is not supported yet [1].
[1] https://issues.apache.org/jira/browse/IGNITE-3860.
On Sat, Feb 11, 2017 at 7:18 PM, Anil <an...@gmail.com> wrote:
>
> Hi team,
>
> i fee this is a bug. i have loaded cache with affinity key and group by
> queries on non affinity key returning results per node with both collocate
> = true/false.
>
> i have any created INSTALL_BASE cache with key as AffinityKey<String> and
> value is InstallBase pojo. and affinity key is equipmentId
>
>
>
> *Query 1 -*
>
> SELECT count (*) as count, serialnumber FROM InstallBase where
> serialnumber= '0000031438' group by serialnumber= '0000031438'
>
> Results - on 4 node cluster
> *With collocated = true* :
>
> 1 - 0000031438
> 3 - 0000031438
>
> *With collocated = false* :
>
> 4 - 0000031438
>
> *Query 2 -*
>
> Select ib.*, p.count from installbase ib join (SELECT serialnumber , count
> (*) as count FROM InstallBase group by serialnumber) p on ib.serialnumber =
> p.serialnumber and ib.serialnumber = '0000031438'
>
> *With collocated = true* :
>
> 1 - 0000031438
> 3 - 0000031438
> 3 - 0000031438
> 3 - 0000031438
>
> *With collocated = false* :
>
> 1 - 0000031438
> 3 - 0000031438
> 3 - 0000031438
> 3 - 0000031438
>
>
> I see similar behavior with count queries as well.
>
> i strongly feel this is not correct behavior. Group by query on non
> affinity field is very common usecase. please share your view on this.
>
> Thanks
>
--
Best regards,
Andrey V. Mashenkov
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi team,
i fee this is a bug. i have loaded cache with affinity key and group by
queries on non affinity key returning results per node with both collocate
= true/false.
i have any created INSTALL_BASE cache with key as AffinityKey<String> and
value is InstallBase pojo. and affinity key is equipmentId
*Query 1 -*
SELECT count (*) as count, serialnumber FROM InstallBase where
serialnumber= '0000031438' group by serialnumber= '0000031438'
Results - on 4 node cluster
*With collocated = true* :
1 - 0000031438
3 - 0000031438
*With collocated = false* :
4 - 0000031438
*Query 2 -*
Select ib.*, p.count from installbase ib join (SELECT serialnumber , count
(*) as count FROM InstallBase group by serialnumber) p on ib.serialnumber =
p.serialnumber and ib.serialnumber = '0000031438'
*With collocated = true* :
1 - 0000031438
3 - 0000031438
3 - 0000031438
3 - 0000031438
*With collocated = false* :
1 - 0000031438
3 - 0000031438
3 - 0000031438
3 - 0000031438
I see similar behavior with count queries as well.
i strongly feel this is not correct behavior. Group by query on non
affinity field is very common usecase. please share your view on this.
Thanks
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
Do you guys accept this behavior as bug ? correct it so that group by with
non affinity key returns same results with/without collocated true ?
Thanks.
On 9 February 2017 at 09:40, Anil <an...@gmail.com> wrote:
> Hi Val,
>
> You are right. i collocated the data and set the collocated = true. and it
> impacted my group by queries.
>
> I was mentioning only group by queries on non affinity key field as an
> issue.
>
> Thanks.
>
> On 9 February 2017 at 03:18, vkulichenko <va...@gmail.com>
> wrote:
>
>> Anil,
>>
>> You should always try to colocate as much as possible when working with a
>> distributed system. If you colocate properly and set collocated=true, you
>> will get correct result with the best possible performance. If you can't
>> colocate, you have to set the flag to false. Result will still correct,
>> but
>> it will work slower. In other words, this is just an optional performance
>> optimization.
>>
>> Not colocating and setting flag to true is a misuse as this combination
>> leads to incorrect result.
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10506.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
You are right. i collocated the data and set the collocated = true. and it
impacted my group by queries.
I was mentioning only group by queries on non affinity key field as an
issue.
Thanks.
On 9 February 2017 at 03:18, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> You should always try to colocate as much as possible when working with a
> distributed system. If you colocate properly and set collocated=true, you
> will get correct result with the best possible performance. If you can't
> colocate, you have to set the flag to false. Result will still correct, but
> it will work slower. In other words, this is just an optional performance
> optimization.
>
> Not colocating and setting flag to true is a misuse as this combination
> leads to incorrect result.
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10506.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
You should always try to colocate as much as possible when working with a
distributed system. If you colocate properly and set collocated=true, you
will get correct result with the best possible performance. If you can't
colocate, you have to set the flag to false. Result will still correct, but
it will work slower. In other words, this is just an optional performance
optimization.
Not colocating and setting flag to true is a misuse as this combination
leads to incorrect result.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10506.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
the context is "group by with non affinity field with collate = true".
Thanks.
On 8 February 2017 at 03:16, vkulichenko <va...@gmail.com>
wrote:
> Not correct :) You can join data from two caches with one connection. Cache
> you provide in the URL will be treated as a default schema. Other tables
> can
> be accessed by providing schema name in from of the table name
> ("my-cache".MyType).
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10492.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Not correct :) You can join data from two caches with one connection. Cache
you provide in the URL will be treated as a default schema. Other tables can
be accessed by providing schema name in from of the table name
("my-cache".MyType).
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10492.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
it means, ignite is not useful for such usecases ? correct ?
On 7 February 2017 at 19:27, Andrey Mashenkov <an...@gmail.com>
wrote:
> Hi Anil,
>
> It looks like "collocate=true" can be set on per JdbcConnection basis
> only, via "ignite.jdbc.collocation" property.
>
>
> On Tue, Feb 7, 2017 at 3:24 PM, Anil <an...@gmail.com> wrote:
>
>> Hi Andrey,
>>
>> its clear now. is there any way to achieve group by results of collocate
>> = false with jdbc client of collocate = true. i mean to change collocate
>> for each query ?
>>
>> i have to use collocate = true for join of two caches and have only one
>> jdbc client node.
>>
>> Thanks.
>>
>> On 7 February 2017 at 17:47, Andrey Mashenkov <andrey.mashenkov@gmail.com
>> > wrote:
>>
>>> Hi Anil,
>>>
>>> If you have GroupBy condition in query and all "rows" that belong to one
>>> group are collocated with affinity key, then group is collocated and you
>>> can use "collocate = true" option to allow Ignite apply optimization.
>>> Otherwise, it is not a collocated grouping and GroupBy should be apply
>>> on reduce stage; You will get wrong result with "collocate = true" option.
>>>
>>> Map response is always returned to node that initiate query (reduce
>>> node), reduce stage will apply anyway.
>>>
>>>
>>> On Tue, Feb 7, 2017 at 1:37 PM, Anil <an...@gmail.com> wrote:
>>>
>>>> Hi Andrey,
>>>>
>>>> thanks for response and little confused.
>>>>
>>>> I hope "group" is the entries of two (or more) caches which are
>>>> collocated with affinity key. please correct if i am wrong.
>>>>
>>>> "For example: In this case, we can make grouping and apply Having
>>>> condition on map stage, that
>>>> results in reducing network traffic." - *Agree. no aggregation in
>>>> reduce and apply pagination and return to client ? or just return map
>>>> response to client ?*
>>>>
>>>> Thanks.
>>>>
>>>>
>>>> On 7 February 2017 at 14:55, Andrey Mashenkov <
>>>> andrey.mashenkov@gmail.com> wrote:
>>>>
>>>>> Hi Anil,
>>>>>
>>>>> Group by queries with collocate = true will be correct if:
>>>>> for every group there is only partition that contains all data of this
>>>>> group.
>>>>> Ignite does not know if group data is collocated for a query, so with
>>>>> this option we can give a hint to Ignite.
>>>>>
>>>>> For example: In this case, we can make grouping and apply Having
>>>>> condition on map stage, that
>>>>> results in reducing network traffic.
>>>>>
>>>>> For non-collocated groups we can't apply Having on map stage as we
>>>>> have only part of data at this time.
>>>>> So, in this case with collocate = true we can get wrong results.
>>>>>
>>>>>
>>>>>
>>>>> On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> May I know what do you mean by "When used incorrectly" ?
>>>>>>
>>>>>> group by queries with collocate = true is not incorrect usage or i
>>>>>> misunderstood this ?
>>>>>>
>>>>>> Thanks.
>>>>>>
>>>>>> On 7 February 2017 at 01:26, vkulichenko <
>>>>>> valentin.kulichenko@gmail.com> wrote:
>>>>>>
>>>>>>> Well, I would agree that the flag is confusing, but your
>>>>>>> understanding is not
>>>>>>> correct. When used incorrectly, this flag does breaks query result
>>>>>>> because
>>>>>>> it forces Ignite to change execution plan to more optimal. This
>>>>>>> optimization
>>>>>>> doesn't always work though (that's actually the reason why it's
>>>>>>> configurable).
>>>>>>>
>>>>>>> -Val
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> View this message in context: http://apache-ignite-users.705
>>>>>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>>>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best regards,
>>>>> Andrey V. Mashenkov
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Best regards,
>>> Andrey V. Mashenkov
>>>
>>
>>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
Re: NOT IN in ignite
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Anil,
It looks like "collocate=true" can be set on per JdbcConnection basis only,
via "ignite.jdbc.collocation" property.
On Tue, Feb 7, 2017 at 3:24 PM, Anil <an...@gmail.com> wrote:
> Hi Andrey,
>
> its clear now. is there any way to achieve group by results of collocate =
> false with jdbc client of collocate = true. i mean to change collocate for
> each query ?
>
> i have to use collocate = true for join of two caches and have only one
> jdbc client node.
>
> Thanks.
>
> On 7 February 2017 at 17:47, Andrey Mashenkov <an...@gmail.com>
> wrote:
>
>> Hi Anil,
>>
>> If you have GroupBy condition in query and all "rows" that belong to one
>> group are collocated with affinity key, then group is collocated and you
>> can use "collocate = true" option to allow Ignite apply optimization.
>> Otherwise, it is not a collocated grouping and GroupBy should be apply on
>> reduce stage; You will get wrong result with "collocate = true" option.
>>
>> Map response is always returned to node that initiate query (reduce
>> node), reduce stage will apply anyway.
>>
>>
>> On Tue, Feb 7, 2017 at 1:37 PM, Anil <an...@gmail.com> wrote:
>>
>>> Hi Andrey,
>>>
>>> thanks for response and little confused.
>>>
>>> I hope "group" is the entries of two (or more) caches which are
>>> collocated with affinity key. please correct if i am wrong.
>>>
>>> "For example: In this case, we can make grouping and apply Having
>>> condition on map stage, that
>>> results in reducing network traffic." - *Agree. no aggregation in
>>> reduce and apply pagination and return to client ? or just return map
>>> response to client ?*
>>>
>>> Thanks.
>>>
>>>
>>> On 7 February 2017 at 14:55, Andrey Mashenkov <
>>> andrey.mashenkov@gmail.com> wrote:
>>>
>>>> Hi Anil,
>>>>
>>>> Group by queries with collocate = true will be correct if:
>>>> for every group there is only partition that contains all data of this
>>>> group.
>>>> Ignite does not know if group data is collocated for a query, so with
>>>> this option we can give a hint to Ignite.
>>>>
>>>> For example: In this case, we can make grouping and apply Having
>>>> condition on map stage, that
>>>> results in reducing network traffic.
>>>>
>>>> For non-collocated groups we can't apply Having on map stage as we have
>>>> only part of data at this time.
>>>> So, in this case with collocate = true we can get wrong results.
>>>>
>>>>
>>>>
>>>> On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> May I know what do you mean by "When used incorrectly" ?
>>>>>
>>>>> group by queries with collocate = true is not incorrect usage or i
>>>>> misunderstood this ?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> On 7 February 2017 at 01:26, vkulichenko <
>>>>> valentin.kulichenko@gmail.com> wrote:
>>>>>
>>>>>> Well, I would agree that the flag is confusing, but your
>>>>>> understanding is not
>>>>>> correct. When used incorrectly, this flag does breaks query result
>>>>>> because
>>>>>> it forces Ignite to change execution plan to more optimal. This
>>>>>> optimization
>>>>>> doesn't always work though (that's actually the reason why it's
>>>>>> configurable).
>>>>>>
>>>>>> -Val
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> View this message in context: http://apache-ignite-users.705
>>>>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>> Andrey V. Mashenkov
>>>>
>>>
>>>
>>
>>
>> --
>> Best regards,
>> Andrey V. Mashenkov
>>
>
>
--
Best regards,
Andrey V. Mashenkov
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Andrey,
its clear now. is there any way to achieve group by results of collocate =
false with jdbc client of collocate = true. i mean to change collocate for
each query ?
i have to use collocate = true for join of two caches and have only one
jdbc client node.
Thanks.
On 7 February 2017 at 17:47, Andrey Mashenkov <an...@gmail.com>
wrote:
> Hi Anil,
>
> If you have GroupBy condition in query and all "rows" that belong to one
> group are collocated with affinity key, then group is collocated and you
> can use "collocate = true" option to allow Ignite apply optimization.
> Otherwise, it is not a collocated grouping and GroupBy should be apply on
> reduce stage; You will get wrong result with "collocate = true" option.
>
> Map response is always returned to node that initiate query (reduce node),
> reduce stage will apply anyway.
>
>
> On Tue, Feb 7, 2017 at 1:37 PM, Anil <an...@gmail.com> wrote:
>
>> Hi Andrey,
>>
>> thanks for response and little confused.
>>
>> I hope "group" is the entries of two (or more) caches which are
>> collocated with affinity key. please correct if i am wrong.
>>
>> "For example: In this case, we can make grouping and apply Having
>> condition on map stage, that
>> results in reducing network traffic." - *Agree. no aggregation in reduce
>> and apply pagination and return to client ? or just return map response to
>> client ?*
>>
>> Thanks.
>>
>>
>> On 7 February 2017 at 14:55, Andrey Mashenkov <andrey.mashenkov@gmail.com
>> > wrote:
>>
>>> Hi Anil,
>>>
>>> Group by queries with collocate = true will be correct if:
>>> for every group there is only partition that contains all data of this
>>> group.
>>> Ignite does not know if group data is collocated for a query, so with
>>> this option we can give a hint to Ignite.
>>>
>>> For example: In this case, we can make grouping and apply Having
>>> condition on map stage, that
>>> results in reducing network traffic.
>>>
>>> For non-collocated groups we can't apply Having on map stage as we have
>>> only part of data at this time.
>>> So, in this case with collocate = true we can get wrong results.
>>>
>>>
>>>
>>> On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> May I know what do you mean by "When used incorrectly" ?
>>>>
>>>> group by queries with collocate = true is not incorrect usage or i
>>>> misunderstood this ?
>>>>
>>>> Thanks.
>>>>
>>>> On 7 February 2017 at 01:26, vkulichenko <valentin.kulichenko@gmail.com
>>>> > wrote:
>>>>
>>>>> Well, I would agree that the flag is confusing, but your understanding
>>>>> is not
>>>>> correct. When used incorrectly, this flag does breaks query result
>>>>> because
>>>>> it forces Ignite to change execution plan to more optimal. This
>>>>> optimization
>>>>> doesn't always work though (that's actually the reason why it's
>>>>> configurable).
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> View this message in context: http://apache-ignite-users.705
>>>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Best regards,
>>> Andrey V. Mashenkov
>>>
>>
>>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
Re: NOT IN in ignite
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Anil,
If you have GroupBy condition in query and all "rows" that belong to one
group are collocated with affinity key, then group is collocated and you
can use "collocate = true" option to allow Ignite apply optimization.
Otherwise, it is not a collocated grouping and GroupBy should be apply on
reduce stage; You will get wrong result with "collocate = true" option.
Map response is always returned to node that initiate query (reduce node),
reduce stage will apply anyway.
On Tue, Feb 7, 2017 at 1:37 PM, Anil <an...@gmail.com> wrote:
> Hi Andrey,
>
> thanks for response and little confused.
>
> I hope "group" is the entries of two (or more) caches which are collocated
> with affinity key. please correct if i am wrong.
>
> "For example: In this case, we can make grouping and apply Having
> condition on map stage, that
> results in reducing network traffic." - *Agree. no aggregation in reduce
> and apply pagination and return to client ? or just return map response to
> client ?*
>
> Thanks.
>
>
> On 7 February 2017 at 14:55, Andrey Mashenkov <an...@gmail.com>
> wrote:
>
>> Hi Anil,
>>
>> Group by queries with collocate = true will be correct if:
>> for every group there is only partition that contains all data of this
>> group.
>> Ignite does not know if group data is collocated for a query, so with
>> this option we can give a hint to Ignite.
>>
>> For example: In this case, we can make grouping and apply Having
>> condition on map stage, that
>> results in reducing network traffic.
>>
>> For non-collocated groups we can't apply Having on map stage as we have
>> only part of data at this time.
>> So, in this case with collocate = true we can get wrong results.
>>
>>
>>
>> On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> May I know what do you mean by "When used incorrectly" ?
>>>
>>> group by queries with collocate = true is not incorrect usage or i
>>> misunderstood this ?
>>>
>>> Thanks.
>>>
>>> On 7 February 2017 at 01:26, vkulichenko <va...@gmail.com>
>>> wrote:
>>>
>>>> Well, I would agree that the flag is confusing, but your understanding
>>>> is not
>>>> correct. When used incorrectly, this flag does breaks query result
>>>> because
>>>> it forces Ignite to change execution plan to more optimal. This
>>>> optimization
>>>> doesn't always work though (that's actually the reason why it's
>>>> configurable).
>>>>
>>>> -Val
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context: http://apache-ignite-users.705
>>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>
>>>
>>>
>>
>>
>> --
>> Best regards,
>> Andrey V. Mashenkov
>>
>
>
--
Best regards,
Andrey V. Mashenkov
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Andrey,
thanks for response and little confused.
I hope "group" is the entries of two (or more) caches which are collocated
with affinity key. please correct if i am wrong.
"For example: In this case, we can make grouping and apply Having condition on
map stage, that
results in reducing network traffic." - *Agree. no aggregation in reduce
and apply pagination and return to client ? or just return map response to
client ?*
Thanks.
On 7 February 2017 at 14:55, Andrey Mashenkov <an...@gmail.com>
wrote:
> Hi Anil,
>
> Group by queries with collocate = true will be correct if:
> for every group there is only partition that contains all data of this
> group.
> Ignite does not know if group data is collocated for a query, so with this
> option we can give a hint to Ignite.
>
> For example: In this case, we can make grouping and apply Having condition on
> map stage, that
> results in reducing network traffic.
>
> For non-collocated groups we can't apply Having on map stage as we have
> only part of data at this time.
> So, in this case with collocate = true we can get wrong results.
>
>
>
> On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
>
>> Hi,
>>
>> May I know what do you mean by "When used incorrectly" ?
>>
>> group by queries with collocate = true is not incorrect usage or i
>> misunderstood this ?
>>
>> Thanks.
>>
>> On 7 February 2017 at 01:26, vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> Well, I would agree that the flag is confusing, but your understanding
>>> is not
>>> correct. When used incorrectly, this flag does breaks query result
>>> because
>>> it forces Ignite to change execution plan to more optimal. This
>>> optimization
>>> doesn't always work though (that's actually the reason why it's
>>> configurable).
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> View this message in context: http://apache-ignite-users.705
>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
Re: NOT IN in ignite
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Anil,
Group by queries with collocate = true will be correct if:
for every group there is only partition that contains all data of this
group.
Ignite does not know if group data is collocated for a query, so with this
option we can give a hint to Ignite.
For example: In this case, we can make grouping and apply Having condition on
map stage, that
results in reducing network traffic.
For non-collocated groups we can't apply Having on map stage as we have
only part of data at this time.
So, in this case with collocate = true we can get wrong results.
On Tue, Feb 7, 2017 at 6:29 AM, Anil <an...@gmail.com> wrote:
> Hi,
>
> May I know what do you mean by "When used incorrectly" ?
>
> group by queries with collocate = true is not incorrect usage or i
> misunderstood this ?
>
> Thanks.
>
> On 7 February 2017 at 01:26, vkulichenko <va...@gmail.com>
> wrote:
>
>> Well, I would agree that the flag is confusing, but your understanding is
>> not
>> correct. When used incorrectly, this flag does breaks query result because
>> it forces Ignite to change execution plan to more optimal. This
>> optimization
>> doesn't always work though (that's actually the reason why it's
>> configurable).
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
--
Best regards,
Andrey V. Mashenkov
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi,
May I know what do you mean by "When used incorrectly" ?
group by queries with collocate = true is not incorrect usage or i
misunderstood this ?
Thanks.
On 7 February 2017 at 01:26, vkulichenko <va...@gmail.com>
wrote:
> Well, I would agree that the flag is confusing, but your understanding is
> not
> correct. When used incorrectly, this flag does breaks query result because
> it forces Ignite to change execution plan to more optimal. This
> optimization
> doesn't always work though (that's actually the reason why it's
> configurable).
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Well, I would agree that the flag is confusing, but your understanding is not
correct. When used incorrectly, this flag does breaks query result because
it forces Ignite to change execution plan to more optimal. This optimization
doesn't always work though (that's actually the reason why it's
configurable).
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10458.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
i read the discussion and does not looks correct to me. Irrespective of
colacate flag, query must return the same results.
Collate flag must influence only internal implementation and not the actual
results.
Thanks.
On 4 February 2017 at 01:59, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> Does it work with collocated=false?
>
> Here is the dev list discussion:
> http://apache-ignite-developers.2346864.n4.nabble.
> com/SQL-query-collocated-flag-td13573.html
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10425.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Yes Val.
On 4 February 2017 at 01:59, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> Does it work with collocated=false?
>
> Here is the dev list discussion:
> http://apache-ignite-developers.2346864.n4.nabble.
> com/SQL-query-collocated-flag-td13573.html
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10425.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
Does it work with collocated=false?
Here is the dev list discussion:
http://apache-ignite-developers.2346864.n4.nabble.com/SQL-query-collocated-flag-td13573.html
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p10425.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
And i see similar issue in group by query. Group by query returning same
value with count from each node.
I am using ignite 1.7 . thanks.
On 3 February 2017 at 12:54, Anil <an...@gmail.com> wrote:
> Hi Val,
>
> Did you get a chance to check with dev list on this ? Thanks.
>
> Thanks.
>
> On 10 January 2017 at 07:51, Anil <an...@gmail.com> wrote:
>
>> Thanks Val.
>>
>> On 10 January 2017 at 04:32, vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> It works properly for me when I remove collocated flag. Not sure why is
>>> that,
>>> will ask on dev list.
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> View this message in context: http://apache-ignite-users.705
>>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p9982.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
Did you get a chance to check with dev list on this ? Thanks.
Thanks.
On 10 January 2017 at 07:51, Anil <an...@gmail.com> wrote:
> Thanks Val.
>
> On 10 January 2017 at 04:32, vkulichenko <va...@gmail.com>
> wrote:
>
>> It works properly for me when I remove collocated flag. Not sure why is
>> that,
>> will ask on dev list.
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/NOT-IN-in-ignite-tp9861p9982.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Thanks Val.
On 10 January 2017 at 04:32, vkulichenko <va...@gmail.com>
wrote:
> It works properly for me when I remove collocated flag. Not sure why is
> that,
> will ask on dev list.
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9982.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
It works properly for me when I remove collocated flag. Not sure why is that,
will ask on dev list.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9982.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
Thanks for clarification. i have attached the sample program which
reproduces the issue.
Please let me know if you are unable to reproduce. thanks.
On 7 January 2017 at 03:48, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> I mean total count. This happens automatically.
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9950.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Anil,
I mean total count. This happens automatically.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9950.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi Val,
"The aggregation should happen on the client" is not clear to me. you mean
jdbc result contains the count of results of each node ? or total count ?.
Thanks,
Anil
On 6 January 2017 at 23:49, vkulichenko <va...@gmail.com>
wrote:
> Hi,
>
> The aggregation should happen on the client and you should get the correct
> result. Are nodes discovering each other? Can you prepare a test case that
> reproduces the issue?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9940.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: NOT IN in ignite
Posted by vkulichenko <va...@gmail.com>.
Hi,
The aggregation should happen on the client and you should get the correct
result. Are nodes discovering each other? Can you prepare a test case that
reproduces the issue?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-tp9861p9940.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
Hi,
I see another wired thing with count query.
Query fired was - SELECT count(*) as COUNT FROM Person p join table(joinId
VARCHAR(50) = ?) i on p.id = i.joinId
inParameter.add("1");
inParameter.add("2");
inParameter.add("3");
inParameter.add("4");
inParameter.add("5");
inParameter.add("6");
inParameter.add("7");
inParameter.add("8");
PreparedStatement statement = conn.prepareStatement(sql);
statement.setObject(1, inParameter.toArray());
ResultSet rs = statement.executeQuery();
while (rs.next()) {
System.out.println("Count - "+ rs.getLong("COUNT"));
}
expected result - *Count - 8*
Actual results as - (in 3 node cluster)
*Count - 1*
*Count - 2*
*Count - 5*
it looks like count query is not aggregation of all counts of each node.
count of each node is returned.
Is this expected behavior ? Could you please point me to the documentation ?
Thanks
On 4 January 2017 at 17:21, Sergi Vladykin <se...@gmail.com> wrote:
>
> I am not clear how joined rows produce cartesian product.
>>
>
> I guess you have to learn SQL then:
>
> https://en.wikipedia.org/wiki/Join_(SQL)
> https://en.wikipedia.org/wiki/Cartesian_product
>
>
>>
>> Can you please correct the query ? thanks.
>>
>>
> Just use NOT IN, <> will not use index anyways.
>
> Sergi
>
>
>
>> On 4 January 2017 at 17:06, Sergi Vladykin <se...@gmail.com>
>> wrote:
>>
>>> Your query is wrong:
>>>
>>> Joined rows will produce a cartesian product of all the row pairs, thus
>>> you will have a row Person[100, 'Name100'] that correctly passed the first
>>> join and paired with n['Name200'] in the second join. Since 'Name100' <>
>>> 'Name200', it will be correctly returned in the result set.
>>>
>>> Sergi
>>>
>>> 2017-01-04 14:06 GMT+03:00 Anil <an...@gmail.com>:
>>>
>>>> HI,
>>>>
>>>> As per ignite documentation, IN operator does not use index and has to
>>>> be use as *join table.*
>>>>
>>>> Hoping this holds good for* NOT IN *operator as well.
>>>>
>>>> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
>>>> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
>>>> ?) n on n.joinId <> p.name";
>>>> PreparedStatement statement = conn.prepareStatement(sql);
>>>> statement.setFetchSize(100);
>>>> statement.setObject(1, new String[]{"100", "200", "300"});
>>>> statement.setObject(2, new String[]{"Name100", "Name200"});
>>>>
>>>>
>>>> Expected results are Name300
>>>> Actual output :
>>>> Name100
>>>> Name200
>>>> Name300
>>>> Name300
>>>>
>>>> Did you see any issue with the query ? please help.
>>>>
>>>> thanks.
>>>>
>>>
>>>
>>
>
Re: NOT IN in ignite
Posted by Sergi Vladykin <se...@gmail.com>.
> I am not clear how joined rows produce cartesian product.
>
I guess you have to learn SQL then:
https://en.wikipedia.org/wiki/Join_(SQL)
https://en.wikipedia.org/wiki/Cartesian_product
>
> Can you please correct the query ? thanks.
>
>
Just use NOT IN, <> will not use index anyways.
Sergi
> On 4 January 2017 at 17:06, Sergi Vladykin <se...@gmail.com>
> wrote:
>
>> Your query is wrong:
>>
>> Joined rows will produce a cartesian product of all the row pairs, thus
>> you will have a row Person[100, 'Name100'] that correctly passed the first
>> join and paired with n['Name200'] in the second join. Since 'Name100' <>
>> 'Name200', it will be correctly returned in the result set.
>>
>> Sergi
>>
>> 2017-01-04 14:06 GMT+03:00 Anil <an...@gmail.com>:
>>
>>> HI,
>>>
>>> As per ignite documentation, IN operator does not use index and has to
>>> be use as *join table.*
>>>
>>> Hoping this holds good for* NOT IN *operator as well.
>>>
>>> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
>>> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
>>> ?) n on n.joinId <> p.name";
>>> PreparedStatement statement = conn.prepareStatement(sql);
>>> statement.setFetchSize(100);
>>> statement.setObject(1, new String[]{"100", "200", "300"});
>>> statement.setObject(2, new String[]{"Name100", "Name200"});
>>>
>>>
>>> Expected results are Name300
>>> Actual output :
>>> Name100
>>> Name200
>>> Name300
>>> Name300
>>>
>>> Did you see any issue with the query ? please help.
>>>
>>> thanks.
>>>
>>
>>
>
Re: NOT IN in ignite
Posted by Anil <an...@gmail.com>.
I am not clear how joined rows produce cartesian product.
Can you please correct the query ? thanks.
On 4 January 2017 at 17:06, Sergi Vladykin <se...@gmail.com> wrote:
> Your query is wrong:
>
> Joined rows will produce a cartesian product of all the row pairs, thus
> you will have a row Person[100, 'Name100'] that correctly passed the first
> join and paired with n['Name200'] in the second join. Since 'Name100' <>
> 'Name200', it will be correctly returned in the result set.
>
> Sergi
>
> 2017-01-04 14:06 GMT+03:00 Anil <an...@gmail.com>:
>
>> HI,
>>
>> As per ignite documentation, IN operator does not use index and has to be
>> use as *join table.*
>>
>> Hoping this holds good for* NOT IN *operator as well.
>>
>> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
>> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
>> ?) n on n.joinId <> p.name";
>> PreparedStatement statement = conn.prepareStatement(sql);
>> statement.setFetchSize(100);
>> statement.setObject(1, new String[]{"100", "200", "300"});
>> statement.setObject(2, new String[]{"Name100", "Name200"});
>>
>>
>> Expected results are Name300
>> Actual output :
>> Name100
>> Name200
>> Name300
>> Name300
>>
>> Did you see any issue with the query ? please help.
>>
>> thanks.
>>
>
>
Re: NOT IN in ignite
Posted by Sergi Vladykin <se...@gmail.com>.
Your query is wrong:
Joined rows will produce a cartesian product of all the row pairs, thus you
will have a row Person[100, 'Name100'] that correctly passed the first join
and paired with n['Name200'] in the second join. Since 'Name100' <>
'Name200', it will be correctly returned in the result set.
Sergi
2017-01-04 14:06 GMT+03:00 Anil <an...@gmail.com>:
> HI,
>
> As per ignite documentation, IN operator does not use index and has to be
> use as *join table.*
>
> Hoping this holds good for* NOT IN *operator as well.
>
> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
> ?) n on n.joinId <> p.name";
> PreparedStatement statement = conn.prepareStatement(sql);
> statement.setFetchSize(100);
> statement.setObject(1, new String[]{"100", "200", "300"});
> statement.setObject(2, new String[]{"Name100", "Name200"});
>
>
> Expected results are Name300
> Actual output :
> Name100
> Name200
> Name300
> Name300
>
> Did you see any issue with the query ? please help.
>
> thanks.
>