Hi Alin, Ignite have no support for non-collocated subqueries [1] [2], here is a ticket [3]. Try to set collocated flag to true [4] to hint Ignite your query is collocated.
[1] http://apache-ignite-users.70518.x6.nabble.com/Does-Ignite-support-nested-SQL-Queries-td1714.html [2] http://apache-ignite-users.70518.x6.nabble.com/SQL-query-result-variation-td2889.html [3] https://issues.apache.org/jira/browse/IGNITE-5359 [4] https://apacheignite.readme.io/docs/jdbc-driver On Fri, Oct 27, 2017 at 3:32 PM, alin-corodescu <[email protected]> wrote: > Hello, > > While experimenting around with Ignite, I came across a bug regarding the > map query building. (the queries that run on each individual node). > Consider > the following dummy query (this is a reproduction of the error I found > while > testing actual production queries): > > SELECT t1.name, count(1) > FROM "default".Persons t1 > JOIN (SELECT name from "default".Persons group by name) t2 > on t1.name = t2.name > group by t1.name > > This query cannot be run on nodes because of how the map query is built. > When using explain for this query, the first line, which represents the map > query to be run on remote nodes, looks like this : > > SELECT > T1__Z0.NAME AS __C0_0, > COUNT(1) AS __C0_1 > FROM "default".PERSONS T1__Z0 > /* "default".PERSONS.__SCAN_ */ > > which is obviously an incorrect SQL query, as there an aggregation function > called without a group by clause. Thus, on each remote node, the following > exception will be thrown: > > Caused by: org.h2.jdbc.JdbcSQLException: Column "T1__Z0.NAME" must be in > the > GROUP BY list; SQL statement > > This is only happening (as far as I observed) only when joining with a > subquery containing a group by clause, and the error can be reproduced with > virtually any table. Has anyone else discovered encountered this behaviour > before? > > As a side note, enabling an index on the "name" column seems to overcome > the > problem, but it is not a viable solution for production systems with many > different queries. > > Thanks, > Alin > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ > -- Best regards, Andrey V. Mashenkov
