Feature Not Supported: Nested aggregate functions in HAVING clause expressions not supported

[dengn]

Description

MatrixOne does not support nested aggregate functions in HAVING clause expressions. When attempting to use nested aggregates (e.g., AVG((SELECT COUNT(*) ...))) in HAVING clauses, MatrixOne returns an error indicating that the expression executor is not yet implemented.

Error Message

ERROR 20102 (HY000): unsupported expression executor for typ:<id:23 notNullable:true > sub:<node_id:3 row_size:1 >  now is not yet implemented

Affected Cases

This issue affects 2 test cases from our boundary testing on the industry_radar_test database.

Failing SQL Statements

Example 1: HAVING with Nested Aggregate in Subquery

SELECT province, COUNT(*) FROM company 
GROUP BY province 
HAVING COUNT(*) > AVG((SELECT COUNT(*) FROM company GROUP BY province)) 
LIMIT 10;

Error: ERROR 20102 (HY000): unsupported expression executor for typ:<id:23 notNullable:true > sub:<node_id:3 row_size:1 > now is not yet implemented

Example 2: HAVING with Subquery Aggregate

SELECT province, COUNT(*) FROM company 
GROUP BY province 
HAVING COUNT(*) > (SELECT COUNT(*) FROM company) 
LIMIT 10;

Error: ERROR 20102 (HY000): unsupported expression executor for typ:<id:23 notNullable:true > sub:<node_id:3 row_size:1 > now is not yet implemented

Expected Behavior

In MySQL, HAVING clauses can contain subqueries with aggregate functions. The subquery is evaluated and the result is used in the HAVING condition.

Example MySQL behavior:

-- MySQL: Fully supported
SELECT province, COUNT(*) 
FROM company 
GROUP BY province 
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) as cnt FROM company GROUP BY province) as subq);

Impact

• Functionality: Complex HAVING conditions with subqueries are not supported
• Query Flexibility: Users cannot write advanced filtering conditions in HAVING clauses
• Migration: Applications using nested aggregates in HAVING will fail when migrating to MatrixOne

Use Cases

HAVING with nested aggregates is used for:

1. Comparative Aggregation: Comparing group aggregates against overall aggregates
2. Statistical Filtering: Filtering groups based on statistical measures
3. Advanced Analytics: Complex analytical queries requiring nested aggregations

Suggested Fix

1. Expression Executor: Implement support for nested aggregate expressions in HAVING clauses
2. Subquery Evaluation: Ensure subqueries in HAVING clauses are properly evaluated
3. Type Handling: Properly handle type conversion and validation for nested aggregates
4. Performance: Optimize execution of nested aggregates in HAVING clauses
5. Testing: Add comprehensive tests for various nested aggregate patterns in HAVING

Related Issues

This is related to Issue #5 (Nested aggregate functions in SELECT expressions), but specifically affects HAVING clauses.

Related MySQL Documentation

• MySQL HAVING Clause
• MySQL Subqueries in HAVING