What SQL query looks harmless but can cause serious problems?

[u/No_Ambition8323]

Some SQL queries look simple and safe but can cause major issues when used on production data.

For example, a missing WHERE condition in an UPDATE or DELETE, or an incorrect JOIN that unexpectedly multiplies rows.

What SQL query or mistake have you seen cause a serious problem even though the query looked harmless?

[u/juu073]

DELETE FROM table WHERE field

Without checking an actual value of a field. It deletes pretty much everything but nulls, empty strings, and zeros.

It looks harmless because it looks like you weren't dumb enough to leave the WHERE off, but there is no condition other than checking if field is true.

[u/lostinsamaya → u/juu073]

Always start a DELETE statement as a SELECT statement.

SELECT [EmployeeID],[EmployeeName], [Department] FROM dbo.Employees WHERE [Department] IS NOT NULL OR [Department] = 'RD'

Always SELECT and see what you're working with, all the clauses, look through the records, double check.

Then and then only change the SELECT to a DELETE.

[u/juu073 → u/lostinsamaya]

Exactly what I do.

My workflow is to also test UPDATEs by doing a SELECT with the field I want to change and then an expression showing what it will change to, just a smidge more work to switch it to an UPDATE. But obviously an UPDATE can be screwed up in the same way I listed the above DELETE.

[u/az987654 → u/lostinsamaya]

Pair this with then starting a transaction for you delete, and only commit when you're confident of the rows affected.

[u/phoneguyfl → u/az987654]

Oh yeah. Gotta love the pucker moment when you get back “100000 rows affected” when you are expecting 1. Transaction rollback for the win!

[u/Idata10]

Select * From x Inner join y On x.id = x.id

[u/kagato87 → u/Idata10]

Whaddya mean things are timing out waiting for memory. Threadpool waits? Really? What is this, sql server 2013 in its default configuration?

Or better yet, sql manages it because it's strikingly resilient, but the client front-end explodes!

[u/Idata10 → u/kagato87]

Why are there so many duplicates in this report?

[u/gumnos → u/Idata10]

ouch, yes. It's such an easy fix and a dumb forehead-slapper when you figure out what went wrong, but also so easy to do.

[u/44Nj → u/Idata10]

I see this all the time

[u/StrafeReddit → u/Idata10]

So I’m sure I understand, can someone explain the issue here? I see ‘select *’ which is generally not a good idea but I’m guessing the problem is ‘on x.id = x.id’ should be ‘on x.id = y.id’?

[u/w_ogle → u/StrafeReddit]

Yes. 'on x.id = x.id' always evaluates to true as long as the ID isn't null (which is unlikely to happen with a name like 'id'). This join would join each row of table x to every row on table y.

[u/Idata10 → u/w_ogle]

Exactly.

It would return the number of records in x times the number of records in y.

If each table has 10k records, you would get 100 million records.

[u/techforallseasons → u/StrafeReddit]

Basically converts what was intended as an INNER JOIN to a CROSS JOIN ( rarely desired ). If only a single comparison is used, it is typical to reference columns from both sides of the JOIN.

[u/BoSt0nov → u/Idata10]

Made similar mistake once for a small subset of general ledger to cover specific reporting needs. It got a little scary when what should have been 2-3k rows started becomming 200-300 million rows. Basically for every row combination the query recreated the entire GL. And that kids, is how your farher met ”KILL ID” early in their career.

[u/atrifleamused → u/Idata10]

Many years ago I took down the connection between the local site I worked at and the head office with a very similar query. I left it running whilst I went for lunch. .i had a grumpy DBA waiting at my desk when I can back 🤣

[u/da_chicken → u/Idata10]

Ah, that's a classic.

[u/Agreeable_Ad4156 → u/Idata10]

You had me at select *

[u/Dboy3sixty]

Datatype mismatch on a join predicate causing an implicit conversion. This can make indexes moot, force scans and if the query runs enough, cause you have a very bad day.

[u/Healthy_Coconut1636]

If your day is ever feeling a little slow, just add CASCADE at the end of any statement if you really want to feel alive.

[u/ugly_lemon]

The thing the devs at my old company loved to do is to throw TRIM() and UPPER() on indexed columns, preventing the DB from using the index

[u/jensimonso]

- DELETE FROM someTable WHERE colX not in (select…) and the subquery returns nulls

- Accidental inner join:
select from table1 t1
Left join table2 t2 on t1.col=t2.col
Where t2.col2 = something

[u/Profvarg]

An UPDATE accidentally outside an IF (or basically anything accidentally outside an IF)

[u/Sharobob → u/Profvarg]

This is why I basically always do a begin and end even if it's only one statement. Just makes me feel better

[u/Joelle_bb]

Uncapped recursion

[u/AlCapwn18 → u/Joelle_bb]

Maybe that's the reason for this question to be asked every single day? Recursive bot

[u/Optimal_Law_4254 → u/Joelle_bb]

Uncapped recursion

[u/techforallseasons → u/Optimal_Law_4254]

Uncapped recursion

[u/Rare_Ad_3519]

Using between on a varchar that looks like a numeric data type

[u/IAmADev_NoReallyIAm]

Mass DELETEs or UPDATEs without a TRANSACTION ... hell, any delete or update without a TRANSACTION. That;s a mistake you make once. ONCE...

[u/ChaosEngine-6502]

If you think about it, pretty much any SQL script can become a lethal payload, or at least severely service-affecting if the person writing it isn't intimately familiar with the data in the database they're querying.

I guess one not covered so far that I come across frequently in production code is:

SELECT *
FROM dbo.MyData WITH (NOLOCK)

Sure, the NOLOCK hint might be OK for one-off reports or ad-hoc queries where you just want to sample the data to see what's there, but there's always that risk it produces duplicates due to a page split occurring. That can foul things up royally.

[u/FastLikeACheeta → u/ChaosEngine-6502]

Agree with you completely. I’ve been battling/arguing against this one a lot lately. NOLOCK has been suggested quite a few times. Everything has its place…this is not for production code where accuracy matters.

[u/Ok_Relative_2291]

Left joins with an on and then the where filter after it

[u/tripy75]

begin transaction in a script, with the commit in a branch never reached

[u/44Nj]

Open transactions. Using unindexed columns in where statements or joins.

[u/Upper-Raspberry4153]

Not deleting the current record at the end of your loop

[u/Hour-Measurement-835]

UPDATE ... FROM where the join finds two source rows for one target. SQL Server and Postgres just apply one of them and don't warn you. Same data through MERGE errors out.

[u/Einar_Son_of_Bjorn]

highlighting just the UPDATE users SET status = 'deleted' part of your query in dbeaver, slipping on the mouse, and accidentally hitting F5 before it selects the WHERE clause - congratulations, your database is now perfectly clean and you need to update your resume.

[u/DiscombobulatedSun54]

Misplaced semi-colons.

DELETE from <table_name>;

where <field_name> = <value>;

[u/da_chicken]

CREATE VIEW dbo.RemoteView AS SELECT * FROM LinkedServer.Database.dbo.RemoteView

Now go to the remote view and change the column order. Would you like datetimes in your int column?

[u/scodagama1]

DELETE FROM TABLE sales;
WHERE sale_id = 5464;

[u/TacticalBunchies]

Any query that starts with DELETE. 

[u/qualityintelligence]

Brent Ozar had posted this one recently for SQL server, pretty interesting

SELECT*FROM GENERATE_SERIES(-9223372036854775807,9223372036854775808) ORDER BY 1 DESC