The Daily WTF: Curious Perversions in Information Technology

2008-03-05 Reply Admin

David,

plz email me teh codez

2008-03-05 Reply Admin

It looks to me like this is an example where an idea or mindset from programming data structures (the bit set or bit string) was badly migrated to a database.

The problem is that the context that a data structure lives in is different. In memory, there are no handy-dandy indexes unless you make them yourself. In a database, that framework is already mostly implemented, waiting for you to flip a switch.

Whoever designed the system was either working with terrifying legacy data and didn't have much of a choice...

... or they just didn't consider that a database is more than just a a form of abstraction for storing typed scalars, arrays and dictionary data structures.

2008-03-05 Reply Admin

How do you know $prodID wasn't already checked? How do you know it's even from an outside source which is where SQL injection comes from?

$prodID = intval($prodID);

Then the select.

2008-03-05 Reply Admin

I'll hire the programmer who finds solutions without redesigining the whole table(s), breaking other stuff, etc., before I hire the complainer.

2008-03-05 Reply Admin

Imran:
I'll hire the programmer who finds solutions without redesigining the whole table(s), breaking other stuff, etc., before I hire the complainer.

Yeah: the prices for faith healers are pretty reasonable nowadays.

Perhaps for an encore you'd hire the doctor who can fix your lung cancer without a hospital stay instead of the "surgery" or "chemo" whiny complainer ones, eh?

dextron · 2008-03-05 Reply Admin

It's just my opinion, but I think building SQL strings instead of using parameters aka bind variables can hamper system performance. In modern databases, SQL strings are parsed and then cached, this means when the exact string is encountered again, the already parsed version is used instead of reparsing the string. If you are substituting values into your string possibly each SQL statement has to be reparsed. Also, if you are using string or date variables, the construction of the sql string tends to look bad and is full of ...'"+str+"'... and stuff like that.

Raedwald · 2008-03-05 Reply Admin

Tiran Kenja:
The 'pretty simple' solution is to rework it into something that isn't so bloody awful.

These days, I only ever make trivial changes to programs. First I'll spend 95% of my time transforming the existing program until the change I want to make will be trivial.

2008-03-05 Reply Admin

real_aardvark:
this webcomic is a wtf:
Rootbeer:
<snipped -- but very pertinent/>
Heres a simplification of the database, it was an attendance tracking system. Now, for each instance of "attendance", I need the student id, the course id, the date of the meeting and the attendance. The student id in this case is 9 digits, the date is 8 digits, the class is 8 digits, and the attendance value could be as small as ONE BIT (but I went ahead and used one digit).
Now while you can optimize somewhat by reducing the size of the combined key, you can instead track attendance as a repeating group for a big win on storage space once your class size exceeds just a few students or meetings. (If you do allow repeating groups then the entire string of attendance values could be just one variable (array), and in that case it would be easily normalized. However, repeating groups (arrays) in a record throw some RDBMS weenies into fits!)

Disclaimer: I am not a DBA.
This, however, is a ludicrous piece of self-justification.

To be interested in the amount of disk space required, one of two things should apply:

(a) You can't afford an 80G disk or (b) Your application gets so many hits per second that locality problems ensue.

I doubt either applies, in your case. Or in any comparable case. But let's just give you the benefit of the (huge) doubt.

Your date fits into a BIGINT (strangely enough, it even fits into a DATE); your class fits into a BIGINT; even your student id (and I applaud any university or college that plans to have more than 4,000,000,000 students attend somewhere between foundation and having the plug pulled because of a moronic computer-based accounting system) can be fitted into a BIGINT using fairly simple compression techniques. Thanks for losing the seven bits, though -- that'll buy you two coffees and a double espresso latte.

Have you considered the other benefits of using an RDBMS (properly, that is?) The reason that repeating groups in a record throws some RDBMS weenies (also non-weenies, also ordinary programmers like me) is that it's just plain cretinous; no gain, much pain. I'd advise a simple ISAM database, but even that fails at the same hurdle.

Are you insane?

I don't know, I just don't think you're making a very good sale on this one. Sure, now 80 gigs seems like peanuts, but 10 years ago, costing out 80 gigs for a vax was a different story, not to mention that this was nowhere near the primary app for the vax it was installed on. Even now, I like to program for virtual servers, and I'm not going to buy storage for weenieisms unless its going to somehow win me something back.

What are you selling me here? Dude you have listed no advantages except for the privilege of pissing away storage space and losing the ease of coding that record editor.

Thats my biggest problem with RDBMS weenies. Even as I've seen in this thread, you guys argue for relational purism yet at the same time don't even know what you're talking about most of the time. Thats the real WTF here IMO!

Let me guess, if you were rewriting microsoft paint, you'd store the filename once for each pixel, right?

2008-03-05 Reply Admin

C_Boo:
How anonymized was this this example? I was given a site to maintain that had products/product lines available to agents, database "designed" by someone who created columns with names like AgentThreeAddressFive, and his initials were the same as a common abbreviation for a bodily function...

It's very anonymized, the conversation is the same, the entitiy names have been changed so have the developer names.

And there was more swearing invoved in the original.

2008-03-05 Reply Admin

sf:
Lighten up dick weed! All he did is post a reply with a working solution and a fair (even if naive) question.
Please, for the sake of all that's good in the world, find yourself a line of work far removed from other people.

No, he didn't post a working solution. Working solutions scale.
Your response to someone pointing out flaws in a horrible solution is to complain that he's mean - sorry pal, but you aren't an engineer - we play rough.

Imran:
I'll hire the programmer who finds solutions without redesigining the whole table(s), breaking other stuff, etc., before I hire the complainer.

God forbid someone point out the elephant in the living room. What have you got against someone who wants to actually be an engineer and not just hack things until they work (sort of)?

Raedwald · 2008-03-05 Reply Admin

regeya:
I don't work as a programmer by trade, but KenW and all the others screaming about this strike me as incredibly lucky, incredibly naive, or they've just never held down a real job.
I'm betting that emil knows the definition of "deadline" and knows to get a working solution first, THEN ask to have a meeting about refactoring

Nice troll.

deadline: if you work in a hospital and you fail to complete some actions by particular times, someone could die. That is a deadline. In most programming jobs, if you fail to complete a task by an arbitrary imposed date, nobody dies. That is just incompetent managers trying to crack the whip.

having a meeting about refactoring: if you need to hold a meeting, it isn't a refactoring, its a re-design and re-implementation.

2008-03-05 Reply Admin

[quote user="Raedwald] Nice troll.

deadline: if you work in a hospital and you fail to complete some actions by particular times, someone could die. That is a deadline. In most programming jobs, if you fail to complete a task by an arbitrary imposed date, nobody dies. That is just incompetent managers trying to crack the whip.

having a meeting about refactoring: if you need to hold a meeting, it isn't a refactoring, its a re-design and re-implementation.[/quote]

If your criteria is whether people die or not, why not just throw random data into the database and then go have a beer? Chances are nobody is going to die right?

2008-03-05 Reply Admin

Franz Kafka:
sf:
Lighten up dick weed! All he did is post a reply with a working solution and a fair (even if naive) question.
Please, for the sake of all that's good in the world, find yourself a line of work far removed from other people.

No, he didn't post a working solution. Working solutions scale.

Your response to someone pointing out flaws in a horrible solution is to complain that he's mean - sorry pal, but you aren't an engineer - we play rough.

Imran:
I'll hire the programmer who finds solutions without redesigining the whole table(s), breaking other stuff, etc., before I hire the complainer.

God forbid someone point out the elephant in the living room. What have you got against someone who wants to actually be an engineer and not just hack things until they work (sort of)?

Who says that a solution doesn't work if it doesn't scale? What works today is sufficient even if it doesn't work tomorrow if today is all you've got. Making decisions like that is a part of being an engineer (like me.)
Playing rough is not what engineers do, it's what assholes do. Are you saying that if you were in a design meeting and someone proposed something that you (in your opinion) thought was bad you would respond like that? Part of the job of most engineers is working effectively within a team. It's called being professional.

2008-03-05 Reply Admin

Zygo:
John:
Tell your colleague I think it is a idiot. Actually, give me his full name so I can add him to my list of morons to never work with.

Heh, now there's an idea for a web site...sort of like the opposite of LinkedIn.com.

The only thing is, I think I'd get too depressed by the number of idiots within 3 degrees of me.

Yeah, I had that idea about 8 months ago, but I couldn't think of a good name for the site. Basically the idea is to be registered on the site and not be on anyones 'moron' list.

2008-03-05 Reply Admin

sf:
Franz Kafka:
sf:
Lighten up dick weed! All he did is post a reply with a working solution and a fair (even if naive) question.
Please, for the sake of all that's good in the world, find yourself a line of work far removed from other people.

No, he didn't post a working solution. Working solutions scale.

Your response to someone pointing out flaws in a horrible solution is to complain that he's mean - sorry pal, but you aren't an engineer - we play rough.

Imran:
I'll hire the programmer who finds solutions without redesigining the whole table(s), breaking other stuff, etc., before I hire the complainer.

God forbid someone point out the elephant in the living room. What have you got against someone who wants to actually be an engineer and not just hack things until they work (sort of)?

Who says that a solution doesn't work if it doesn't scale? What works today is sufficient even if it doesn't work tomorrow if today is all you've got. Making decisions like that is a part of being an engineer (like me.)

Playing rough is not what engineers do, it's what assholes do. Are you saying that if you were in a design meeting and someone proposed something that you (in your opinion) thought was bad you would respond like that? Part of the job of most engineers is working effectively within a team. It's called being professional.

Today's trivial toy is tomorrow's enterprisey trainwreck. Better make it at least handle large data volume lest the management call you on the carpet because they dumped 12G of crap onto it and it died at a customer site.

No, I'd tone down the language, but i'd still call it terrible and all kinds of awful. In fact, I've done that a couple times, as I have a coworker that swings between gold plated crap and denormalized stuff like this.

2008-03-05 Reply Admin

Ilyak:
"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"
That's so PHPish - not knowing anything about parameter substitition and thus always introducing sql injection.
That's why nobody likes PHP, if not PHPist himself.

Who said there was never an int cast to $prodID when it was retrieved from the source input, moron. And its sample code just to show that it can be done on a conceptual level. I suppose you are one who thinks they are exempt from stupid mistakes because you picked up some .NET or Java? Glad to see your knowledge goes as far as to notice one of the most basic of vulnerabilities though.

2008-03-05 Reply Admin

@kenW:

Wow. Harsh much? It's one possible, simple solution. Not a terribly good one, but a simple one..

Eternal Density · 2008-03-05 Reply Admin

This is absolutely insane! Where's the mindbleach? CAPTCHA: gygax

real_aardvark · 2008-03-05 Reply Admin

this webcomic is a wtf:
Let me guess, if you were rewriting microsoft paint, you'd store the filename once for each pixel, right?

Of course: I'd write out a single record that consists of (pixel, filename) tuples, one after the other, probably in CSV format; although I might want to insert some metadata into the tuple to reflect the schema details. (This is an exercise in XML virtuosity that is best left to the reader.) That's the official weenie way to do it, isn't it? Way to go, straw man!

And I certainly wouldn't want to rewrite Microsoft Paint in any way that would make it a more useful application. No, I love data formats, me. Data formats, and blinking.

I'm not an RDBMS fan. In fact, I hate the stuff; I just have to deal with it, in no small part because it's a way of communicating with everybody else.

I am, however, not a fan of insanely over-optimised solutions that don't scale, don't make sense, and are clearly hacked up for personal amusement and for no other reason.

Call me Ishmael.

2008-03-05 Reply Admin

Come on WTF staffers... I can't believe you just... Why in god's green earth did you put a flame comment in as one of the 'best of comments' for this article? I thought you made that 'best of' section for the people who hated reading the comments because they wanted to avoid the rampant flaming and trolling going on in the comments section...

I'm really disappointed in you guys.

2008-03-05 Reply Admin

emurphy:
However, for other reasons (e.g. printing monthly statements), you should think about giving the orders/invoices/payments/etc. tables an Open/Closed status flag with an index on it.

Please notice that a Yes/No column is not selective. There is, in fact, a 50% chance to find the right value. An RDBMS will, therefore, not use an index and make a table scan instead. This is actually faster than the use of a useless index.

2008-03-05 Reply Admin

Or worse. You might find your name has been submitted.

2008-03-05 Reply Admin

Joe:
the article:
So how exactly can can I build
Can-can?

Can the can

Suzie Quattro

2008-03-06 Reply Admin

KenW, why don't you suggest an alternative. That sounded like a reasonable solution to an otherwise stupid database design.

How many row are we talking about in that "agent" table? 10? 50? 1,000,000? How many time is that query going to run per minute? hours? days?

"Only some kind of total imbecile" would against a solution without ANY information on its use case. You're the WTF here.

regards,

death · 2008-03-06 Reply Admin

This is scary... Seriously. Not only the WTF but also the comments that show this is not a single incident and there ARE people who believe this is elegant... OUCH...

Hatshepsut · 2008-03-06 Reply Admin

emil:

"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

As so many other stories on this site tell us, it's highly unlikely the designer of this scheme would even think of using a where clause, rather than trawling through all the records in client code.

vr602 · 2008-03-06 Reply Admin

Congo:
Has this technique been patented? If not I would like to use in my next DB design.
Also I'm trying to use a FOR NEXT loop in the WHERE clause of a SQL statement but I get a syntax error. Our DBA is a real jerk and won't help, does anyone have any code they can send me?

Try this: where FOR i;i++;i>10{ statements } NEXT i; and ... Make sure all your statements are in upper case and comma-delimited. If, for any reason, it doesn't work, give up programming, as you may have no aptitude for it at all. Actually, just go ahead and give it up now.

2008-03-06 Reply Admin

ClaudeSuck.de:
Please notice that a Yes/No column is not selective. There is, in fact, a 50% chance to find the right value. An RDBMS will, therefore, not use an index and make a table scan instead. This is actually faster than the use of a useless index.

If the RDBMS uses statistics, then, for a table with 99 YES records and one NO record, it surely *will* use the index when you're hunting for that NO record. Selectivity is not a static thing.

That said, it's true that in most cases, an index on a boolean is not a good idea. If some records are "very special", it often pays to keep these to a separate table (and use a view to reintegrate the special and non-special records). Depending on your scenario, this might also bring concurrency benefits. Of course, YMMV: twiddling a bit column is usually faster than moving records between tables, and the concurrency benefits are negated if the field changes frequently.

Ah, the joys of database optimization. The real challenge is to make it efficient while still presenting a sane schema to the outside world.

2008-03-06 Reply Admin

012-3100000100110000001000000103000001001100000-1011010101---0010011000000100000001000001001---00000500000010600010011000000100000001000001001100000010

...

0 1 1 - 3 1 0

WTF?

DZ-Jay · 2008-03-06 Reply Admin

Why couldn't all-knowing James create a few simple scripts to extract this data from that field, expand it, and insert it into additional satellite tables as proper?

I know that the first time I should encounter a field like that in a database that I need to query, I'd be plotting it's demise.

-dZ.

2008-03-06 Reply Admin

real_aardvark:
Of course: I'd write out a single record that consists of (pixel, filename) tuples, one after the other, probably in CSV format; although I might want to insert some metadata into the tuple to reflect the schema details. (This is an exercise in XML virtuosity that is best left to the reader.)

I rest my case!

2008-03-06 Reply Admin

sturm-und-drang:
that guy:
emil:
I have seen and had to maintain much worse in my short life. You'll do too and and at some point will have to relax and choose quiet resignation and a "let's get things moving" attitude over database normalization, Induced Tourette Syndrome and homicidal rage.
I've indeed encountered worse, and in my long life I've learned that a "let's get things moving" attitude eventually gets you an unpleasant conversation with your IT director, wherein you get to explain to him that a feature the owner has promised to deliver for an important client can't be done in the promised time-frame because the original developer was hopelessly naive and left behind something shitty and brittle.
Ain't that the truth. I wouldn't sweat it, though - 'emil' was wondering in all apparent seriousness whether his SUBSTRING hack might actually beat out indexed joins. He's got a lot to learn: a few more years of putting "let's get things moving" into practice will be just the ticket, I think.
Nothing can school developers about maintainability - and finding the all-important balance between "getting it done" and "doing it right" - quite so effectively as a few solid years spent maintaining and adding new features to their own houses of cards. A few gray hairs later, and you've got a perfectly serviceable intermediate programmer.

mmm... I though the WTF was about how to extract the information needed to have the new module in place. My bad, I guess. Thanks for teaching me a lesson.

2008-03-06 Reply Admin

Tell us how you really feel, Ken. Don't hold back.

2008-03-06 Reply Admin

Would someone plz send me teh codez? I need to implement this solution right away!

Many thanks!

2008-03-06 Reply Admin

[quote user="Ilyak"][quote] "select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0" [/quote] That's so PHPish - not knowing anything about parameter substitition and thus always introducing sql injection.

That's why nobody likes PHP, if not PHPist himself.[/quote

Oh please. You can fail to validate input in any language. This is the fault of idiotic programmers, not the language they write in.

2008-03-06 Reply Admin

You guys have lost sight of the goal here. He needs to create a list of authorized sellers by product.

Add a varchar column AuthSellers to the product table that contains a comma separated list of authorized sellers. You could even add the existing codes 1-9 at the end of the name if you need to.

Then a simple query "select product from product_list where AuthSellers like '%Joe Blow%'" will get the desired results.

At least this is how the designer of the database I work with would have done it.

2008-03-06 Reply Admin

Well, the actual solution would be pretty easy. However it appears as though that table must be updated every time a new 'product' line is added or modified (I would hope). Talk about an orphan hell!

Screw scalar practices!

2008-03-06 Reply Admin

this webcomic is a wtf:
I don't know, I just don't think you're making a very good sale on this one. Sure, now 80 gigs seems like peanuts, but 10 years ago, costing out 80 gigs for a vax was a different story, not to mention that this was nowhere near the primary app for the vax it was installed on. Even now, I like to program for virtual servers, and I'm not going to buy storage for weenieisms unless its going to somehow win me something back.
What are you selling me here? Dude you have listed no advantages except for the privilege of pissing away storage space and losing the ease of coding that record editor.

Thats my biggest problem with RDBMS weenies. Even as I've seen in this thread, you guys argue for relational purism yet at the same time don't even know what you're talking about most of the time. Thats the real WTF here IMO!

Let me guess, if you were rewriting microsoft paint, you'd store the filename once for each pixel, right?

My guess is that there was an original application written on a X.500 or similar directory that was later moved to an SQL database due to hardware failure/end of support for the directory software. The maintainer kept the view and controller code and only changed that related to storing the information.

RDBMSs are perfect for accounting, but a big pain for other very important real life tasks, such as text storage, indexing and retrieval.

real_aardvark · 2008-03-06 Reply Admin

this webcomic is a wtf:
real_aardvark:
Of course: I'd write out a single record that consists of (pixel, filename) tuples, one after the other, probably in CSV format; although I might want to insert some metadata into the tuple to reflect the schema details. (This is an exercise in XML virtuosity that is best left to the reader.)

I rest my case!

I really, really, hope that you've lost the sarcasm tags there. Otherwise: sorry, you're insane, and I recommend EST right now. Either that, or go back to school. Pre-school, I would think.

2008-03-07 Reply Admin

real_aardvark:
this webcomic is a wtf:
real_aardvark:
Of course: I'd write out a single record that consists of (pixel, filename) tuples, one after the other, probably in CSV format; although I might want to insert some metadata into the tuple to reflect the schema details. (This is an exercise in XML virtuosity that is best left to the reader.)

I rest my case!
.... some retroactive rationalization snipped....

You're the one advocating XML for storing bitmaps. Thats the REAL wtf!!!

2008-03-07 Reply Admin

DHager:
Perhaps for an encore you'd hire the doctor who can fix your lung cancer without a hospital stay instead of the "surgery" or "chemo" whiny complainer ones, eh?

We can hope.

2008-03-07 Reply Admin

PHP does, but PHPers don't.

Othersider · 2008-03-08 Reply Admin

Congo:
Has this technique been patented? If not I would like to use in my next DB design.
Also I'm trying to use a FOR NEXT loop in the WHERE clause of a SQL statement but I get a syntax error. Our DBA is a real jerk and won't help, does anyone have any code they can send me?

Any code should work, as long as it has lots of GOTOs.

2008-03-14 Reply Admin

Typo:
What?? Noone spotted that the beginning of the string
012-3100000100110000001000000103000001001100000-10110--

does not correspond to

Value 011-3100...

I thought you guys were programmers :-)

Do expect programmers to actually look at the data?

2008-03-14 Reply Admin

Typo:
What?? Noone spotted that the beginning of the string
012-3100000100110000001000000103000001001100000-10110--

does not correspond to

Value 011-3100...

I thought you guys were programmers :-)

Do you expect programmers to actually look at the data?

2008-04-02 Reply Admin

this webcomic is a wtf:
one database I worked on had to be unnormalized because the normalized form would have taken maybe 10 times the disk space that the unnormalized form took.

It's called denormalization and yes it's a valid technique. I've never heard anyone say you must normalize to the extreme in every situation. I'm confused though, because typically denormalization actually adds to disk used, and the point is to improve performance!

this webcomic is a wtf:
RDBMS's are a tool, sometimes they're the wrong tool. Fortunately, the major vendors are catching on, even Oracle now has repeating groups.
Look at the typical example, customers, orders and payments, say each customer could have thousands of orders, and have made thousands of payments, but would only have one balance due column per customer, why would you keep totalling up these orders and payments instead of just maintaining a balance column per customer everytime they added a new order or payment? Yet this is exactly the sort of thing I hear database purists rail against.

With Oracle, I would use a fast refresh materialized view - easy as pie and you get the best of both worlds. Other vendors have similar options...Not sure about the fast refresh though.

2008-04-02 Reply Admin

JM:
If the RDBMS uses statistics, then, for a table with 99 YES records and one NO record, it surely *will* use the index when you're hunting for that NO record. Selectivity is not a static thing.

With that few records, the optimizer will probably do a full table scan anyway. If you are talking about a large table with 99% YES and 1% NO then that's another story. =)

Bitmap indexes could work well for the 50/50 case, provided the underlying table isn't heavily updated.

2008-04-21 Reply Admin

Let's not forget, in all this talk about how fast or slow the substring implementation might be, that the time to maintain the mess is the expensive part.

Ask yourself these questions.

How much does an engineer cost.

How much does a CPU Second cost.

I think you will find the first answer is a lot larger than the second.

So what if the normalized solution is somewhat slower. It will at least be maintainable, and scalable.

It will probably not fall flat on its face when 10x or 100x rows are added to any of the tables.

Even if it does fall flat, some changes in indexing etc can pick it up again without rewriting the whole mess.

2009-10-30 Reply Admin

Based on my knowledge of PHP, it should be fairly easy to do...


//Connect to database, etc.

for($i = 0; $i < strlen($string); $i++) {
$status = $string[$i];
$query = "INSERT INTO table (`ID`, `Status`) VALUES (`$i`, `$status`)";
mysqli_query($dbc, $query);
}

If anyone sees this, can they tell me what's wrong with it?

immibis · 2010-06-28 Reply Admin

KenW:
emil:
supposing that 0 is for "can sell":
"select * from agencies where SUBSTRING(strProductLines," . $prodID . ",1) == 0"

Not that bad.

Wrong. It's terrible. It's worse than terrible. The fact that you even suggest it tells me to stay far away from anything you've worked on, or for that matter anything that anyone who ever associated with you worked on.

emil:
I wonder which is faster: a straight join on three tables (agencies, productLines, agencies_producLines) or this hack ?
Still, it's not what I would do, unless the performance advantage of the SUBSTRING version would be big enough.

More evidence that everyone should stay far away from your code.

The SUBSTRING() solution is one of the worst possible ones. It not only completely removes the advantage of indexes (unless you happen to be searching for the first character of the column values), but it requires the massive overhead of a function call for every single row in the table. Only some kind of total imbecile would even suggest this as a possible solution; it would take a total moron to even think that this is "Not that bad.".

Please, for the sake of all that's good in the world, find yourself a new line of work far removed from computers. In fact, I'm not even sure you should be allowed near a computer, as a matter of public safety.

It's better than doing "SELECT *" then filtering them manually.

Pretty Simple

Leave a comment on “Pretty Simple”