You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by "Roman Kondakov (JIRA)" <ji...@apache.org> on 2019/02/28 10:46:00 UTC
[jira] [Created] (IGNITE-11448) SQL: Wrong results of select with
aggregates in subquery
Roman Kondakov created IGNITE-11448:
---------------------------------------
Summary: SQL: Wrong results of select with aggregates in subquery
Key: IGNITE-11448
URL: https://issues.apache.org/jira/browse/IGNITE-11448
Project: Ignite
Issue Type: Bug
Components: sql
Reporter: Roman Kondakov
Attachments: Subquery reproducer
Subqueries with aggregates may return wrong results due to incorrect splitting.
Let's consider a table {{person}}:
{noformat}
SELECT id, firstName FROM person:
[1, firstName1],
[2, firstName2],
[3, firstName3],
[4, firstName4],
[5, firstName5],
[6, firstName6],
[7, firstName7],
[8, firstName8],
[9, firstName9],
[10, firstName10]
{noformat}
The result of query {{SELECT COUNT(\*) FROM person}} is {{10}}, which is correct.
The result of query {{SELECT * FROM person WHERE id = 10}} is {{[10, firstName10]}}, which is also correct.
But the result of the query {{SELECT * FROM person WHERE id = (SELECT COUNT(\*) FROM person)}} is {{[1, firstName1]}} which is completely wrong.
The root cause of this behavior is the incorrect query splitting. The latest query is split into these parts:
Map:
{noformat}
SELECT
__Z0.ID __C0_0,
__Z0.FIRSTNAME __C0_1
FROM PUBLIC.PERSON __Z0
WHERE __Z0.ID = (SELECT
COUNT(*)
FROM PUBLIC.PERSON __Z1)
{noformat}
Reduce:
{noformat}
SELECT
__C0_0 ID,
__C0_1 FIRSTNAME
FROM PUBLIC.__T0
{noformat}
As we can see, aggregate {{COUNT(\*)}} is calculated locally on each map node instead of calculating a single global aggregate and then using it in predicate.
Reproducer is attached.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)