[IGNITE-19402] Sql. Incorrect validation for query contains aggregates by outer query

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: calcite2-required ignite-3
• Epic Link: Improve SQL quality in AI3. Part1
• Assignee: Unassigned
• Reporter: Iurii Gerzhedovich
• Created: 02/May/23 14:54
• Updated: 10/Jul/24 06:20

Description

Sql engine consider incorrect queries which have aggregate function for outer query in WHERE  clause. For example few queries in sql/subquery/any_all/test_correlated_any_all.test are fails:

SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;

SELECT SUM(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;

SELECT MIN(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;

Validation error looks as

 Aggregate expression is illegal in WHERE clause