Related — Postgres (other RDBMS's too, probably) has JOIN b USING (column, ...) and NATURAL JOIN. IIRC, both fail when there's ambiguity in column names.
I'm using PG 20 years, never used those. Observed code reviews where it was frowned upon. Precisely because of the ambiguity issue, possible future footgun.
I do not use SQL in anger on a regular basis at my day job. So I'm no expert. But it seems to me that the footgun is still there, it just fails slowly instead of immediately. The "fail immediately on ambiguity" footgun will manifest itself immediately in a test environment. The "performance is bad if column names are ambiguous" footgun is more difficult to observe in test, and may not show up at all if your test environment doesn't mimic prod closely enough. But it will bring down prod with precious little help from your logs/analytics: it will tell you one of your queries is taking exponentially more time than it used to, but can't tell you why. The fact that it takes exponentially more time means that your database servers will slow all requests, perhaps catastrophically. The "fail immediately on ambiguity" footgun will have obvious "query failed because <reason>", which depending on how good your logging is might tell you exactly what the problem is. (I do use SQL at work -- rarely. But in our app a failed query just returns a "fail" error code with no additional information. Soo... yeah.) And in the meantime, the database servers are not performing full row scans, meaning unaffected queries are unaffected.
Again, I'm no expert, but I'm a huge fan of "fail fast" in general. I'd rather have a miswritten query refuse to execute instead of executing poorly. I guess if you have an SLA where you're allowed to ... stretch the truth about the state of your servers, it's better to have everything grind to a half than error immediately. So that way your dashboard stays green. I guess I can see it both ways.
Neither did I. I tend to avoid RBDMS-specific syntax extensions of questionable benefit.
The only thing I really miss in PG is ASOF JOIN. It's possible to implement it via CROSS JOIN LATERAL, but in my case it resulted in nested loops — coming from ClickHouse it bothers me that there is no way (that I know of) to hint PG to use a specific join strategy.
edoceo|4 years ago
nwallin|4 years ago
Again, I'm no expert, but I'm a huge fan of "fail fast" in general. I'd rather have a miswritten query refuse to execute instead of executing poorly. I guess if you have an SLA where you're allowed to ... stretch the truth about the state of your servers, it's better to have everything grind to a half than error immediately. So that way your dashboard stays green. I guess I can see it both ways.
mgradowski|4 years ago
The only thing I really miss in PG is ASOF JOIN. It's possible to implement it via CROSS JOIN LATERAL, but in my case it resulted in nested loops — coming from ClickHouse it bothers me that there is no way (that I know of) to hint PG to use a specific join strategy.