PERF018: Detect HAVING without aggregate function

[RAprogramm]

Summary

Add a rule to detect HAVING clauses that don't use aggregate functions — a common mistake where WHERE should be used instead.

Why This Matters

HAVING without aggregates is incorrect SQL usage:

• Wrong execution order — HAVING filters after GROUP BY, WHERE filters before
• Wasted processing — groups are created only to be filtered out
• Confusing intent — makes code harder to understand
• Sometimes slower — filtering happens too late in execution

Execution Order Reminder

1. FROM / JOIN    — get data
2. WHERE          — filter rows (before grouping) ← early, efficient
3. GROUP BY       — create groups
4. Aggregates     — compute SUM, COUNT, etc.
5. HAVING         — filter groups (after aggregates) ← late, for aggregates only
6. SELECT         — choose columns
7. ORDER BY       — sort results

Wrong vs Right

-- WRONG: filtering by non-aggregate in HAVING
SELECT category, SUM(price) as total
FROM products
GROUP BY category
HAVING category = 'electronics';

-- RIGHT: filter before grouping
SELECT category, SUM(price) as total
FROM products
WHERE category = 'electronics'
GROUP BY category;

-- RIGHT use of HAVING (aggregate condition)
SELECT category, SUM(price) as total
FROM products
GROUP BY category
HAVING SUM(price) > 1000;

Rule Specification

Property	Value
Rule ID	PERF018
Name	HAVING without aggregate function
Category	Performance
Severity	Warning
Default	Enabled

Detection Patterns

-- Should trigger (HAVING filters non-aggregate)
SELECT * FROM orders GROUP BY status HAVING status = 'active';
SELECT category, COUNT(*) FROM products GROUP BY category HAVING category LIKE 'A%';

-- Should NOT trigger (correct HAVING usage)
SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10;
SELECT category, SUM(price) FROM products GROUP BY category HAVING SUM(price) > 1000;
SELECT user_id, AVG(amount) FROM orders GROUP BY user_id HAVING AVG(amount) > 100;

Aggregate Functions to Check For

• COUNT, SUM, AVG, MIN, MAX
• GROUP_CONCAT, STRING_AGG
• STDDEV, VARIANCE
• Window functions when misused

Suggested Message

"HAVING clause without aggregate function. Use WHERE instead to filter rows before grouping — it's more efficient and semantically correct. HAVING should only be used for aggregate conditions (COUNT, SUM, AVG, etc.)."

References

• PostgreSQL HAVING
• MySQL HAVING
• SQL HAVING vs WHERE
• SQL Query Processing Order

Implementation Notes

• Parse HAVING clause content
• Check if any aggregate function present: COUNT(, SUM(, AVG(, MIN(, MAX(, etc.
• If no aggregate found → trigger warning
• Consider: some databases allow HAVING without GROUP BY (rare, but valid)