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

["Roman Kondakov (JIRA)" <ji...@apache.org>]

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)