I am going to make the claim that MySQL’s where_condition differs between SELECT statements and DELETE statements. And I’m going to make it right now.

There. Done.

Thoughts?

Rebuttal?

I guess I should present some flimsy evidence to back up such a bold claim.

It started with a bug report. Investigating the issue led me to believe the problem lay with the query being sent to MySQL. Its purpose was to allow the user to delete a plant if, and only if, there were no instances of that plant currently within the inventory.

Oh, by the way this was for a thing about plants.

The query went a little something – exactly – like this:

DELETE FROM af3_plants as p WHERE p.plant_id = 12 AND IFNULL((SELECT SUM(i.number) as available from af3_inventory as i WHERE i.species = p.species GROUP BY i.species), 0) = 0

12 is the id of the plant we’re trying to delete – PHP put that guy in when he initially constructed the query.

So, what we’re saying here is delete plant 12, only if the sum of all inventory fragments for the species is 0. If there are no inventory fragments, the subquery returns null, which is why we have the IFNULL whose second expression is the literal 0.

Looks like it should work? I thought so, but that’s where MySQL and I disagreed. He said:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE p.plant_id = 12 AND IFNULL((SELECT SUM(i.number) as available from af3_inv’ at line 1

And he said it with anger in his voice.

PHPMyAdmin put the little X marker next to p.plant_id in the subquery, which may be coincidence since it doesn’t appear in the error quote itself. But read on, and decide for yourself.

I changed it from a DELETE to a SELECT query:

SELECT * FROM af3_plants as p WHERE p.plant_id = 12 AND IFNULL((SELECT SUM(i.number) as available from af3_inventory as i WHERE i.species = p.species GROUP BY i.species), 0) = 0

That guy worked fine; he returned exactly what I was expecting.

I’ll admit it — I got a little daring. I removed the af3_plants as p alias, to see what would happen – suspecting the same result.

It was not to be. The new query returned multiple rows, leading me to believe the WHERE condition i.species = species was referencing the same species, and thus always evaluating as TRUE.

It certainly caused me a lot of frustration or enjoyment. We may have found a bug, or I might have done something horribly wrong. It might even be a whatsit – a feature?

In my opinion, a where_condition is a where_condition is a where_condition. If they differ, maybe call one where_condition_THIS_ONE_IS_INEXPLICABLY_DIFFERENT.

There may be a reason for this behaviour, but it will have to be a super good one before I buy it.

In conclusion, I broke the query in two. I decided to fix it, rather than fight it.