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/
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
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/
Cluster nodes failures while under load
Hello, I have a pretty vague description for the problem I am having with my Ignite cluster and I was wondering if anyone is experiencing similar behaviour. I am holding an in-memory database on an Ignite cluster, with about 500 GB of data in it (across 3 nodes). Data is constantly being streamed into the cache while other entries are evicted / expire (no memory problems). When trying to run complex queries (diverse, and sometimes they work, sometimes they don't) using the JDBC driver, some nodes fail (they either leave the topolgy - but somehow the node is still up, forming a topology by himself, or the ignite node gets shut down completely) - this usually happens after a spike in CPU usage (due to query execution). Logs aren't that helpful in this matter, simply saying that the respective node is unreachable). I tested this cluster using the 2.1 version of Ignite, which doesn't lazily stream the result sets from other nodes to the reducer node (as it can be done in 2.3), and I suspect the behaviour might be caused by loading the whole result set in memory at once. I tried adjusting the JVM heap size to 20G per node, set failure detection timeout on each node to be 5 (10 times higher than the default), reduce query parallelism for the cache, increased the system thread pool size, but to no avail. One thing to mention is that the cpu usage even during spikes was at about 70%. I am trying my luck here, maybe someone experienced something similar, as I am aware that the description is not very precise. I will update with any other findings which are relevant to this problem. Thank you, Alin -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
