The first think you have to know about SQL in Ignite is that it works differently when you run it over replicated cache and when over partitioned cache [1].
Having said that in case of REPLICATED cache Ignite supports everything that H2 database engine does, in case of PARTITIONED cache Ingite has to split the original query into map-reduce parts to be executed across all the partitions and currently this ability to rewrite queries is quite limited: it analyzes only top level queries but not subqueries. It means that all the subqueries must be fully collocated. Lets take your example. Suppose Person is in a partitioned cache and and it is collocated with Organization which is in partitioned cache as well by affinity key `orgId`. Then we know that the top level query can be rewritten, but the subquery can not. But in this query it is not needed, because join condition is collocated and this join of the subquery to Organization can be correctly done on each node and the results can be merged and sorted in reduce step. I know it looks a bit complicated but we continuously work on improving this functionality. Currently I'd suggest to run EXPLAIN and see if map and reduce queries look correctly for you (map query will be executed on each data node, reduce query is on local node will merge results from map step). Sergi [1] https://apacheignite.readme.io/docs/sql-queries#how-sql-queries-work 2015-10-27 12:00 GMT+03:00 iceguo <[email protected]>: > I want to know whether Ignite can do nested SQL Queries such as following: > > "select a.orgid, b.name from (select orgid from person group by orgid) a > LEFT JOIN organization b on a.orgid = b.id order by b.name". > > > > -- > View this message in context: > http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-tp1714.html > Sent from the Apache Ignite Users mailing list archive at Nabble.com. >
