Just learned the hard way a piece of MySQL sytax - why does this work and what does it mean to do?

Question

I'm using MySQL 5.6. The internal app at my company works with SET SQL_SAFE_UPDATES = 0;(I know). I've brought this up before but was told to just let it be.

Today, I accidentally wrote and ran this DELETE FROM table WHERE id - 11235, with a - instead of = and lo and behold all the rows deleted. I understand delete all rows is allowable with the safe updates off, but why didn't this throw some syntax error? What would id - 11235 even mean in SQL?

Answer 1

This works in MySQL because MySQL is non-standard with respect to SQL boolean expressions.

Boolean expressions must evaluate to true or false. But in MySQL, false is simply the integer 0, true is the integer 1, and any other integer other than 0 is also treated as true.

So it is strange, and non compliant with ANSI SQL, but any arithmetic expression can also be used as a boolean expression, and vice-versa.

This allows us to do neat tricks like this:

SELECT SUM( name = 'Harry' ) FROM mytable

The boolean expression results in integer values, either 0 or 1, depending on the string comparison. Summing these effectively returns a count of the rows where the expression is true, the same as if we had run:

SELECT COUNT(*) FROM mytable WHERE name = 'Harry'

But the SUM trick allows us to "count" multiple conditions with a single pass over the table:

SELECT SUM(name = 'Harry'), SUM(name = 'Ron'), SUM(name = 'Hermione') FROM mytable

You can't do that with COUNT(*) and a WHERE clause.

Again, this works only in MySQL, because MySQL makes booleans and integers the same. It doesn't work in other implementations of SQL.

In your query, id - 11235 is 0 on a row where id = 11235, and it's nonzero on every other row. The rows where it is nonzero are treated as true, so the delete is applied.

This is unfortunate at least in your case, because a typo results in deleting all your data.

Answer 2

You have a DELETE-statement with a condition (WHERE). If the condition is true for the row, the delete operation is being done.

The condition you have is id - 11235. Every row where this condition doesn’t produce false/0 is considered true. So, the command will delete all the rows but id 11235 (11235-11235 = 0).