MySql issues

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.



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.



  1. Karl says:

    There are too many bugs in the universe =O

    You should isolate it and look it up on http://bugs.mysql.com/ ?
    I would try af3_plants.{plant_id,species} ?

    Also “whatsit – a feature?” made me laugh out loud.

  2. Shaun says:

    You guys are heroes at finding bugs.

  3. phattie says:

    Personally, I don’t mind these type of bugs. These type of bugs come about when you are doing wierd things… things nobody else thought of to do.

    I think the real bug here Brad, is that you’re not thinking like everyone else. Conform or be hosed (don’t do subselect IFNULL wierdness!!!)!

    Anyway, what is going on around here?!? there are postings on a regular basis!! and wordpress??

  4. Brad says:

    Haha do you ever find yourself attempting to accomplish some task with a single giant query, for no real reason other than to see if you can. At that point your are basically making your own challenges :S

    Slash: Phattie is back :O I thought you’d fallen off the internet!

  5. Brad says:

    Also, I was trying to see how long I could go @ updating the GC every day (i guessed three days) – and then Karl and Shaun decided to help! 😀
    Also tbh it is way more fun for me to read their blogs than update, so everybody (by which i mean myself) wins!

    GC6 was wordpress, but that guy had a custom skin – gc7 doesn’t (yet?) 🙁

  6. phattie says:

    Ah, well that’s cool. I like reading all the posts too, but I got no time lately and there are tons of posts now! =/

    Anyway, it wasn’t the wife to blame for my absentness.. not fully anyway. I’m not on gtalk right now because of my job. They won’t let me use IM. It’s wierd here; they don’t trust their employees at all (no offense guys; just sayin’! [they are no doubt reading this right now]). In fact I have a camera pointed right at me this very moment so they can detect when I fall asleep, or steal this awesome standard dell keyboard. Naw, admittedly I do have three Dell widescreen monitors I’d steal first (just kidding guys, I’m not going to steal anything!). Speaking of stealing. Go STEELERS! (superbowl this weekend, and I am pretending to be into sports so I can be excited about it)

    Anyway part2, I’m not online at home because I don’t get much computer time lately, and if I do it’s usually to work on stuff for the old employer. Outside of that, the wife does demand some time from me, so I don’t feel guilty blaming her.

    So nothing against wordpress; in fact, I use it on my “family” blog. Writing your own blog is a waste of time unless you intend to learn a new language in the process of it. I’ll probably turn my site into a project-site; just somewhere I can brag and show off my stuff I never finish. Or I’ll take the plunge into python, and rewrite it. Now that I jquery everything, I can make something pretty sweet

    To get back on point; I admit to making super queries to do all the work. But I am now so deeply converted to the concept of the “database is sacred” concept, that I no longer write any SQL that might compromise speed or SQL threads. I’m affraid to even do joins now. 🙁

You must be logged in to post a comment.