You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Valentin Kulichenko <va...@gmail.com> on 2017/01/09 23:22:18 UTC

SQL query collocated flag

Folks,

One of the users faced an interesting case [1]. Basically, there is a query
[2] which returns correct result when collocated flag is not set, but if
it's true, several counts for each node is received by the client without
being aggregated. Is this expected? I'm also attaching the test that
reproduces behavior.

Actually, the flag seems to be very confusing and documentation description
[3] doesn't help a lot. I'm reading it and it's not clear what has to be
collocated, what would happen if I incorrectly set the flag to true, etc.
Probably, some examples could help. Can someone elaborate?

[1]
http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-td9861.html
[2] SELECT count(*) as COUNT FROM Person p join table(joinId VARCHAR(50) =
?) i on p.id = i.joinId
[3]
https://apacheignite.readme.io/docs/configuration-parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties

-Val

Re: SQL query collocated flag

Posted by Sergi Vladykin <se...@gmail.com>.
It means exactly that: more effective plan. If we know in advance that
entries will be grouped by colocated field, then we can avoid grouping on
reduce side. If they are grouped by non-colocated field and the flag is
set, then the plan will be invalid and user will get wrong result.
I'm not sure how to write it better in docs.

Sergi

2017-01-10 23:04 GMT+03:00 Denis Magda <dm...@apache.org>:

> Sergi,
>
> What does ‘more effective query plan’ exactly means? Will the aggregation
> happen on the data nodes and why this flag set to ‘true’ leads to strange
> behavior described here [1] which sounds like a bug?
>
> As for the documentation how would you rewrite existed setCollocated
> explanation [2] so that it becomes clearer what the flag is for and how it
> works? Please provide your doc version.
>
> [1] http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-
> ignite-td9861.html
> [2] https://apacheignite.readme.io/docs/configuration-
> parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties
> <https://apacheignite.readme.io/docs/configuration-
> parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties>
>
> —
> Denis
>
> > On Jan 10, 2017, at 9:13 AM, Sergi Vladykin <se...@gmail.com>
> wrote:
> >
> > Yes, this flag indeed is very obscure. Basically it means that if you
> have
> > some query with aggregates (at the top level right now) and you know that
> > you will do GROUP BY by affinity key, then Ignite can generate a more
> > effective query plan with this flag set.
> >
> > Sergi
> >
> > 2017-01-10 2:22 GMT+03:00 Valentin Kulichenko <
> valentin.kulichenko@gmail.com
> >> :
> >
> >> Folks,
> >>
> >> One of the users faced an interesting case [1]. Basically, there is a
> >> query [2] which returns correct result when collocated flag is not set,
> but
> >> if it's true, several counts for each node is received by the client
> >> without being aggregated. Is this expected? I'm also attaching the test
> >> that reproduces behavior.
> >>
> >> Actually, the flag seems to be very confusing and documentation
> >> description [3] doesn't help a lot. I'm reading it and it's not clear
> what
> >> has to be collocated, what would happen if I incorrectly set the flag to
> >> true, etc. Probably, some examples could help. Can someone elaborate?
> >>
> >> [1] http://apache-ignite-users.70518.x6.nabble.com/NOT-
> >> IN-in-ignite-td9861.html
> >> [2] SELECT count(*) as COUNT FROM Person p join table(joinId
> VARCHAR(50) =
> >> ?) i on p.id = i.joinId
> >> [3] https://apacheignite.readme.io/docs/configuration-
> >> parameters#section--sqlfields-and-sqlfieldsquery-
> configuration-properties
> >>
> >> -Val
> >>
>
>

Re: SQL query collocated flag

Posted by Denis Magda <dm...@apache.org>.
Sergi,

What does ‘more effective query plan’ exactly means? Will the aggregation happen on the data nodes and why this flag set to ‘true’ leads to strange behavior described here [1] which sounds like a bug?

As for the documentation how would you rewrite existed setCollocated explanation [2] so that it becomes clearer what the flag is for and how it works? Please provide your doc version.

[1] http://apache-ignite-users.70518.x6.nabble.com/NOT-IN-in-ignite-td9861.html
[2] https://apacheignite.readme.io/docs/configuration-parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties <https://apacheignite.readme.io/docs/configuration-parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties>

—
Denis

> On Jan 10, 2017, at 9:13 AM, Sergi Vladykin <se...@gmail.com> wrote:
> 
> Yes, this flag indeed is very obscure. Basically it means that if you have
> some query with aggregates (at the top level right now) and you know that
> you will do GROUP BY by affinity key, then Ignite can generate a more
> effective query plan with this flag set.
> 
> Sergi
> 
> 2017-01-10 2:22 GMT+03:00 Valentin Kulichenko <valentin.kulichenko@gmail.com
>> :
> 
>> Folks,
>> 
>> One of the users faced an interesting case [1]. Basically, there is a
>> query [2] which returns correct result when collocated flag is not set, but
>> if it's true, several counts for each node is received by the client
>> without being aggregated. Is this expected? I'm also attaching the test
>> that reproduces behavior.
>> 
>> Actually, the flag seems to be very confusing and documentation
>> description [3] doesn't help a lot. I'm reading it and it's not clear what
>> has to be collocated, what would happen if I incorrectly set the flag to
>> true, etc. Probably, some examples could help. Can someone elaborate?
>> 
>> [1] http://apache-ignite-users.70518.x6.nabble.com/NOT-
>> IN-in-ignite-td9861.html
>> [2] SELECT count(*) as COUNT FROM Person p join table(joinId VARCHAR(50) =
>> ?) i on p.id = i.joinId
>> [3] https://apacheignite.readme.io/docs/configuration-
>> parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties
>> 
>> -Val
>>

Re: SQL query collocated flag

Posted by Sergi Vladykin <se...@gmail.com>.
Yes, this flag indeed is very obscure. Basically it means that if you have
some query with aggregates (at the top level right now) and you know that
you will do GROUP BY by affinity key, then Ignite can generate a more
effective query plan with this flag set.

Sergi

2017-01-10 2:22 GMT+03:00 Valentin Kulichenko <valentin.kulichenko@gmail.com
>:

> Folks,
>
> One of the users faced an interesting case [1]. Basically, there is a
> query [2] which returns correct result when collocated flag is not set, but
> if it's true, several counts for each node is received by the client
> without being aggregated. Is this expected? I'm also attaching the test
> that reproduces behavior.
>
> Actually, the flag seems to be very confusing and documentation
> description [3] doesn't help a lot. I'm reading it and it's not clear what
> has to be collocated, what would happen if I incorrectly set the flag to
> true, etc. Probably, some examples could help. Can someone elaborate?
>
> [1] http://apache-ignite-users.70518.x6.nabble.com/NOT-
> IN-in-ignite-td9861.html
> [2] SELECT count(*) as COUNT FROM Person p join table(joinId VARCHAR(50) =
> ?) i on p.id = i.joinId
> [3] https://apacheignite.readme.io/docs/configuration-
> parameters#section--sqlfields-and-sqlfieldsquery-configuration-properties
>
> -Val
>