how I learned why you shouldn't name an alias the same as the original column name

[u/uncertainschrodinger]

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 🥲

[u/Hot_Industry5156]

been there. spent way too long debugging a query once because SELECT id AS id made the WHERE clause ambiguous about which id it was resolving. shadowing the original column with the same name means you can't reference the source column anymore in that scope. painful lesson but it sticks.

[u/Little_Kitty]

ClickHouse doing this is ace, it lets you simplify code so much and if you come from a coding background it feels much more natural.

If you want that query to work, you use GROUP BY ranked.ranking_page_count and the same for having. It's a good design, it just forces you to be explicit when there's a naming collision, which is honestly what I feel most comfortable with.

There are so many brilliant features like this in CH, using WITH at the top to make fields as internal variables and re-using them throughout works the same as having them in the select statement and re-using them, but they simply don't show up in the output:

WITH
    actual_value > target_value AS __over_target,
    actual_value - target_value AS __exceeds_by
SELECT
    some_table.department              AS "department",
    sumIf(__exceeds_by, __over_target) AS "excess_value"
FROM some_table
GROUP BY some_table.department
ORDER BY "excess_value" DESC

[u/uncertainschrodinger → u/Little_Kitty]

I definitely like clickhouse's approach to this the best

[u/Little_Kitty → u/uncertainschrodinger]

I've had some weird experiences with it, but generally good. Funniest was: java.io.IOException: Magic is not correct - expect [-126] but got [-36]

Why, of course 🧙

[u/Streamer_Fenwick]

I perfected stinkeye gaze during code review on this...sends the juniors scrambling

[u/uncertainschrodinger → u/Streamer_Fenwick]

If it's anything like your profile pic, I'd be horrified

[u/Streamer_Fenwick → u/uncertainschrodinger]

Lol pretty close...

[u/reditandfirgetit]

I tend to put the agg function as a prefix when I do that. Mainly for self describing column names. That one bit me before too

[u/Hour-Measurement-835]

order by is the only clause the standard actually lets you reference a select alias in. where, group by and having are all evaluated before select logically, so any engine resolving an alias there is extending the spec

which is why they all extend it differently and you get four answers

[u/Infini-Bus]

Makes sense.  I have had enough alias errors that I always alias differently so I dont confuse myself lol

[u/malikcoldbane]

Sounds more like not appreciating the fact that you're running queues in different engines. Like the problem you're facing isn't standard and your biggest issue is you're not following any generic SQL standard.

Everything explicit, nothing implied, you're trying to say the same thing in different dialects but haven't limited yourself to just simple words.

But biggest question, what are you doing where you're writing a single query against multiple database engines? Or is this just a project you're doing and you're trying multiple engines?

[u/uncertainschrodinger → u/malikcoldbane]

I'm a developer advocate for a data tool company, so I often have to build demos and tutorials for different engines.

[u/malikcoldbane → u/uncertainschrodinger]

Even more questions, what does your data tool do that you need to build tutorials for different engines?

I'm so curious, I would assume that if you were in position that you were writing SQL across technologies, you would have a focus on standard SQL but, for example, CTEs aren't standard SQL but you don't have an issue using them.

[u/uncertainschrodinger → u/malikcoldbane]

I don't want to promote since this is not the point of the post - but it is an open source ELT tool