SQL Anti-Patterns • Hacker News Board

Re: SQL Anti-Patterns

#131

Post by tomjakubowski » Sat, Oct 18, 2025, 8:42 PM UTC

furyofantares wrote: post #117

BuyMyBitcoins wrote: post #70

Earlier quoted context omitted.

I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’ It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.

The most egregious one I saw, I was tracking down a bug and found code like this: bool x; ... if (x == true) { DoThing1(); } else if (x == false) { DoThing2(); } And of course neither branch was hit, because this is C, and the uninitialized x was neither 0 nor 1, but some other random value.

Sometimes this kind of thing happens after a few revisions of code, where in earlier versions the structure of the code made more sense: maybe several conditions which were tested and then, due to changing requirements, they coalesced into something which now reads as nonsense.

When making a code change which touches a lot of places, it's not always obvious to "zoom out" and read the surrounding context to see if the structure of the code can be updated. The developer may be chewing through a grep list of a few dozen locations that need to be changed.

Re: SQL Anti-Patterns

#132

Post by SoftTalker » Sat, Oct 18, 2025, 8:44 PM UTC

crazygringo wrote: post #125

SoftTalker wrote: post #72

Earlier quoted context omitted.

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.

Not sure what you mean. If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL. This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. If you're suggesting mandatory additional has_address and has_c…

No null is fine if you don’t know or there’s literally no value. But don’t interpret a null phone number to mean the customer doesn’t have a phone number. You can’t infer anything from that, other than you don’t have it.

Re: SQL Anti-Patterns

#133

Post by giovannibonetti » Sat, Oct 18, 2025, 8:55 PM UTC

dotancohen wrote: post #63

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.

I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.

I've noticed that LIMIT 1 makes a huge difference when working with LATERAL JOINs in Postgres, even when the WHERE condition has a unique constraint.

Re: SQL Anti-Patterns

#134

Post by bandrami » Sat, Oct 18, 2025, 9:02 PM UTC

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

I do reporting, not application development. If somebody wants to know different information I'd write a different query.

Re: SQL Anti-Patterns

#135

Post by RHSeeger » Sat, Oct 18, 2025, 9:03 PM UTC

dleeftink wrote: post #109

RHSeeger wrote: post #74

Earlier quoted context omitted.

> 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 firs…

Partly in jest, but maybe we need a NON-DISTINCT signaller to convey the inverse and return duplicate values only. SOMEWHAT-DISTINCT with a fuzzy threshold would also be useful.

I hear you. It's not all _that_ uncommon for me to query for "things with more than one instance". Although, to be fair, it's more common for me to that when grep/sort/uniqing logs on the command line.

Re: SQL Anti-Patterns

#136

Post by bandrami » Sat, Oct 18, 2025, 9:04 PM UTC

majormajor wrote: post #80

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

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.

Ah, yeah, you beat me to it. I do reporting, not applications.

Re: SQL Anti-Patterns

#137

Post by layer8 » Sat, Oct 18, 2025, 9:16 PM UTC

avalys wrote: post #104

layer8 wrote: post #84

Earlier quoted context omitted.

Or, for a boolean type, that XOR is the same as the inequality operator.

Maybe it’s confusing because it’s misnamed?

Is it? Two things are equal exactly when they aren’t exclusive.

Re: SQL Anti-Patterns

#138

Post by mkeedlinger » Sat, Oct 18, 2025, 9:17 PM UTC

aerzen wrote: post #79

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

Hey, this looks really cool! Best wishes and I’ll try to watch out for when this is more ready

Re: SQL Anti-Patterns

#139

Post by dotancohen » Sat, Oct 18, 2025, 9:17 PM UTC

EvanAnderson wrote: post #118

dotancohen wrote: post #113

Earlier quoted context omitted.

IDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record. I'll test again, really the last time I tested that was two decades ago.

That seems like your RDBMS wasn't handling something right there or there wasn't a unique index on the column. Do you recall what the database server was?

Yes, I was using Mysql exclusively at the time. I don't recall which version.

I also tested this once years later when doing a Python app with sqlite. Similar result, but admittedly that was not a very big table to begin with.

I am meticulous with my database schemas, and periodically review my indexes and covering indexes. I'm no DBA, but I believe that the database is the only real value a codebase has, other than maybe a novel method here and there. So I put care into designing it properly and testing my assumptions.

Re: SQL Anti-Patterns

#140

Post by dotancohen » Sat, Oct 18, 2025, 9:21 PM UTC

buckle8017 wrote: post #115

dotancohen wrote: post #113

Earlier quoted context omitted.

IDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record. I'll test again, really the last time I tested that was two decades ago.

You are certainly doing something wrong if that's true. I'm curious, can you demo this?

I'm curious as well to see if this still holds up. I'll try this week.