Subqueries with aggregates may return wrong results due to incorrect splitting.
Let's consider a table person:
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:
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.