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
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
