matrixorigin / matrixorigin/matrixone

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

Open
#23,108 0 comments 0 reactions 1 assignee Claimed by @Ariznawlll View on GitHub
ai-light ai-medium kind/feature phase/testing priority/p0
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.