[
https://issues.apache.org/jira/browse/IGNITE-6865?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alin Andrei Corodescu updated IGNITE-6865:
------------------------------------------
The Ignite SQL engine builds an incorrect SQL query for the "map" part of a
distributed query when the select statements contains 2 nested group by's. I
initiated a discussion on the ignite users mailing list :
[http://apache-ignite-users.70518.x6.nabble.com/Incorrect-map-query-built-when-joining-with-a-subquery-with-group-by-statement-td17784.html]
To reproduce the error:
Consider a simple table with only one column: Persons(name), and two Ignite
nodes, each containing 2 rows for this table, for example :
Node 1 : { p1 : name = "A"; p2 : name = "B"}
Node 2 : { p3 : name = "A"; p4 : name = "B"}
Given the query :
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
The query won't be executed because a wrong map query is being built :
SELECT
T1__Z0.NAME AS __C0_0,
COUNT(1) AS __C0_1
FROM "default".PERSONS T1__Z0
/* "default".PERSONS.__SCAN_ */
The map query uses an aggregate function even though the group by has been
dropped, thus the query can't be run on any of the nodes.
I tested with the distributedJoins=true flag, and the behaviour is still the
same. When running with collocated=true however, it works (as it is expected
since the query is passed directly to the underlying H2 engine), but only
retrieves data from the current node (as it is expected) (result = (A,1 ;
B,1).
The workaround I found for this problem is to re-write the query as follows:
SELECT t1.name, count(1)
FROM (select * from "default".Persons) t1
JOIN (SELECT name from "default".Persons group by name) t2
on t1.name = t2.name
group by t1.name
This form is completely equivalent with the previous one and works as expected,
and the data returned is correctly calculated (A, 2 ; B, 2).
I also found out that the problem doesn't arise when Ignite decides to use
indexes instead of a table scan, so the problem seems to be related with table
scans.
Please mind the example given is simply to reproduce the error. It was
identified using real production queries with much more complicated structure,
but I was able to reproduce it using this simple table.
> Wrong map query build when using group by in both the outer and inner queries
> -----------------------------------------------------------------------------
>
> Key: IGNITE-6865
> URL: https://issues.apache.org/jira/browse/IGNITE-6865
> Project: Ignite
> Issue Type: Bug
> Security Level: Public(Viewable by anyone)
> Components: sql
> Affects Versions: 2.3
> Reporter: Alin Andrei Corodescu
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
