[
https://issues.apache.org/jira/browse/IGNITE-11448?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nikolay Izhikov updated IGNITE-11448:
-------------------------------------
Labels: h2-limitation (was: )
> 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
> Priority: Major
> Labels: h2-limitation
> 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
(v8.3.4#803005)
