171–180 of 222 posts
Earlier quoted context omitted.
Only if your table is missing an unique index on that column, which it should have to enforce your assumption, so yeah LIMIT 1 is a code (or schema in the case) smell.
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.
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.
> 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.
SQL is somehow "ask two people, get three different opinions" for something as basic as: "given a BTreeMap >, how do I do .keys() and .len()".
The big justification for its design is to enable compiler optimizations (query planning) but compilers can optimize imperative code very well too, so I wonder if you could get the same benefits with a language that's less declarative.
> 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.
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
Earlier quoted context omitted.
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
It depends when you see it, but I agree that DISTINCT shouldn't be used in production. If I'm writing a one off query and DISTINCT gets me over the finish line sparing me a few minutes then that's fine.
If I have to work with one more "custom field" or "classifier" implementation, I am going to cry. Your business domain isn't too hard to model, if you need a 100 different "entities" as a part of it, then you should have at least 100 different tables, instead of putting everything into an ill fitting grab bag. Otherwise you can't figure out what is connected to what by just looking at the foreign keys pointing to and from a table, because those simply don't exist. Developers inevitably end up creating shitty polymorphic links with similarly inevitable data integrity issues and also end up coupling the schema to the back end, so you don't get like "table" and "table_id" but rather "section" and "entity_id" so you can't read the schema without reading the back end code either. Before you know it, you're not working with the business domain directly, but it's all "custom fields this" and "custom fields that" and people end up tacking on additional logic, like custom_field_uses, custom_field_use_ids, custom_field_periods, custom_field_sources and god knows what else. If I wanted to work with fields that much, I'd go and work on a farm. Oh, you're afraid of creating 100 tables? Use codegen, even your LLM of choice has no issues with that. Oh, you're too afraid that you're gonna need to do blanket changes across them and will forget something? Surely you're not above a basic ADR, literally putting a Markdown file in a folder in the repo. Oh, you're afraid that something will go wrong in those 100 migrations? How is that any different than you building literally most of your app around a small collection of tables and having fewer migrations that will affect pretty much everything? Don't even get me started on what it's like when the data integrity issues and refactoring gone bad starts. Worst of all, people love taking that pattern and putting it literally everywhere, feels like I'm taking crazy pills and nobody seems to have an issue what it's like when most of the logic in your app has something to do with CustomFieldService.
Fuck EAV/OTLT, thanks for coming to my rant. When it comes to bad patterns, it's very much up there, alongside using JSON in a relational database for the data that you can model and predict and put into regular columns, instead of just using JSON for highly dynamic data.
> Excessive View Layer Stacking
> In larger data environments, it’s easy to fall into the trap of layering views on top of views. At first, this seems modular and organized. But over time, as more teams build their own transformations on top of existing views, the dependency chain becomes unmanageable. Performance slows down because the database has to expand multiple layers of logic each time, and debugging turns into an archaeological dig through nested queries. The fix is to flatten transformations periodically and materialize heavy logic into clean, well-defined base views or tables.
I will say that this is nice to strive for, but at the same time, I much prefer having at least a lot of views instead of dynamically generated SQL by the application (see: myBatis XML mappers), because otherwise with complex logic it's impossible to predict exactly how your application will query the DB and you'll need to run the app locally with logging debug levels on so you see the actual SQL, but god forbid you have noisy DB querying or an N+1 problem somewhere, log spam for days, so unpleasant to work with. It's even more fun when people start nesting mappers and fucking around with aliases, just give me MongoDB at this point, it's web scale.
> 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.
Oh you looked the schema for t and it said x has a PRIMARY or UNIQUE constraint?
Ah well two minutes after you looked at the schema Tom removed the UNIQUE constraint. Now your scratching your head when you get duplicates.
Sql is a bag language not a set language. The contract with relation t is that if the runtime can find there rel t and attribute x it will return it. You may end up with rows or not, and you may end up with duplicates or not, and the type of x may change between subsequent execution.
So if you want a set you need to say so using DISTINCT. At runtime the query planner will check the schema and if the attribute is UNIQUE or PRIMARY it will not have to do a deduplication.
Earlier quoted context omitted.
> The way to do it without using a nullable column I mean, you could, but having separate tables for every optional field would be an organizational and usability nightmare. Queries would be longer and slower for no good reason. Not to mention a gigantic waste of space with all those repeated primary keys and their indexes. And you could have databases that prohibited NULL values, but we mostly don't, because they're…
> but having separate tables for every optional field would be an organizational and usability nightmare I think this indicates that declaring and managing state is too onerous in SQL.
The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It's a fundamentally overcomplicated idea. It's like a programming language where every variable that could be null had to be put in its own file. It would be terrible design.
Earlier quoted context omitted.
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
Because a city/region/state can be uniquely identified with a postal code (hell, in Ireland, the entire address is encapsulated in the postal code), but the reverse is not true. At scale, repeated low-cardinality columns matter a great deal.
The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable. https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates...
I've been working with SQL for 20+ years, and have literally never come across that word a single time in any documentation, tutorial, Stack Overflow answer, or here on HN. Working in Postgres, MySQL and SQLite.
Is it used at some particular company, or open source community, or with a particular database, or something?