Re: Incorrect map query built when joining with a subquery with group by statement
Following up on the previous discussion, I have filed an issue regarding the problem : https://issues.apache.org/jira/browse/IGNITE-6865 I have found a workaround this issue by using FROM (select * from Persons) instead of FROM Persons directly, which seems like a bug, because the 2 queries should be semantically equivalent. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Incorrect map query built when joining with a subquery with group by statement
Thank you for your response, I will look further into the problem and see if I can find any workarounds. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Incorrect map query built when joining with a subquery with group by statement
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-corodescuwrote: > 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
Incorrect map query built when joining with a subquery with group by statement
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/