[jira] [Updated] (IGNITE-11448) SQL: Wrong results of select with aggregates in subquery

["Nikolay Izhikov (Jira)" <ji...@apache.org>]

     [ 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)