Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

71–80 of 222 posts

Re: SQL Anti-Patterns

#71

Post by ryandv »

dotancohen wrote: post #69
ryandv wrote: post #61

Earlier quoted context omitted.

PostgreSQL's `DISTINCT ON` extension is useful for navigating bitemporal data in which I want, for example, the latest recorded version of an entry, for each day of the year. There are few other legitimate use cases of the regular `DISTINCT` that I have seen, other than the typical one-off `SELECT DISTINCT(foo) FROM bar`.

Without DISTINCT ON (which I've never used) you can use a window function via the OVER clause with PARTITION BY. I'm pretty sure that's standard SQL.

Yes, this is the implementation I have seen in other dialects.

Re: SQL Anti-Patterns

#72

Post by SoftTalker »

magicalhippo wrote: post #66
SoftTalker wrote: post #62

A big one that isn't listed is looking for stuff that isn't there. Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already). Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

> Also, understand how your DB handles nulls. Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed. If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.

NULL should generally never be used to "mean" anything.

If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

Re: SQL Anti-Patterns

#73

Post by DrewADesign »

MobiusHorizons wrote: post #52
DrewADesign wrote: post #49

Earlier quoted context omitted.

> what I like to do is putting 1=1 after each WHERE to align ANDs nicely Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s…

I use `WHERE true` for this. Very little cognitive load parsing that. And it makes AND conditions more copy pastable. Effectively the trailing comma of SQL where clauses

I absolutely cannot see how this would do what IDE formatting can’t, but admittedly the last time I wrote any significant amount of SQL directly was in a still-totally-relevant Perl 5 application. Could you give an example or link to a file in a public repository or whatever that would show this practice in context?

Re: SQL Anti-Patterns

#74

Post by RHSeeger »

wvbdmp wrote: post #56
mbb70 wrote: post #37

Earlier quoted context omitted.

The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started

Anti-Patterns You Should Avoid: overengineering for potential future requirements. Are there real-life cases where you should design with the future in mind? Yes. Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time? Also yes.

> Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time

Indeed, along that line, I would say that DISTINCT can be used to convey intent... and doing that in code is important.

- I want to know the zipcodes we have customers in - DISTINCT

- I want to know how many customers we have in each zipcode - aggregates

Can you do the first with the second? Sure.. but the first makes it clear what your goal is.

Re: SQL Anti-Patterns

#75

Post by SoftTalker »

geysersam wrote: post #67
SoftTalker wrote: post #62

A big one that isn't listed is looking for stuff that isn't there. Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already). Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

> Using != or NOT IN (...) is almost always going to be inefficient. Why do you say that? My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive. What's the more efficient alternative?

Because they can't use indexes.

If I have a table of several million rows and I want to find rows "WHERE foo NOT IN ('A', 'B', 'C')" that's a full table scan, or possibly an index scan if foo is indexed, unless there are other conditions that narrow it down.

Re: SQL Anti-Patterns

#76

Post by tombert »

DrewADesign wrote: post #49
egeozcan wrote: post #39

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.

> what I like to do is putting 1=1 after each WHERE to align ANDs nicely Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s…

Using `WHERE 1=1` is such a common pattern that I seriously doubt it's realistically increasing "cognitive load".

I've seen it used in dozens of places, in particular places that programmatically generate the AND parts of queries. I wasn't really that confused the first time I saw it and I was never confused any time after that.

Re: SQL Anti-Patterns

#77

Post by hvb2 »

0xbadcafebee wrote: post #46
dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

`select *` is bad for many reasons, but the biggest is that the "contract" your code has with the remote data store isn't immutable. The database can change, for many different reasons, independent of your code. If you want to write reliable code, you need to make as few assumptions as possible. One of those assumptions is what the remote schema is.

Sure but columns can change data types too which 'select column's doesn't protect you from either

Re: SQL Anti-Patterns

#78

Post by Sesse__ »

geysersam wrote: post #67
SoftTalker wrote: post #62

A big one that isn't listed is looking for stuff that isn't there. Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already). Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

> Using != or NOT IN (...) is almost always going to be inefficient. Why do you say that? My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive. What's the more efficient alternative?

I'm going to assume here that we're talking about a subquery here (SELECT * FROM t1 WHERE x NOT IN ( SELECT x FROM t2 )). If you're just talking about a static list, then the basic problem is the amount of data you get back. :-)

The biggest problem with NOT IN is that it has very surprising NULL behavior: Due to the way it's defined, if there is any NULL in the joined-on columns, then _all_ rows must pass. If the column is non-nullable, then sure, you can convert it into an antijoin and optimize it together with the rest of the join tree. If not, it usually ends up being something more complicated.

For this reason, NOT EXISTS should usually be preferred. The syntax sucks, but it's much easier to rewrite to antijoin.

Re: SQL Anti-Patterns

#79

Post by aerzen »

These "anti-patterns" are just workarounds for bad language design of SQL (or lack of design actually). I'm working on a language that can run on SQL databases, so I hope it will do better with every one of these points.

If anyone wants to check out a half-done lang with lacking documentation, I'd be happy to read your feedback: https://lutra-lang.org

Re: SQL Anti-Patterns

#80

Post by majormajor »

mbb70 wrote: post #37
bandrami wrote: post #36

Earlier quoted context omitted.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started

Here we start to get close to analytics sql vs application sql, and I think that's a whole separate beast itself with different patterns and anti-patterns.
Post reply on HN