matrixorigin / matrixorigin/matrixone
Feature Not Supported: Nested aggregate functions in HAVING clause expressions not supported
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 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: sub: 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
```sql
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: sub: now is not yet implemented`
### Example 2: HAVING with Subquery Aggregate
```sql
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: sub: 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:
```sql
-- 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](https://dev.mysql.com/doc/refman/8.0/en/select.html#select-having)
- [MySQL Subqueries in HAVING](https://dev.mysql.com/doc/refman/8.0/en/subqueries.html)
Contributor guide
Assessment
This issue has not been assessed yet.