• Paula (unregistered)

    1.) Acquaint oneself with database 2.) ?????????? 1000000.) Profit!

  • (cs)

    I think Paula is correct...it's just about profit...

    Mobocracy

  • Herby (unregistered)

    Is is just me, or are WAY to many WTF's related to SQL. It seems that SQL is just about the biggest WTF there ever was. It is Sooo easy to get wrong (and get a "reasonable" result) that I suspect that we should all go back to first principles and say that databases are a general WTF, and let it go at that. Of course, the little "database" that I implemented in high school (back in the 60's) was done in Fortran, but that is another story.

  • Database Troll (unregistered) in reply to Osno
    Not only sick, also slow. That query doesn't use indexes and in the case of Sql Server you will have to recompile stored procedures on each use because of that.

    Time for the mandatory mock the Microsoft user comment...

    Get yourself a proper database! Oracle and IBM DB2 both support indexes on functions or expressions so this could happily be an indexed query.

  • Mandelbrot (unregistered) in reply to snoofle
    snoofle:
    At first glance it may seem sick, but it's possible that the application code has a relationship where thing2.id = thing1.id * 1000000 (semi-analgous to dates stored like: year*10000+month*100+day), and so they propagated that into the db relationships instead of mapping it properly.

    I'm not saying it's a good thing to do, but I've seen it done.

    Great idea! My next foreign key relationships will be fractal functions.

  • (cs)

    If any one on this thread thinks that it is a good idea to do what this article discribes....then do us that will be fixing your code in the future a favor , and swallow a bullet and get out of the business...

    Mobocracy

  • SPHS (unregistered)

    To anyone who doesn't find the typo objectionable:

    This isn't some artsy blog where you can say precision doesn't matter. This is a technology blog, and it's read by programmers. If you make that kind of mistake in your code, you're gonna find yourself with serious problems. It's natural for us to expect a higher standard here.

    And c'mon, it's not like this was a long article and the two numbers were separated by lots of space. They're essentially right next to each other. He could have copied and pasted and then added commas.

    I realize this seems like a stupid thing to get worked up over, but these kinds of typos are far too common here to just let slide.

  • BentFranklin (unregistered)

    All you perfectionists out there:

    All you need to do is reward Alex on mistake-free days by visiting all his sponsors' links, and withhold your clicks when you see mistakes.

    Or stop complaining.

    Anything else is hypocritical.

  • iToad (unregistered) in reply to a
    a:
    I can't believe you idiots spend this much time arguing over typos. This is ridiculous. Typos are not "the real WTF", "the real WTF" is that anyone gives a shit. It's a blog, not his job, it doesn't have to be perfect. Stop wasting your time and ours.

    In all online discussion forums, the size and virulence of the arguments is inversely proportional to the importance of the subject under discussion.

  • (cs) in reply to Jay
    Jay:

    Unless God's goal is to prevent that particular airplane from taking off, in which case:

    1. Observe plane attempting to take off
    2. Determine that this is not in accordance with Your long-range plan for the universe
    3. Ignite volcano to prevent plane from taking off
    4. Profit! (Or in this case, Prophet!)

    Win!

  • FuBar (unregistered) in reply to iToad
    iToad:
    In all online discussion forums, the size and virulence of the arguments is inversely proportional to the importance of the subject under discussion.
    Feh. Now you're starting to sound just like Hitler.
  • Sam (unregistered) in reply to toshir0
    toshir0:
    (not it's not a typo)(yes, it has already be quoted in other TDWTF posts)(maybe I'm just loving parenthesis)
    1. (no, it's not a typo)
    2. (yes, it has already been quoted in other TDWTF posts)
    3. (maybe I'm just loving parentheses)

    I mean... one typo per set of brackets - that's an appalling error rate. Maybe you need an Editor?

  • abitslow (unregistered) in reply to Max
    Max:
    Anonymous:
    Jason:
    The attitude that you can't criticize someone unless you can do better is senseless. I'm not an aeronautical engineer, but I can still see that someone whose airplane won't take off is doing it wrong.
    How can you possibly say that? From your position of "absolutely clueless" you cannot possibly make the assertion that "you're doing it wrong". There may be environmental factors at play, there may be any number of external influences that you simply don't understand so you are in no place to judge. I actually think your basic point is OK but your example was absolutely retarded! "Hurr durr, you plane no fly, you doing it wrong, hurr durr!!!".
    I think the example is just fine. He doesn't know the details of airplane design, but he knows the definition of an airplane encompasses machines that fly. If someone tells him they have an airplane, but upon demonstration it fails to fly, he knows something is wrong. Whether the "doing it wrong" is inherent to the design (insufficient thrust, excessive drag, excessive weight, etc.), due to pilot error (not pushing the throttle enough, pushing down instead of pulling up because he doesn't invert the pitch when he plays video games, etc.), or due to some external factor (strong crosswind, improperly maintained runway, etc. - all of which should be checked before flight anyways), he can still see that something is wrong. Likewise, when there is a typo in TDWTF, there's no knowing if it was in the original version, added when posting, or a result of a cat jumping on the keyboard. Regardless, it's something that ought to be checked before being posted. In the end, some errors will still make it through, just like some planes will fail to take off for one reason or another, however the occurrence should be reasonably small.

    you guys are so anal.

  • Osno (unregistered) in reply to Database Troll
    Database Troll:
    Not only sick, also slow. That query doesn't use indexes and in the case of Sql Server you will have to recompile stored procedures on each use because of that.

    Time for the mandatory mock the Microsoft user comment...

    Get yourself a proper database! Oracle and IBM DB2 both support indexes on functions or expressions so this could happily be an indexed query.

    I always kind of assumed that if you do things right, Sql Serevr will work ok (using indexes and such) and if you just hack your way to the data, it'll be slow. This is confirmation. And btw, you can also force an index in sql... I was speaking of the automatic precached type.

    Disclaimer: I just read that and it sounded too violent. Not the intention at all, but I don't know how to rephrase it to make it milder.

  • (cs) in reply to Sam
    Sam:
    toshir0:
    (not it's not a typo)(yes, it has already be quoted in other TDWTF posts)(maybe I'm just loving parenthesis)
    1. (no, it's not a typo)
    2. (yes, it has already been quoted in other TDWTF posts)
    3. (maybe I'm just loving parentheses)

    I mean... one typo per set of brackets - that's an appalling error rate. Maybe you need an Editor?

    Thank you so much for correcting my bad english*, dude... (i swear it's not ironical but who will believe it on this site ?) (maybe I'm just a french jerk ;)

    Moreover, when referring to Muphry's Law, I SHOULD have made the typoes myself to be fun. My bad english have more spirit and humour than I have. It's fucking hilarious.

    • for my defense I must say english isn't my mother tongue and I've never lived in an english-speaking country... anyway : I've learned a bit more today thanks to you.
  • Koan (unregistered) in reply to Osno

    Or in Sql Server just make a computed column of the calculated value (o.ShipToCustomerId / 1000000), index that column and then join on an actual column rather than a formula.

  • Harrow (unregistered) in reply to jrh
    jrh:
    ...Would you have a class where the CustomerID and the OrderID are stored in the same property and leave it up to the class user to pick the two apart?
    Well, duh. Of course not -- what you describe is inconvenient and error-prone.

    Any responsible developer will provide access methods to pick the two apart.

    -Harrow.

  • Silverhill (unregistered) in reply to Anon
    Anon:
    Just because I can't (or don't want to) do X doesn't mean I can see the problems when somebody else does X. And if you don't like this comment, let's see you do better.

    "Just because I can't (or don't want to) do X doesn't mean I can't see the problems when somebody else does X. And if you don't like this comment, let's see you do better."

    FTFY ;-)

    captcha: nibh (National Institutes for Bodily Health, I presume)

  • Anonymously Yours (unregistered) in reply to RobFreundlich
    RobFreundlich:
    Anonymously Yours:
    Amateur. My single table database can store anything and has its customer records on a modulus of 17.

    You kid, but I've worked with something like this.

    My second job was at a medical software company (1991 or so). The frequently-used database tables were optimized around the limitations of the programming language/database (which originated in the late 60's or early 70's and whose name is synonymous with a childhood disease) and the disk storage system.

    I remember one table specifically - it was a data dictionary, and the original programmers (also in the early 70's, I believe) had determined that storing the primary key (DE, for Dictionary Entry) as two values - DE DIV 23 and DE MOD 23 - would provide the most efficient access.

    When I got there, the technology had progressed far beyond needing such things, but like most legacy systems, it hadn't been changed.

    I'm... I'm so sorry... I feel someone who unknowingly told a joke about setting people on fire in front of a burn victim.

  • (cs) in reply to Osno
    Osno:
    Not only sick, also slow. That query doesn't use indexes and in the case of Sql Server you will have to recompile stored procedures on each use because of that.

    It probably isn't that slow. With code like that running their information systems they probably don't have many customers/

  • (cs) in reply to frits
    frits:
    Arthur D:
    No, everybody, please, continue to comment on things that you don't understand. You add to the entertainment value of this site.

    Cointently! Nyuck Nyuck!

    That's "soitenly". "Cointently" is when 2 people are intent on the same thing at the same time. Also, Nyuck is the old Soviet Union-style spelling. Preferred, today, is "nyuk".

  • Aussie Contractor (unregistered)

    "I'm Sorry, your company must have less than '1000000' customers. Please delete a customer account and try again."

  • (cs) in reply to SQLDave
    SQLDave:
    frits:
    Arthur D:
    No, everybody, please, continue to comment on things that you don't understand. You add to the entertainment value of this site.

    Cointently! Nyuck Nyuck!

    That's "soitenly". "Cointently" is when 2 people are intent on the same thing at the same time. Also, Nyuck is the old Soviet Union-style spelling. Preferred, today, is "nyuk".

    In Soviet Union three stooges nyuk you.
  • Luis Espinal (unregistered) in reply to Herby
    Herby:
    Is is just me, or are WAY to many WTF's related to SQL.

    There are WAY too many WTFs related to many programming languages.

    Herby:
    It seems that SQL is just about the biggest WTF there ever was.

    No. It is not a perfect language, but it isn't a terrible one for what it is (a language to query relations out of a RDBMs). It is people who use it ways not intended for it, or that are oblivious to the basics of SQL and relational database theory (which should be part of any undergrad CS/MIS curriculum) that are to blame, not the language.

    This would be akin to blaming Java or C# or whatever for the WTFs that inept programmers code with them.

    In this particular example (a WHERE clause based on comparing one id against a mathematical expression of another id is a programming WTF, not a SQL one.

    Imagine the following construct in your favorite language:

    x=someConstant
for 1 to someVeryLargeNum
do
   a = aVeryExpensiveInvariantAndUnnecessaryComputation(x);
done

    No matter the language of your choosing, the WTF is on the programmer, not the language. This example WTF is orthogonal to the SQL example.

    Herby:
    It is Sooo easy to get wrong (and get a "reasonable" result)

    If by "reasonable" result you mean just the data, then you are partially right. And I say partially because there are well-known methods to make sure such things never happen. But the truth of the matter is that what matters is not just the result, but the performance and maintainability of the system that spits out the result, and the ease by which you can have confidence that the result is correct in the first place.

    I'd say that, without knowing anything about the submitted WTF, if that is only one instance, it's something we can stomach. But if that is the general pattern across the system, it is a major brain fart. My experience has been that people who commit those errors all over the place simply don't how to use a RDBMS and have never put an ounce of effort in picking up a book on SQL. They are usually the same people who create tables with fields named temp1, temp2 and temp3 where they throw everything and the kitchen sink. And that's simply the SQL equivalent of unstructured programming. In this time and age, the blame falls squarely on the programmer.

    Herby:
    that I suspect that we should all go back to first principles and say that databases are a general WTF, and let it go at that.

    And what kind of first principles are we speaking off? What alternatives do you see today over RDBMS for systems and enterprises? Yes, we could go NoSQL or fall back to the older models (network and hierarchical.)

    But I could bet one of my balls that the same type people who commit such SQL WTFery would create equivalent monstrosities in any DB model you can think off. The onus still falls squarely on the programmer.

  • FuBar (unregistered) in reply to Anonymously Yours
    Anonymously Yours:
    I feel someone
    You feel someone? What part of them do you feel? Better be careful or you'll be up on harassment charges.
  • Ollie (unregistered)

    This is great. I thought the legacy SQL in the code base I've been maintaining was bad. Now I know it could be at least 1000 times worse.

  • (cs) in reply to SQLDave
    SQLDave:
    frits:
    Arthur D:
    No, everybody, please, continue to comment on things that you don't understand. You add to the entertainment value of this site.

    Cointently! Nyuck Nyuck!

    That's "soitenly". "Cointently" is when 2 people are intent on the same thing at the same time. Also, Nyuck is the old Soviet Union-style spelling. Preferred, today, is "nyuk".

    Don't be a Cherkov (Soviet Union spelling retained).

  • (cs) in reply to EngleBart
    EngleBart:
    How are they going to have a big party for the millionth customer? Sorry, you would have been the millionth customer, but we overflowed your id back to zero, so the original customer zero is receiving your order and your prize.

    P.S. Customer 0 is the special back door customer id that gives you administrative privileges on the site. Planned rather well I would say...

    Well, obviously they would have a big flashing banner at the top of every page stating, "Congratulations! You are the 1,000,000th customer! Please click here to redeem your {car|$1000|administrative privileges|brain shattering into 1,000,000 pieces}"

  • RocknRoller (unregistered) in reply to frits
    frits:
    SQLDave:
    frits:
    Arthur D:
    No, everybody, please, continue to comment on things that you don't understand. You add to the entertainment value of this site.

    Cointently! Nyuck Nyuck!

    That's "soitenly". "Cointently" is when 2 people are intent on the same thing at the same time. Also, Nyuck is the old Soviet Union-style spelling. Preferred, today, is "nyuk".

    Don't be a Cherkov (Soviet Union spelling retained).

    The Cold War is over due to Global Warming - but the spelling endures

  • Adrian (unregistered) in reply to Bruce W

    or to catch the pedents.

  • Steve The Cynic (unregistered) in reply to Adrian
    Adrian:
    or to catch the pedents.
    I expect you know it should be "pedants", with an "a".
  • A pedant (unregistered) in reply to Steve The Cynic
    Steve The Cynic:
    Adrian:
    or to catch the pedents.
    I expect you know it should be "pedants", with an "a".

    Catch me if you can

  • graybreard (unregistered) in reply to RogerC
    RogerC:
    I feel like my brain just exploded into 1,000,000 pieces.
    My brain explodes at the fact that someone would find this mild thing that strange. Welcome to the real world..
  • graybreard (unregistered) in reply to Herby
    Herby:
    Is is just me, or are WAY to many WTF's related to SQL.

    Perhaps because a) most of the site's followers understand it b) posting lots of SQL wtf's drags a bigger SQL crowd, posting new SQL wtf's c) many other wtf-y places are so laden with NDAs that you don't dare post them d) the editor doesn't understand some other wtfs and they never get posted

    Personally, I skip most of the code wtfs these days; the best ones recently have been the job interview stories..

  • Banking Contractor (unregistered) in reply to dave
    dave:
    Looks to me like they use order IDs on the form XXXXXXYYYYYY where XXXXXX is the customer ID and YYYYYY is a squence number. Yes, it could have made sense to split this up into two columns in the database, but this method works just as fine for finding orders for a customer.

    Wow.... So you think that's OK do you??

  • Zel (unregistered)

    The real wtf here is people trying to -even remotely- justify this monstrosity...

  • Banking Contractor (unregistered) in reply to Zel
    Zel:
    The real wtf here is people trying to -even remotely- justify this monstrosity...

    I know! Fully agree... I thought the DailyWTF audience was supposed to be developers who actually had a clue, but judging by some of the comments lately it seems that the people on here are very similar to the people who produce the WTF's!!!!

  • Banking Contractor (unregistered) in reply to graybreard
    graybreard:
    Herby:
    Is is just me, or are WAY to many WTF's related to SQL.

    Perhaps because a) most of the site's followers understand it b) posting lots of SQL wtf's drags a bigger SQL crowd, posting new SQL wtf's c) many other wtf-y places are so laden with NDAs that you don't dare post them d) the editor doesn't understand some other wtfs and they never get posted

    Personally, I skip most of the code wtfs these days; the best ones recently have been the job interview stories..

    Dude I think you should check out the editors website... I think Alex understands more than you seem to think....

  • Rhialto (unregistered) in reply to Zapp Brannigan
    Zapp Brannigan:
    frits:
    Andy:
    For the database unacquainted, having an identifier in a table that’s the result of another identifier divided by 10,000,000 is just sick.

    How about 1,000,000? It that OK?

    I'm not sure, it's either 10 times better or 10 times worse.
    It must be 10 times as good (or 9 times better), since presumably dividing identifiers by 1 is ok.

  • Nex (unregistered) in reply to frits

    Thinking that this crowd of ORM loving devs could recognize a database design issue is going a bit far. Seriously nobody has even brought up the horrible queryplan this where clause will generate? "Indexes Schmindexes..."

  • Nex (unregistered) in reply to Herby
    Herby:
    Is is just me, or are WAY to many WTF's related to SQL. It seems that SQL is just about the biggest WTF there ever was. It is Sooo easy to get wrong (and get a "reasonable" result) that I suspect that we should all go back to first principles and say that databases are a general WTF, and let it go at that. Of course, the little "database" that I implemented in high school (back in the 60's) was done in Fortran, but that is another story.

    I smell an Oracle Douche...

  • umlikewhatever (unregistered) in reply to MyKey_

    People still say this?

    How lame.

  • (cs) in reply to RobFreundlich
    RobFreundlich:
    The frequently-used database tables were optimized around the limitations of the programming language/database (which originated in the late 60's or early 70's and whose name is synonymous with a childhood disease) and the disk storage system.

    Aaah, ChickenPox++... Memories...

  • (cs) in reply to Banking Contractor
    Banking Contractor:
    Zel:
    The real wtf here is people trying to -even remotely- justify this monstrosity...

    I know! Fully agree... I thought the DailyWTF audience was supposed to be developers who actually had a clue, but judging by some of the comments lately it seems that the people on here are very similar to the people who produce the WTF's!!!!

    Oh come on, you can easily write a SQL query to select all transactions for a single customer without having to resort to division in the query statement...

    SELECT * FROM table WHERE ShipToCustomerId IN (1000000,1000001,1000002,1000003, .... 1999999);

  • AC (unregistered)

    Oh my. The WTFs in this comment thread are more plentiful and sometimes even bigger than that of the article.

  • (cs) in reply to AC
    AC:
    Oh my. The WTFs in this comment thread are more plentiful and sometimes even bigger than that of the article.

    Your original and superior comment is a breath of fresh air.

  • remi (unregistered)

    You know sometimes we have to put some fun in our job ... so maybe he tried to make a good joke to his future co-worker.

    It's way funnier than your boss who works in IT since 20 years and tells you "I create private function so they don't appear in intellisense"

  • Mike (unregistered)

    Did the developer responsible also write the custom "database synchronization" algorithm used in one of our records management products?

    That system distinguishes "locally-added records" from "records in the master database" by using ID's greater than or equal to 1000 for the local records, and ID's less than 1000 for records that were already on the server. During the synchronization process, the local records with ID's >= 1000 are INSERT'ed into the master database, and UPDATE statements are generated for the ones below 1000. Of course, such nonsense with the ID numbers is fine as long as you hide the evidence, so the last step in the process "re-sequences" all those pesky >= 1000 ID numbers so that they all start from 1 again in the master database.

    All that was only for ID's generated by the program (which were supposed to represent sequence numbers in collections of related records, i.e. Person -> Person Allergies -> Allergy 1, Allergy 2, Allgery 3).

    For auto-generated identity columns (mostly primary keys), the software reconfigures the Access database at start-up to auto-increment backwards, so that it could later tell which records were added locally and which ones were already in the master database by checking the sign of the primary key value.

    Clearly, though, more zeros is superior and I would much happier with this system if it used 1,000,000.

  • (cs) in reply to Владмир
    Владмир:
    RogerC:
    I feel like my brain just exploded into 1,000,000 pieces.
    Mine exploded into 10,000,000 pieces.
    Mine's only in 9001 pieces.
  • Ben (unregistered) in reply to jrh

    jrh,

    "But why do otherwise decent developers throw out good practice when they get to the database?"

    I don't, I've just seen a fair number of systems with different requirements. And my contention wasn't that this was "good practice," just that it wasn't "sick" or worthy of a WTF.

    "Would you have a class where the CustomerID and the OrderID are stored in the same property and leave it up to the class user to pick the two apart?"

    Trick question. A table or, more properly relational variable, stores a set of propositions with a common predicate, and the state of the table (the assigned relational value) constitutes a true statement about the world. A class is a common set of methods for a set of objects that have no particular meaning, though the definition of a class is highly dependent on the vagaries of your style of OOP.

    The two are, logically, completely unrelated and anyone who tries to map tables to classes ought to punch themselves in the face. Though, the fact that they're already creating a ton of useless work by recreating the DBMS, poorly, might be punishment itself.

    "If you have a compound key you, you need to either use a compound key or a surrogate key."

    I wouldn't just load two values into a single column like that for the hell of it, I'm assuming the person had some requirement that it be stored like that. Usually that requirement is someone else's design decision.

    Once you've designed a schema one way, it can be expensive to change it, so I wouldn't be surprised to see a mildly problematic design like this persist for a long time.

    "As for your theory that it's not "so awful to load two number into one by means of simple arithmetic", well I guess that's true as long as you don't need your database to scale in any way."

    Okay, show me how this will prevent scaling the number of concurrent users. You did say "any" way.

    I already suggested how it will be hard to scale: "It's generally a bad idea to store two values as one, since it makes them unavailable to relational operators." To expand on that, you lose declarative integrity through foreign key constraints, and you'll have to babysit the optimizer with, e.g. function-based indexes, partitioning and such.

Leave a comment on “The Quotient ID”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article