Incorrect map query built when joining with a subquery with group by statement

[alin-corodescu <al...@gmail.com>]

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

[alin-corodescu <al...@gmail.com>]

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

[alin-corodescu <al...@gmail.com>]

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

[Andrey Mashenkov <an...@gmail.com>]

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 <al...@gmail.com>
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