Quantcast

NOT IN in ignite

classic Classic list List threaded Threaded
38 messages Options
12
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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.
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]>:
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.

Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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 <[hidden email]>:
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.


Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite


I am not clear how joined rows produce cartesian product.

I guess you have to learn SQL then:

 

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 <[hidden email]> 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 <[hidden email]>:
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.



Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> wrote:

I am not clear how joined rows produce cartesian product.

I guess you have to learn SQL then:

 

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 <[hidden email]> 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 <[hidden email]>:
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.




vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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.

vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

Anil,

I mean total count. This happens automatically.

-Val
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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.


Person.java (1K) Download Attachment
TestManager.java (10K) Download Attachment
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

It works properly for me when I remove collocated flag. Not sure why is that, will ask on dev list.

-Val
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

Thanks Val.

On 10 January 2017 at 04:32, vkulichenko <[hidden email]> 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.

Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

Hi Val,

Did you get a chance to check with dev list on this ? Thanks.

Thanks.

On 10 January 2017 at 07:51, Anil <[hidden email]> wrote:
Thanks Val.

On 10 January 2017 at 04:32, vkulichenko <[hidden email]> 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.


Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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 <[hidden email]> wrote:
Thanks Val.

On 10 January 2017 at 04:32, vkulichenko <[hidden email]> 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.



vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

Yes Val.

On 4 February 2017 at 01:59, vkulichenko <[hidden email]> 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.

Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

In reply to this post by vkulichenko
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 <[hidden email]> 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.

vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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.

Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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 <[hidden email]> 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.




--
Best regards,
Andrey V. Mashenkov
Regards, Andrew.
Anil Anil
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: NOT IN in ignite

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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.




--
Best regards,
Andrey V. Mashenkov

12
Loading...