SQL Anti-Patterns

Still waiting for the definitive article on how using the term anti-pattern is an anti-pattern.

https://pragprog.com/titles/bksqla/sql-antipatterns/ There's an actual book on them that had me nodding along the entire time.

"Dimension table" is the name for lookup tables in a star or snowflake schema.

but sometimes large case statements cant be turned into a simple dimension table/lookup table because it's not a simple key-value transformation.

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.

I usually use generated columns for this. It still uses CASE WHEN but it is obvious to all consumers of the table that it exists.

[and a third] OR use a case-insensitive collation for the name column.

I think they reversed the 2 expressions. You should use “WHERE UPPER(name) = ‘ABC’” if you want to use the index.

The given solution (create an indexed UPPER(name) column) is not the best way to solve this, at least not on MS SQL Server. Not sure if this is equally supported in other databases, but the better solution is to create a case-insensitive computed column:

  ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;

(season to taste)

The blog has a typo. The first line needs to have the text in uppercase:

> 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.

"Unfortunately I learned this the hard way!" ... Seems to be the motto of SQL developers.

Otoh, it seems a fairly stable language (family of dialects?) so finding the pitfalls has long leverage

Some well known docs on the topic- https://use-the-index-luke.com/sql/where-clause/obfuscation

And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?

That’s almost always my experience too.

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.

Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code.

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.

  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);

  SELECT DISTINCT * FROM t1 JOIN t2 USING (x);

  SELECT * FROM t1 JOIN t2 USING (x) GROUP BY t1.*;

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

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?)

I'd be wary of overgeneralizing on that. I guess it depends on whose queries you're usually reading.

Set theory...

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.

    bool x;

    ...

    if (x == true) {
        DoThing1();
    } else if (x == false) {
        DoThing2();
    }

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`.

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.

It's the exact opposite in Cypher. I'm currently working with some complex data in neo4j, and wondered why my perfectly fine looking queries were so slow, until I remembered to use DISTINCT. It's very easy to get duplicate nodes in your results, especially when you use variable length relationships, and DISTINCT is the only fix I'm aware of that fixes that.

SQL is somehow "ask two people, get three different opinions" for something as basic as:

"given a BTreeMap<String, Vec<String>>, how do I do .keys() and .len()".

In my experience, its nearly as often a problem with the design of the database as the query author.

Or just doesn't know how to do semijoins in SQL, since they don't follow the same syntax as normal joins for whatever historical reason.

Or believe more in Codd’s relational model than SQL’s tabulational model.

Or maybe they’re on OLAP not OLTP.

So how do you "know" when you can safely omit DISTINCT for your shiny new query SELECT x FROM t ?

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.

I'm not sure I understand the part about set theory. If anything, a valid use of DISTINCT is if you want the result to be (closer to) a set, as otherwise (to your point, depending on the data model) you may get a bag instead.

In fact, IIRC, using DISTINCT (usually bad for performance, btw) is an SQL advice by CJ Date in https://www.oreilly.com/library/view/sql-and-relational/9781...

If you have select * in your code, there already is something wrong with your code, whether it breaks or not: the performance and possibly output of your code is now dependent on the table definition. I'm pretty sure Rich Hickey has also talked about the importance of avoiding non-local dependencies and effects in your code.

I don't see anything wrong with what the article is saying. If you have a view over a join of A and B, and the view uses "select *", then what is gonna happen when A adds a column with the same name as a column in B?

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.

`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.

The reasoning is in the article, and true.

> 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.

This is not a pipeline in the control flow sense; the full query is compiled into a single processing statement, and the query compiler is free to remove and/or reorder any of the subqueries as it sees fit. The intermediate results during query execution (e.g. temp table spools) do not follow the structure of the original query, as CTEs and subqueries are not execution boundaries. It's more accurate to compare this to a C compiler that performs aggressive link-time optimization, including new rounds of copy elision, loop unrolling and dead code elimination.

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).

I wrote some tooling to help debug sql queries with many CTEs. It parses the sql, finds all the CTEs, and prints the result of each CTE formatted as csv. If the .sql file changes on disk, it reruns the query and tells you which CTEs’ output changed. Saved me hours in debugging.

> Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?

F# in the visual studio debugger does a pretty good job of this in recent versions.

It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with

    where a=1
      And k=2
      And v=3

> 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 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.

   AND <condition>

  WHERE <condition 1>

  WHERE true
  --    AND some_column = “some value”
      AND event = “SOME_EVENT_TYPE”
      AND EXISTS(SELECT * FROM UNNEST(array_column) as v WHERE v = “some value”)

Are we on bizarro HN?

No, you ask the DB to EXPLAIN itself to you.

I agree. Modern code models tend to do a great job advising in SQL, especially if you include the table definition and EXPLAIN output in the context. Alternatively, I've found that an EXPLAIN MCP tool works well.

Nice articles in there. Bookmarked.

Edit: it’s also actually a book!

> 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.

    table Contact { int Id; Nullable<string> email; Nullable<string> phoneNo; }

    type PhoneOrEmail = Phone<string> | Email<string>
    table Contact { int Id; PhoneOrEmail info; }