I think most people think an anti-pattern is an aberration in the "solution" section that creates more problems.
So here, the anti-pattern is that people use a term so casually (e.g., DevOps) that no one knows what it's referring to anymore.
(The problem: need a way to refer to concept(s) in a pithy way. The solution: make up or reuse an existing word/phrase to incorporate the concept(s) by reference so that it can can, unambiguously, be used as a replacement for the longer description. )
Strange choice of example! I'm not sure I agree that your example is a common problem, and I'm even less sure that the proposed solution to it is generally useful.
it isn't, is the thing.
if you read the book design patterns, they spell out what a pattern is.
if you read the book anti-patterns, he spells out what an anti-pattern is.
people have gotten the wrong idea by learning the phrases from casual usage.
but also, the book anti-patterns is pretty clear here
Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.
'Landed table'? Is that the 'fact table', the one that contains the codes that need to be looked-up?
* in whatever order they're used
if your case statement is just a series of straighahead "WHEN x=this THEN that", you're very lucky.
the nasty case statements are the ones were the when expression sometimes uses different pieces of data and/or the ordering of the statements is important.
Why wouldn’t you store this information in a table and query it when you need it? What if you need to support other languages? With a table you can just add more columns for more languages!
query WHERE name = ‘abc’
create an indexed UPPER(name) column"
Should there be an "or" between these 2 points, or am I missing something? Why create an UPPER index column and not use it?
Unfortunately I learned this the hard way!
Otoh, it seems a fairly stable language (family of dialects?) so finding the pitfalls has long leverage
ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
For example, you define an index on UPPER(name_column), and in your query you can use WHERE UPPER(name_to_search_for) = UPPER(name_column), and it will use the index.
> query WHERE name = ‘ABC’
> create an indexed UPPER(name) column
The point is that the index itself is already on the data with the function applied. So it's not a full scan, the way the original query was.
Of course, in this particular example you just want to use a case-insensitive collation to begin with. But the general concept is valid.
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.
Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.
Distinct is also easily explained to users, who are probably familiar with Excel’s “remove duplicate rows”.
It can also be great for exploring unfamiliar databases. I ask applicants to find stuff in a database they would never see by scrolling, and you’d be surprised how many don’t find it.
>less verbose
Well…
In any case, it depends. OP nicely guarded himself by writing “overusing”, so at that point his pro-tip is just a tautology and we are in agreement: not every use of DISTINCT is an immediate smell.
SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x );
SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 );
SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x);
You could argue that you could fake a semijoin using
SELECT DISTINCT * FROM t1 JOIN t2 USING (x);
SELECT * FROM t1 JOIN t2 USING (x) GROUP BY t1.*;
SELECT * FROM t1 SEMIJOIN t2 USING (x);
although it creates some extra problems for the join optimizer.
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.
SOMEWHAT-DISTINCT with a fuzzy threshold would also be useful.
> I immediately become suspicious
All I read from that is, when DISTINCT is used, it's worth taking a look to make sure the person in question understands the data/query; and isn't just "fixing" a broken query with it. That doesn't mean it's wrong, but it's a "smell", a "flag" saying pay attention.
There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.
`if(X&IsFooMask != 0)`
:)
bool x;
...
if (x == true) {
DoThing1();
} else if (x == false) {
DoThing2();
}
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.
https://hackage.haskell.org/package/base-4.21.0.0/docs/Data-...
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`.
I'll test again, really the last time I tested that was two decades ago.
I'm curious, can you demo this?
Do you recall what the database server was?
Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.
Though sure, known to negatively affect performance, I think in some database systems more than in others?
> Schema evolution can break your view, which can have downstream effects
Select * is the problem itself in the face of schema evolution and things like name collision.
In sqlite, the view definition will be automatically expanded and one of the columns in the output will automatically be distinguished with an alias. Which column name changes is dependent on the order of tables in the join. This can absolutely break code.
In postgres, the view columns are qualified at definition time so nothing changes immediately. But when the view definition gets updated you will get a failure in the DDL.
In any system, a large column can be added to one of the constituent tables and cause a performance problem. The best advice is to avoid these problems and never use "select *" in production code.
This mirrors how adding additional fields to an object type in a programming language usually isn’t considered a breaking change, but changing the type of an existing field is.
In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?
If you want to build a pipeline and store each intermediate result, most tooling will make that easy for you. E.g. in dbt, just put each subquery in its separate file, and the processing engine will correctly schedule each subresult after the other. Just make sure you have enough storage available, it's not uncommon for intermediate results to be hundreds of times larger than the end result (e.g. when you perform a full table join in the first CTE, and do target filtering in another).
In some languages, a series of assignments and a large expression will often compile to the same thing, but if written as assignments, it will make it easier to set breakpoints.
where a=1
And k=2
And v=3Frankly, 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 annoying functionless clutter making their job more annoying. “Wait, that should do nothing… but does it actually do something hackish and ‘clever’ that they didn’t comment? Let’s think about this for a minute.” Use an editor with proper formatting capability, and don’t use executable expressions for formatting in code that other people look at.
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.
No, you ask the DB to EXPLAIN itself to you.
Translating status codes into English or some other natural language? That's better done in the application, not the database. Maybe even leave it to the frontend if you have one. As a rule of thumb, any transformation that does not affect which rows are returned can be applied in another layer after those rows have been returned. Just because you know SQL doesn't mean you have to do everything in SQL.
Deeply nested subqueries? You might want to split that up into simpler queries. There's nothing shameful about throwing three stones to kill three birds, as long as you don't fall into the 1+N pattern. Whoever has to maintain your code will thank you for not trying to be too clever.
Also, a series of simple queries often run faster than a single large query, because there's a limit to how well the query planner can optimize an excessively complicated statement. With proper use of transactions, you shouldn't have to worry about the data changing under your feet as you make these queries.
I wrote a small tutorial (~9000 words in two parts) on how to design complicated queries so that they don't need DISTINCT and are basically correct by construction.
https://kb.databasedesignbook.com/posts/systematic-design-of...
Edit: it’s also actually a book!
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 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.
If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.
I guess you would handle it in the application and not in the query, right?
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_customer_id fields, I would disagree. You'd be reinventing a database tool that already exists precisely for that purpose.
Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0]
> If you're suggesting mandatory additional has_address and has_customer_id fields, I would disagree. You'd be reinventing a database tool that already exists precisely for that purpose.
The way to do it without using a nullable column is to introduce another table for the 'optional' data, and use a left outer join.
[0] https://en.wikipedia.org/wiki/First_normal_form#Christopher_...
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?
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.
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.
Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wisely.
How that auto-formatter indents is borderly almost a hate crime. A thousand times better to indent manually.
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
* Don't store UUIDs as strings.
* Don't use random UUID variants for your primary key (or don't use UUIDs for your primary key).
* Don't use a random column in your clustered index.
User Defined Functions (UDFs) are another option to consolidate the logic in one place.
> Using Functions on Indexed Columns
In other words, the query is not sargable [0]
> Overusing DISTINCT to “Fix” Duplicates
Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:
ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
[0] https://en.wikipedia.org/wiki/Sargable
[1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...
The funny thing is it's actually several of those languages. :-)
was surprised to not see anything about dates/time.
Very often I have seen this problem buried in code design and it always sucks. Sometimes an orm obscures this but the basic antipattern looks like
Select some stuff
For each row in stuff:
… do some important things …
Select a thing to do with this row
… maybe do some other things …
The non-sucky version of the code above is
Select some stuff, joining on all the things you need for the rows because databases are great
For each row in stuff:
… do some important things …
… maybe do some other things …Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated.
Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18 and forgot that means I need to rm rf the standby and pg_basebackup to rebuild... That wasn't fun.
Your code should handle the data model and never allow bad states to enter the database.
There's too much performance loss and too many footguns from these "features".
jwsteigerwalt•6h ago