[DRILL-2962] Correlated subquery with scalar aggregate or scalar aggregate with expression throws and error

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.9.0
• Fix Version/s: 1.0.0
• Component/s: Query Planning & Optimization
• Assignee: Aman Sinha
• Reporter: Victoria Markman
• Created: 05/May/15 19:12
• Updated: 15/May/15 21:52
• Resolved: 06/May/15 17:56

Description

Filing this bug on Aman's request. It is not the same as DRILL-2949
DRILL-2949 has two outer tables.

0: jdbc:drill:schema=dfs> select * from t1 where a1 > (select avg(a2)*100 from t2 where t1.c1 = t2.c2);
Query failed: SYSTEM ERROR: Non-scalar sub-query used in an expression
See Apache Drill JIRA: DRILL-1937
[991f5544-d44f-4ba2-aa70-783a88a5b16a on atsqa4-133.qa.lab:31010]
Error: exception while executing query: Failure while executing query. (state=,code=0)

Correlated scalar aggregation without expression:

0: jdbc:drill:schema=dfs> select * from t1 where a1 > (select avg(a2) from t2 where t1.c1 = t2.c2);
Query failed: SYSTEM ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join
[f238c0c0-efea-4684-8ec3-899fbcc2a075 on atsqa4-133.qa.lab:31010]
Error: exception while executing query: Failure while executing query. (state=,code=0)