• When (unregistered)

    WHEN 1 THEN FRIST

  • (nodebb)

    Someone wanted to make a statement!

    But honestly, I couldn't stop laughing just by looking at it. Thank you for this gem!

  • Darren (unregistered)

    I'm not a SQL-er, but won't this just make everything 8? 2 becomes 3, which becomes 4, which becomes 5, and so forth.

  • (nodebb)

    I'm just pleased to see that the database table uses UPPERCASE while the result set uses CamelCase and there are zero typos between the two sets of field names. It's a low bar, but they cleared it.

    Although then they spoiled it by spelling out things like DocumentType and UpdatedDate, but then went with abbreviated things like DocNmbr and CustomerNo.

    For as often as the word "number" appears in IT contexts, you'd think we'd have settled on one way to abbreviate it. You'd be wrong though.

    As to the CASE itself, I'm impressed the business rules (rule change?) are as simple as "increment by 1" in that range. I'd far sooner expected the ugly history and PHB involvement to have resulted in type 2 becomes 4, type 4 becomes 7 and type 7 becomes 2. While type 3 becomes 13 and type 6 becomes type C. Etc.

  • (nodebb) in reply to Darren

    Not at all. It's more like this from C:

        switch(h.DOCUMENTTYPE)
    {
    case 2:
        DocumentType = 3;
        break;
    case 3:
        DocumentType = 4;
        break;
    case 4:
        DocumentType = 5;
        break;
    case 5:
        DocumentType = 6;
        break;
    case 6:
        DocumentType = 7;
        break;
    case 7:
        DocumentType = 8;
        break;
    default:
       DocumentType = h.DOCUMENTTYPE;
    }
  • ichbinkeinroboter (unregistered)

    I dont feel this fix is the real WTF. Just pragmatic. As an actual real-world fix right now without tons of work? It seems solid to me.

    I want the REAL WTF that you ask the questions about...

  • ichbinkeinroboter (unregistered)

    I dont feel this fix is the real WTF. Just pragmatic. As an actual real-world fix right now without tons of work? It seems solid to me.

    I want the REAL WTF that you ask the questions about...

  • DrPepper (unregistered)

    This is a huge WTF. When you write a record into the DB you have to remember the DocumentType is a different value than the one you got back from the query. And you have to remember that for every client that connects to the DB. And that's where the bugs live.

  • (nodebb)

    My guess would be someone is trying to match an Enum in the code and they had a #2 that didn't match anything in the database.

  • (nodebb) in reply to Auction_God

    and they had a #2 that didn't match anything in the database.

    Somebody had a number two.

    Actually, I don't think this really clears the bar as a WTF. It's just some data normalisation attempt (as in making it consistent, not third normal form or whatever), but the joke was too tempting to resist.

  • Jon (unregistered) in reply to Steve_The_Cynic

    Having written my first code in 1973, it warms my heart to see the lingua franca is still C :)

  • (nodebb)

    I've seen this kind of thing a lot when you have to match data between systems, although sane peope usually create match tables so they don't have to do it on the query itself. And given the way the fields are aliased y bet that query was taken from a view, possibly querying a different database altogether.

    @Remy do you happen to have the full text of the query?

  • (author) in reply to AGlezB

    I do not. This is as submitted. I would have loved to see it.

  • (nodebb)

    TRWTF:

    When DOCUMENTTYPE returned from the database is 7, we say 8. When DOCUMENTTYPE is 8, we also say 8.

    This query demonstrates the WTFedness of wonky SQL code, the WTFedness of changing document types "dynamically" rather than "pragmatically", and it demonstrates an extremely poor example of one-to-many "relationships"

  • Jonathan (unregistered) in reply to DrPepper

    Bingo. This is creating a breeding ground for bugs. At a bare minimum, call the result of the CASE statement something like DOCUMENTTYPE_adj, or DOCUMENTTYPE_that_other_db.

  • (nodebb) in reply to Bananafish

    When DOCUMENTTYPE is 8, we also say 8

    We have no evidence that this can even happen (is 8 even valid for h.DOCUMENTTYPE?), although it does slightly worry me.

  • (nodebb) in reply to Steve_The_Cynic

    Maybe DOCUMENTTYPE set to 8 is the equivalent of redirecting to /dev/null.

  • MRAB (unregistered) in reply to When

    You're missing the incrementing.

    It should be WHEN 0 THEN FRIST WHEN 1 THEN SNECOD ELSE FILE_NOT_FOUND.

  • Joe (unregistered) in reply to Bananafish

    Playing devil's advocate, maybe this advances some status, and 8 is the final status, so it can't be advanced beyond that?

  • COBOL Dilettante (unregistered) in reply to Jonathan

    Yeah, but you just know that this query populates a view or derived table, from which countless unaccountable business users run their own daily reports.

    And at one time this derived table did indeed just contain h.DOCUMENTTYPE without edits, but then someone realised that what the business considers to be a DOCUMENTTYPE is not the same as how "h" defines a DOCUMENTTYPE, hence the edits.

    And the sane person on the team said "Shouldn't we call this BusinessDocumentType or something?" But the Project Manager said, "No, because then we would have to find every team that queries this table to get them to update their reporting SQL, and they would all make me raise a Jira ticket to put on their 18-month backlog, and we wouldn't find everyone anyway, so let's just do this."

    Granted, I've no idea what company we're talking about, but that's definitely what happened

  • (nodebb)

    For all the comments y'all have posted, the only real WTF is that there's no comment. For all we know, this code is brilliant given the context. It probably isn't, but maybe it is . . . it's certainly /stupid/ enough that it's good for what it had to do, but without a thorough comment, we'll never know.

  • Jonathan (unregistered) in reply to COBOL Dilettante

    That's totally plausible. But if that were the situation wouldn't it mean that those countless daily reports that reference that field have been just plain wrong, forever?

    The common sense part of me would say to go ahead and change the column name, and tell everyone who had referenced the previous column "Your previous reports were wrong, now they're broken", and try to convince them that we'd done them a favor by clearly identifying the incorrect reports.

    In reality, if no-one was previously bothered by the incorrect reports, they won't see any benefit in having valid ones, and they really just don't want to do the work required to fix them. Hell, when they added the case statement to increment the doctype there should have a huge discontinuity, and people should have been screaming "why did they all of a sudden change so much?". Just replace that CASE statement with a call to rand(1,8).

  • (nodebb) in reply to Steve_The_Cynic

    We have no evidence that this can even happen (is 8 even valid for h.DOCUMENTTYPE?), although it does slightly worry me.

    No, we don't know that 8 is a valid value for h.DOCUMENTTYPE. But 8 is definitely a valid DocumentType because it is returned as DocumentType if h.DOCUMENTTYPE is 7, so we can presume it is a valid value and, as such, might be the true value returned by the query before transformation.

    Joe says:

    Playing devil's advocate, maybe this advances some status, and 8 is the final status, so it can't be advanced beyond that?

    That could be, I suppose, but there's (imho) an extra heavy-duty WTF if you advance the status by manipulating the the value returned rather than performing a transaction that includes updating the status and then reporting its value. But this column is named "Document Type" and I sincerely doubt that these advance. Sure, it's possible to translate a DOCX into a PDF and a GIF to a JPG, but it's not "normal" for document types to advance in a cycle: Plain Text -> Word Doc -> GIF -> PDF -> JPG -> Rich Text -> Post Script -> XLSX [-> Recycle Bin] In what universe would a document's type advance like that? Similarly, it's not likely that a document type would change from Building Permit -> Inspection -> Certificate of Occupancy -> Tax Assessment -> Title -> Deed [-> Recycle Bin], would it?

    The most frightening possibility is that two or more applications/modules/classes/whatever share the same data, but the value of Document Type 1 (Word Document) in the first application is Document Type 2 (Word Document) in the second. I can't imagine how or why that would happen, but it's not outside the realm of possibilities.

  • airdrik (unregistered)

    I'm also "appreciating" the column aliasing just to convert the case. Isn't SQL generally case insensitive, so given a table with columns in all-caps, a select using mixed case will return the results in mixed case? At least that's the case with sql server. In such environments, what they did would be equivalent to just using the target casing:

    SELECT CASE h.DOCUMENTTYPE
        WHEN 2 THEN 3 WHEN 3 THEN 4 WHEN 4 THEN 5
        WHEN 5 THEN 6 WHEN 6 THEN 7 WHEN 7 THEN 8
        ELSE h.DOCUMENTTYPE
    END AS DocumentType,
h.DocNmbr, h.FullPolicy, h.BatchId, h.OrigBatchId, h.UpdatedDate, h.CustomerNo, h.ProjectID, h.Amount

    If that's not the case in their environment, then what's up with OrigBatchId being the only one that's not all-caps?

  • erffrfez (unregistered) in reply to airdrik

    the differences MIGHT be "convention". With the assumption that these are two different databases, but with different meaning for what a DocumentType / DOCUMENTTYPE of 2 is etc, we might be using this to try to keep clear which one is which.

    If that is the case, we all pity the poor souls who need to deal with that....

  • DJ LIZARD SPUDDER (unregistered)

    [Sigh] - Even T-SQL allows when case between ....

  • DJ LIZARD SPUDDER (unregistered) in reply to DJ LIZARD SPUDDER

    Yes, I mean ... Case .. When Between

  • 516052 (unregistered) in reply to airdrik

    Depends on the implementation. Postgrese in particular is case sensitive and not in the fun way. Long story short if a table has a mixed case name you have to access it via the same case. Sane so far. But the trick is you have to put all such table names in "double quotes". If you do not it just silently converts your input into lower case and than predictably fails to find the table.

    So MyMixedCaseTable becomes mymixedcasetable just because you forgot to quote.

    And yes, this does make copy pasting SQL from any other system a pain.

  • (nodebb) in reply to 516052

    It's not quite that bad, because the only way for the table name to be mixed case is if you quoted the name when you created the table. So the rule is pretty simple - either always quote the table name or never quote the table name.

  • 516052 (unregistered) in reply to Dragnslcr

    It's still a gross departure from expected behavior for any developer familiar with SQL. And it's made worse by the fact that all available admin tools including their own PGAdmin automatically add quotes without telling you. Together these two make for one hell of a trap that basically permanently and unfixably infects your DB with quotes that make it incompatible with external SQL.

    Or in short. I hate it.

  • (nodebb) in reply to 516052

    Oh, don't get me wrong, I don't like it either. It sucks and can cause problems, I just don't think it's a major catastrophe. But I also only ever use snake_case for table and column names, so it doesn't affect me.

Leave a comment on “One Case”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article