1. SQL in partitioned cache works the following way: Ignite splits top level query into 2 separate queries - one for "map" phase and another one for "reduce". All the "map" queries are executed on all the data nodes, the results are combined together on the node which initiated the query and those combined results are getting reduced by "reduce" part of the query. Since currently we split query only at the top level, your sub-query (which becomes part of "map" query) must produce correct independent result part on each node. For that you need to setup correct data collocation. In your example it looks like you should collocate your entries by Customer, so that all the entries for a single customer will be on the same node. See https://apacheignite.readme.io/docs/affinity-collocation
2. There are virtually no limitation for queries with correct collocation :) And we continuously improve SQL support. The limitations are mostly come up from the way Ignite splits queries and the way you collocate data. How you collocate your data you decide yourself, what does Ignite with SQL you can check by running EXPLAIN, it will return plan for "map" and "reduce" parts. Sergi 2016-02-09 18:26 GMT+03:00 Syed Moizuddin <[email protected]>: > Thanks Sergi, > > The model provided in the example is just a simplified version of our > production model which I am evaluating on ignite. One of our production > query is a two-level query as in the example. I have two follow-up > questions then: > 1. Most of our queries are two level nested and the outer one is always > group by on 2 particular keys, and inner query is group by on one of the > two. Can you please suggest how to setup the data model to make such > queries work. > 2. As per Ignite documentation > <https://apacheignite.readme.io/docs/sql-queries> , there is virtually no > limitation on queries. I would like to know if there are more such > scenarios so that I could verify my production queries are compliant with > Ignite. > > Regards, > Moiz > > On Tue, Feb 9, 2016 at 8:14 PM, Sergi Vladykin <[email protected]> > wrote: > >> Hi Syed, >> >> I'm not sure I understand you data model, but the problem here is that >> Ignite supports distributive aggregate functions in SQL only at the top >> level query. I mean *sum(bal)* will work correctly always, but *min(balance) >> *will run on each node separately (in general all the sub-queries will >> run on each partition). To make this query work you have to setup you data >> model so that every group in sub-query GROUP BY will be on separate node. >> I'm not sure I understand your data model to give a more precise advice. >> >> Sergi >> >> >> >> 2016-02-09 7:18 GMT+03:00 vkulichenko <[email protected]>: >> >>> Hi, >>> >>> I'm having the same behavior and it doesn't look correct. I created a >>> ticket >>> for this: https://issues.apache.org/jira/browse/IGNITE-2592. Someone in >>> the >>> community will take a look. >>> >>> -Val >>> >>> >>> >>> -- >>> View this message in context: >>> http://apache-ignite-users.70518.x6.nabble.com/SQL-query-result-variation-tp2889p2892.html >>> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >>> >> >> >
