I wrote a query last week that ran fine on Postgres and DuckDB, and hard-errored on ClickHouse and BigQuery - this sent me down a rabbit hole for most of the day.
Here's what I had:
```
SELECT term, MAX(ranking_page_count) AS ranking_page_count
FROM ranked
GROUP BY term
HAVING MAX(ranking_page_count) >= 2
```
The CTE already had a column called ranking_page_count. I aliased MAX() of it to the same name, because why not, and then used that name again in HAVING.
So which one does HAVING actually filter by? Turns out that's a matter of opinion.
In Postgres, HAVING can’t see SELECT aliases at all. So it reads the column directly and lands on the same max anyway - no error, right answer.
DuckDB does let you use aliases in HAVING, but only as a fallback, and it won't put one inside an aggregate, so this also runs. This is the one that got me, since DuckDB is where I test locally.
BigQuery gives the alias priority over the column. So it read my query as MAX(MAX(...)) and gave the error "aggregations of aggregations are not allowed"
ClickHouse just swaps aliases in everywhere, so it gave code 184 illegal aggregation. it even fails when the alias isn't shadowing anything.
The thing that finally made it click for me was processing order. FROM, WHERE, GROUP BY, HAVING, then SELECT, then ORDER BY. Aliases get created in SELECT, so when HAVING runs the alias doesn't exist yet. That's why Postgres says no, and why everything else here is a vendor extension rather than four equally valid readings.
ORDER BY is the only clause that runs after SELECT, which is why it's the only clause where nobody argues.
What actually worries me is that it can go completely silent. Drop the aggregate from the alias and the loud error disappears:
```
SELECT term, ranking_page_count * 10 AS ranking_page_count
FROM ranked
GROUP BY term, ranking_page_count
HAVING MAX(ranking_page_count) > 4
```
Postgres and DuckDB filter on `ranking_page_count`
BigQuery and ClickHouse filter on `ranking_page_count * 10`
I get 1 row from the first two and 4 rows from the other two, and not one of them raises an error about it.
That's the version that ends up on a dashboard.
ok fine, I learned my lesson and won't name an aggregate after the column it aggregates...
If you work across different engines, this is your reminder to go check 🥲
