I stumbled upon a small (but zesty!) python WTF today, thought I would share.

First a little background. This is Python running with SQLite as a DB. Therefore we should all know this (which the programmer in question here obviously did not understand): 

http://www.sqlite.org/autoinc.html:

In SQLite, every row of every table has an integer ROWID. The ROWID for each row is unique among all rows in the same table.

 Entire function for retrieval:

So, we have made a query using a WHERE clause on a unique column. We have then limited the query to 1 result (???). And then on top of that, we fetchone() to make triple sure that we only return one row. Brilliant!

On top of this, we don't bother to close the cursor... 

I am not sure what the programmer was thinking here, but I guess they figure that just in case that unique row suddenly returns multiple results and the LIMIT statement fails, this application will be rock solid!

For extra zestiness, note the abundance of semi colons... In python!

The best part is that this is a common theme throughout the DB code.

 This one doesn't seem so bad. My SQLite experience consists of about an hour of fiddling with it before realizing that it didn't fit my needs for anything I was working on, but one of the systems I have to maintain consists of a medium-sized, horribly-designed MySQL database and a Java component that somehow managed to hose up the tables enough to enter supposedly-unique keys multiple times.

The sad part is, one of the changes I made while tracking down the problem was to force a function to do the following:

1. "SELECT `blah blah blah` FROM hosedtable WHERE uniquekey = 'blah'"

2.  if (returned_row_count > 1){ email.send("Table is hosed again");}

...and if I had left line #2 in there I'm pretty sure I would eventually see that code on TDWTF too. Anyway, the point here is that I understand being paranoid about it, as in my experience, "unique" keys aren't always so, and it's easy to get in the habit of writing code that says something to the effect of, "...and please, for crying out loud, give me only ONE GOD DAMN ROW!"

durendal.mk3:

2.  if (returned_row_count > 1){ email.send("Table is hosed again");}

...and if I had left line #2 in there I'm pretty sure I would eventually see that code on TDWTF too.

I'm not so sure.  To another party your code will obviously be catering for perceived failings in the software and actively informing someone of the problem.  MPS's example (if it is indeed for the same reasons) just tries to brush the problem under the carpet and get a (possibly spurious) result anyway?  Arguable.

Using fetchone is not a wtf, it returns a single row instead of a list.

The limit statement is unnecessary, but just a minor mistake. Cursor is made global, so it shouldn't be closed (it being global is a bit nasty though). The semicolons are quite strange though, seems the writer was not familiar with python. 

durendal.mk3:

 This one doesn't seem so bad. My SQLite experience consists of about an hour of fiddling with it before realizing that it didn't fit my needs for anything I was working on, but one of the systems I have to maintain consists of a medium-sized, horribly-designed MySQL database and a Java component that somehow managed to hose up the tables enough to enter supposedly-unique keys multiple times.

The Real WTF on this example would be that even if it was possible to have duplicate rowId's, the overworked checking would not alert the user that such a case exists!

danixdefcon5:

durendal.mk3:

 This one doesn't seem so bad. My SQLite experience consists of about an hour of fiddling with it before realizing that it didn't fit my needs for anything I was working on, but one of the systems I have to maintain consists of a medium-sized, horribly-designed MySQL database and a Java component that somehow managed to hose up the tables enough to enter supposedly-unique keys multiple times.

Was this a distributed DBMS system? If so, I'd understand the possibility of key duplications. If not, then I'd point out the "horribly-designed" WTF as setting either a UNIQUE constraint or actually using the key as a PRIMARY KEY would avoid the duplicates.

The Real WTF on this example would be that even if it was possible to have duplicate rowId's, the overworked checking would not alert the user that such a case exists!

Renan "C#" Sousa:

Even for distributed systems I think this would still be a WTF. I'm quite sure modern DBMS's can be set up to avoid key replication in distributed environments.

Yes, but the beauty of this thread so far are all the people arguing against trusting a unique rowid.

TRWTF always emerges.

danixdefcon5:

Was this a distributed DBMS system?

No, it was all on one server, and replicated up to a backup server for disaster recovery purposes. To this day, I still don't know how duplicates made it in, but upgrading from MySQL version 3 to MySQL version 5, and making an attempt at normalization solved the problem. Even if I had an unreasonable hatred of MySQL's replication feature for a long time after (having to restart replication multiple times a day because it keeps breaking with a "Duplicate key" error tends to grate on your nerves after a while).

danixdefcon5:

The Real WTF on this example would be that even if it was possible to have duplicate rowId's, the overworked checking would not alert the user that such a case exists!

Agreed; I doubt there is there a situation where a database would have duplicate keys and it wouldn't be a critical issue.

MasterPlanSoftware:

Yes, but the beauty of this thread so far are all the people arguing against trusting a unique rowid.

I'd be surprised if a thread managed to stay on topic for longer than one post.

durendal.mk3:

I'd be surprised if a thread managed to stay on topic for longer than one post.

Not sure how this had anything to do with what you quoted, but this thread is still on topic so far. 

Not sure what you are hoping to achieve here, but you and soviut seem to be the only trying to derail anything.

MasterPlanSoftware:

We have then limited the query to 1 result (???).

 There have been databases where an explicit limit can be used as an optimization technique. Granted, this hasn't been true for things with a unique constraint in ages in any real Database, but perhaps this developer is used to a system so stupid? Or they are in the habit of doing a LIMIT 1 whenever they know they want one row instead of thinking "can the database reasonably optimize this?"

 It doesn't seem to be a horrible habit of getting into -- much better than the alternative. Would you rather that the developer forget to limit 1 if doing something like sorting by date, then only grabbing one row?

 .fetchone() is not a wtf for the reasons outlined above.

So, really, the only real WTF is a global cursor, with a few lower-cased wtfs that don't deserve to have been mentioned except for possibly barely, under your breath, as you "fixed" it and moved one quietly.

MasterPlanSoftware:

Not sure what you are hoping to achieve here

Pointing out the fact that your original post isn't a WTF, not for the reason you presented. While one wouldn't expect a DBMS to allow duplicate keys into a table, it can happen, and this is a case where such paranoia would be justified. As pointed out, TRWTF is that in such a situation the code you presented picks a row and shows it, instead of falling back to some sort of an error--the programmer foresaw a situation where database integrity could fail and thought the acceptable solution would be to silently pick something at random when the chances of choosing correctly are at best 50%.

You evidently seem to think the best course of action in that situation is to just use the first row returned (the same mistake made by the original programmer). Either that, or you trust your DBMS too much and think there's no way you can have duplicate keys, ever. 

The best course of action would be to at least log an error or send an email, rollback in case you have other queries in that transaction that depend on this one working (and you are using SQLite's transactional features), and throw an exception (not necessarily in that order).

MasterPlanSoftware:

but you and soviut

I'm not entirely clear on what my and soviut's posts have in common.

durendal.mk3:

Pointing out the fact that your original post isn't a WTF, not for the reason you presented. While one wouldn't expect a DBMS to allow duplicate keys into a table, it can happen, and this is a case where such paranoia would be justified. As pointed out, TRWTF is that in such a situation the code you presented picks a row and shows it, instead of falling back to some sort of an error--the programmer foresaw a situation where database integrity could fail and thought the acceptable solution would be to silently pick something at random when the chances of choosing correctly are at best 50%.

durendal.mk3:

You evidently seem to think the best course of action in that situation is to just use the first row returned

It can only return 1 row, with or without the LIMIT 1. Arguing anything different makes you stupid.

durendal.mk3:

Either that, or you trust your DBMS too much and think there's no way you can have duplicate keys, ever. 

Yes, I trust my DBMS to maintain the RowID as unique. The fact you are arguing against this is amazing, and makes you way stupider than the idiot who wrote the code in my OP.

durendal.mk3:

The best course of action would be to at least log an error or send an email,

And how would you recommend that happens with the code in the OP? Using LIMIT 1 is certainly not going to help you achieve that goal!

durendal.mk3:

I'm not entirely clear on what my and soviut's posts have in common.

You are both trying to troll. Especially your post. No one can be stupid enough to really think that RowID is going to spontaneously not be unique. And I cannot believe that their solution to this would be to think LIMIT 1 would be a valid solution to this.

bstorer:

So, no, it isn't possible to have duplicate keys.

It surely shouldn't be. And I've never seen it happen personally. I'm looking at this from my standpoint and assuming that the DBMS does its job. Others have said that they have seen some version of an unspecified (I think) DBMS fail at this. Assuming they they are not lying (and I have no reason to assume this), this is one possible reason the developer might have written the above code in the way that they did. And yes, this leaves some WTFs (strange failure mode for an anticipated failure case) which others have been pointing out.

negativeview:

It surely shouldn't be. And I've never seen it happen personally. I'm looking at this from my standpoint and assuming that the DBMS does its job. Others have said that they have seen some version of an unspecified (I think) DBMS fail at this. Assuming they they are not lying (and I have no reason to assume this), this is one possible reason the developer might have written the above code in the way that they did. And yes, this leaves some WTFs (strange failure mode for an anticipated failure case) which others have been pointing out.

Even if you could argue that it could return duplicate rows (it cannot) the fetchone() would solve this issue. Also, this would be the worst way to get around this issue if you were truly trying to write defensively. It would specifically just ignore the issue and 'guess' by returning the first duplicate.

This is a very stupid thing to do, and it is even stupider that people would actually argue for this.

negativeview:

Assuming they they are not lying (and I have no reason to assume this), this is one possible reason the developer might have written the above code in the way that they did.

MasterPlanSoftware:

Even if you could argue that it could return duplicate rows (it cannot) the fetchone() would solve this issue. Also, this would be the worst way to get around this issue if you were truly trying to write defensively. It would specifically just ignore the issue and 'guess' by returning the first duplicate.

This is a very stupid thing to do, and it is even stupider that people would actually argue for this.

MPS, where did I argue for this at all? Where did anyone say it was a GOOD idea? They have simply debated possible thought processes leading to this code. Even the person saying that their DBMS has screwed up the ids admits that it was a short-lived problem that they don't know how to replicate, merely offering it as a possible reason that the original developer was that paranoid about What Cannot Happen. I have said three things:

1)  It may have been an attempt at optimization, as I've had to do similar (though not with a unique constraint) things for that reason in the past.

2) Using LIMIT 1 reflexively when you only want one row is better than not using it, fetching one row, then moving on.

3) People giving justifications do not necessarily believe that these "reasons" provide more benefit than downfall. They're not necessarily saying "the original code is BRILLIANT! WHY ARE YOU QUESTIONING IT?!" They're simply saying "this might be the thought process of the original developer..."

I also find it very telling that you're already pulling out both "troll" and "stupid." This post was less WTF-worthy than 90% of the ones that I see you criticize for the same reason. I mean, what is the side-effect of this? Crippling performance? Completely unmaintainable?

No. The function does its job perfectly. Its big WTF moment is that it makes the sql server do a little more work at parsing a bit that should be optimized out. It even uses parameterized queries. WTF-worthy this is not, no matter how many times you call people stupid or accusing others of trolling.

bstorer:

negativeview:

Assuming they they are not lying (and I have no reason to assume this), this is one possible reason the developer might have written the above code in the way that they did.

If you can't trust the database to meet its own invariants, why are you using it? This isn't defensive programming, where you check for some obscure error condition that should never happen but could, this is checking for things it says aren't possible. At that point, shouldn't you be checking that fetchone returns only one record? And then that the data hasn't disappeared since you called fetchone? Where do you stop checking for the impossible?

My God, can people not understand that hypothesizing about a thought process does not mean that I believe it?

 I got it, the developer wrote it that way because Xenu told him to. Now I'm a scientologist. I admit that some people believe in Xenu, therefore, I must as well.

negativeview:

1)  It may have been an attempt at optimization, as I've had to do similar (though not with a unique constraint) things for that reason in the past.

It is performing a query on a unique column. There is no more optimization than that.

negativeview:

2) Using LIMIT 1 reflexively when you only want one row is better than not using it, fetching one row, then moving on.

Using something reflexively is not an excuse. Ever. The unique portion of this query is verified in triplicate. It is just stupid.

negativeview:

They're simply saying "this might be the thought process of the original developer..."

Actually, saying this isn't a WTF is surprisingly stupid. And that is what I am calling you on.

negativeview:

I also find it very telling that you're already pulling out both "troll" and "stupid."

Again, the point of view that this isn't a WTF is stupid. Also, the two posters I called trolls, were in fact trolling in the posts I called them on.

negativeview:

This post was less WTF-worthy than 90% of the ones that I see you criticize for the same reason. I mean, what is the side-effect of this? Crippling performance? Completely unmaintainable?

It is a WTF because it verifies THREE times that there will be one row returned. It is stupid, and it shows an amazing amount of ignorance in the developer for not trusting the RowID to be unique in the first place.

This cannot even be compared with the lolcat crap that has been posted recently.

It is a small WTF, but I admitted that in my first sentence in my OP. It is still a WTF though, and has spawned a great reaction of stupid people defending this kind of behavior. That is what our forum is all about. so this thread is actually going perfectly.

negativeview:

No. The function does its job perfectly.

It can 'do it's job' just fine, but when you look at the code and think about what it is doing, if you don't say "WTF?" to yourself then you should be writing code or working with databases. And that is the definition of the sidebar.

MasterPlanSoftware:

It is performing a query on a unique column. There is no more optimization than that.

I agree that it should be that way. Though unable to find it at the moment (and thus I shall drop this line of thinking), I am convinced that there was a release of a major DB that failed to optimize this away, however. Rather a bug or a known limitation of the time, I am convinced that it not only existed, but I used that release.

MasterPlanSoftware:

negativeview:

2) Using LIMIT 1 reflexively when you only want one row is better than not using it, fetching one row, then moving on.

Using something reflexively is not an excuse. Ever. The unique portion of this query is verified in triplicate. It is just stupid.

Tell that to everyone that claims that you should always always always check the return value of all system calls. Doing things that give Java a bad name like catching, then throwing an exception that winds up bubbling up to the user is stupid and is a logical side-effect of this message being preached so loudly for so long, but it's still a good lesson. There are some things that you wind up doing by reflex because it's always a good idea. Adding LIMIT 1 to a query when you only want one row is NOT horrible to put on that list.

MasterPlanSoftware:

negativeview:

They're simply saying "this might be the thought process of the original developer..."

Actually, saying this isn't a WTF is surprisingly stupid. And that is what I am calling you on.

Your rebuttal seems to not be at all related to what I said above. In that quote I don't even hint that it's not a WTF, let alone outright say it. So if I were a less mature person, I'd probably call YOU stupid right now for THAT.

MasterPlanSoftware:

negativeview:

I also find it very telling that you're already pulling out both "troll" and "stupid."

Again, the point of view that this isn't a WTF is stupid. Also, the two posters I called trolls, were in fact trolling in the posts I called them on.

Nobody said that this wasn't a lower-case wtf. It's simply not tdwtf worthy. It's surely not worth calling anyone stupid over, and is less of a WTF than half the posts you run around yelling at people for posting on here that don't meet your strangely-defined standards.

MasterPlanSoftware:

negativeview:

This post was less WTF-worthy than 90% of the ones that I see you criticize for the same reason. I mean, what is the side-effect of this? Crippling performance? Completely unmaintainable?

It is a WTF because it verifies THREE times that there will be one row returned. It is stupid, and it shows an amazing amount of ignorance in the developer for not trusting the RowID to be unique in the first place.

 You're counting the use of .getone() as verifying that it only returns one row? When cur is global. Granted, it would be a wtf to call that function then call cur.getone() again, but the use of global variables means that getone doesn't actually verify anything about the query (the function, yes). You're down to two. The fact that rowid is a magical row in sqlite is hardly universal knowledge. I didn't know that off the top of my head, with years of experience in closely related fields (mysql, postgres, a little oracle, etc). I should be able to tell by the name of the row, sure, but I didn't _know_ off the top of my head. LIMIT 1 is in this case redundant (getone() is not, quit arguing that) but adds a trivial amount of overhead and might make the intent of the code a little more clear for someone coming from a much more common sql solution.

 I'll agree with you that this solution is less than optimal. I wouldn't have put a LIMIT 1 on there. I'm looking at roughly equivilent code here that doesn't have a LIMIT 1. But this sort of griping seems akin to someone posting on here saying that the entire WTF is a lack of indentation. Or that the entire wtf is that a single variable is named 'foo'.

MasterPlanSoftware:

This cannot even be compared with the lolcat crap that has been posted recently.

Maybe I don't spend all day on here, but I haven't seen any LOLCat stuff posted. I've seen a few things that don't qualify as a WTF. I've seen some things that are mild WTFs, and I've seen a few amusing things. The thing they all had in common was that you would jump in and bash someone in that thread for something. If not for the mildness of their WTF, then for quoting habits or their perceived level of intelligence. I normally wouldn't jump in and bash people (look at my post history!, I ignored you when you tried to bash me for my first post here, having already read long enough to know that you would bash me no matter what I said) but the level of your own hypocriticalness is astounding.

MasterPlanSoftware:

It is a small WTF, but I admitted that in my first sentence in my OP. It is still a WTF though, and has spawned a great reaction of stupid people defending this kind of behavior. That is what our forum is all about. so this thread is actually going perfectly.

NOBODY has said that this is good behavior. NOBODY has defended this in as so many words. All we have said is that you're being silly for posting this in a forum for deriding actual problematic code.

MasterPlanSoftware:

 

negativeview:

No. The function does its job perfectly.

It can 'do it's job' just fine, but when you look at the code and think about what it is doing, if you don't say "WTF?" to yourself then you should be writing code or working with databases. And that is the definition of the sidebar.

I don't say WTF. I would quietly replace it. Or, to dredge up the very first thing that you ever said to me, when I posted my first WTF (much bigger than yours, I might add):

MasterPlanSoftware:

If you could have implemented this in 5 lines, then what is the big issue? Rewrite it in 5 lines, pretend it was a huge problem, charge for time, and move on with your life. Sounds like an easy project to me!

Making the code only check for things once seems like an easy project to me! Charge for your time (is THAT why you're on this site constantly?) and move on with your life.

negativeview:

There are some things that you wind up doing by reflex because it's always a good idea. Adding LIMIT 1 to a query when you only want one row is NOT horrible to put on that list.

Verifying that a unique value is unique 3 times is stupid. Sorry, but you are wrong. It goes against all good logic no matter how you (lamely) try and justify it.

negativeview:

It's surely not worth calling anyone stupid over,

It sure is.

negativeview:

and is less of a WTF than half