The Query of Despair

  • ParkinT 2011-10-24 08:24
    Rather than a "Wooden Table", this photograph was taken on a WHITEBOARD.
  • Spivonious 2011-10-24 09:07
    ...

    That's insane.

    captcha: populus - boring game
  • trtrwtf 2011-10-24 09:07
    Probably true.
  • The poop of DOOM 2011-10-24 09:07
    Fr1st two words: SELECT ( SELECT. That's already very promising.

    Also, not fr1st!
  • Matt 2011-10-24 09:08
    Holy Fuck, no human could write that!
  • SilentRunner 2011-10-24 09:13
    I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.
  • Nagesh 2011-10-24 09:13
    Matt:
    Holy Fuck, no human could write that!

    You are being corect: this is most likly Hibarnate.
  • frits 2011-10-24 09:13
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
  • Ale 2011-10-24 09:15
    I've already seen a ten-page-long query. And it was used in a real application.
    Thankfully, I've never had to debug it.
  • olaf 2011-10-24 09:15
    Why not tidysql the query instead of printing it out unreadable? (or printing it out at all)
  • C-Octothorpe 2011-10-24 09:16
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
  • trollsolo 2011-10-24 09:16
    I've seen many things do this. Use a beautifier. Sure it'll use more paper, but stable it together and it's worth it.
  • gobes 2011-10-24 09:17
    olaf:
    Why not tidysql the query instead of printing it out unreadable?

    Because it would have made it longer.
  • dave 2011-10-24 09:17
    I feel for the his colleague.
  • Damien 2011-10-24 09:19
    The problem is on page 4, line 27. Isn't that obvious to everyone?
  • apaq11 2011-10-24 09:21
    The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...
  • frits 2011-10-24 09:23
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
  • veggen 2011-10-24 09:23
    Seen a query of this size that was generated by an alien logic that my colleague wrote... and strangely enough, it was the only possible way to get the desired result as the DB structure was just as alien.
  • trtrwtf 2011-10-24 09:26
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.


    I'm feeling like I know more about frits than I want to know right now.

    I'm off to have a shower.
  • Steve The Cynic 2011-10-24 09:28
    SilentRunner:
    I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.

    Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?
  • C-Octothorpe 2011-10-24 09:31
    trtrwtf:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.


    I'm feeling like I know more about frits than I want to know right now.

    I'm off to have a shower.
    That's more than I wanted to know about your relationship with frits...
  • C-Octothorpe 2011-10-24 09:31
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
  • kjordan 2011-10-24 09:33
    apaq11:
    The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...

    I'm not even sure what kind of query you would have hibernate doing that it would do it like that. Hibernate seems to do it less in big queries now, but you might have more running to populate your objects if you don't have lazy loading turned on for relationships.
  • SuperQuery 2011-10-24 09:33
    I've got one. Try being told to pull out info from 245 tables that all need to be related. To create another unassociated, mess of a db with..... :(
    Although my is spaced a lot better :)
  • frits 2011-10-24 09:34
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.
  • The poop of DOOM 2011-10-24 09:37
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

    Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

    Then again, at least I've been warned now.
  • Bas 2011-10-24 09:44
    Awesome.. the mail reads "The platform is very slow and produces a lot of errors. Could you check this?"
  • Nagesh 2011-10-24 09:44
    If this is Orcle, Tomas Kyte can certenly help with it.
  • Anon 2011-10-24 09:44
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    I'd bet on generating reports that require aggregate functions while not actually using any aggregate functions.

    My own record in reporting is a page and a half on one SQL-query (unbeautified). 7K of text, optimized the heck out of, to run in under two seconds every few hours.
  • someone 2011-10-24 09:47
    Oh my god... it's full of stars.
  • I. G. E. 2011-10-24 09:48
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

    Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

    Then again, at least I've been warned now.

    I have seen it, and I haven't a clue what scene they may be talking about. In a Van Damme movie, what kind of *censored* even notices anything except the fighting scenes?
  • Lockwood 2011-10-24 09:50
    http://tinyurl.com/QueryOfDespairReaction

    That's the first thing that sprung to mind when I saw that printout.

    Also, they need a highlighter to make it have some more colour to discern various bits of it.
  • frits 2011-10-24 09:51
    I. G. E.:
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

    Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

    Then again, at least I've been warned now.

    I have seen it, and I haven't a clue what scene they may be talking about. In a Van Damme movie, what kind of *censored* even notices anything except the fighting scenes?
    I'm sorry that I don't distinguish one type of fiction and choreography from another.
  • Canthros 2011-10-24 09:52
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?

    Regrettably, my second or third thought was, "I really hope that wasn't one I had to write." After another, closer look, it isn't (I never had to write anything against a schema with Dutch table names).

    I wound up writing a hideous abomination of an 8+-way union, once. IIRC, it was a bunch of reporting queries that needed to be squished together, so the individual queries weren't terrible, there were just a lot of them.

    It's also possible that it's a bunch of MS Access queries that have been strung together, which might explain the SELECT (SELECT thing at the start. Access has traditionally had some curiously quaint restrictions on query size that tends to turn any even moderately complex query into a maze of small, twisty queries, all alike.

    Finally, it could just be that whoever created that thing had no idea what they were up to.
  • Anon 2011-10-24 09:55
    Selffix: aggregate functions = somewhat complex aggregate functions, like AVG .. OVER ... RANGE BETWEEN .. etc.
  • C-Octothorpe 2011-10-24 09:56
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

    Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

    Then again, at least I've been warned now.
    Just remember: what has been seen, cannot be unseen.
  • D 2011-10-24 09:57
    There is no hope. The best you can do is put the beast down. Burn the document, wipe the drives, torch the building... Oh, and flush the site with holy water just to be on the safe side.
  • Anon 2011-10-24 10:01
    Canthros:
    a bunch of MS Access queries


    No. Please, just - no. *horrified*
  • boog 2011-10-24 10:03
    That's pretty bad, but in my experience such queries are usually a result of
    1) lack of SQL know-how and
    2) extensive CTRL+Cing-and-CTRL+Ving.

    This massive beast is most likely a handful of copies of several smaller-but-ever-so-slightly-different beasts all JOINed and UNIONed together. Example:

    SELECT (bunch of crappy columns)
    FROM (bunch of crappy tables)
    WHERE (bunch of crappy conditions)
    AND item_type = "XYZZY"
    UNION
    SELECT (the same crappy columns)
    FROM (the same crappy tables)
    WHERE (the same crappy conditions)
    AND item_type = "PLUGH"
    UNION
    ...

    I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.
  • C-Octothorpe 2011-10-24 10:08
    boog:
    That's pretty bad, but in my experience such queries are usually a result of
    1) lack of SQL know-how and
    2) extensive CTRL+Cing-and-CTRL+Ving.

    This massive beast is most likely a handful of copies of several smaller-but-ever-so-slightly-different beasts all JOINed and UNIONed together. Example:

    SELECT (bunch of crappy columns)
    FROM (bunch of crappy tables)
    WHERE (bunch of crappy conditions)
    AND item_type = "XYZZY"
    UNION
    SELECT (the same crappy columns)
    FROM (the same crappy tables)
    WHERE (the same crappy conditions)
    AND item_type = "PLUGH"
    UNION
    ...

    I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.
    Which is exactly why he shouldn't try to RE the bitch and should just rewrite it from scratch based on specs (HA!) or expected behavior...
  • WC 2011-10-24 10:10
    Am I the only one here that wishes he (or she) could attempt to clean that up and then refactor it? :D

    Inside that ugly mess is some beautiful code, just trying to come out.
  • Ross 2011-10-24 10:11
    Nuke the site from orbit. It's the only way to be sure.
  • Canthros 2011-10-24 10:12
    Anon:
    Canthros:
    a bunch of MS Access queries


    No. Please, just - no. *horrified*

    I think I'm wrong about that, anyway. Looks like the beginning is caused by the first column being an aggregate (count) in a correlated subquery against a different table.

    But, yeah. When I was decoding Access queries, many oaths were sworn and curses invoked.
  • Daniil S. 2011-10-24 10:12
    Just out of sheer curiosity, I really wish to see this query in full.
  • Zylon 2011-10-24 10:26
    Obligatory grammar snark:

    "I don't think anyone can help me," the his colleague wrote.

    CHOOSE ONE OR THE OTHER ALEX.
  • trtrwtf 2011-10-24 10:28
    Daniil S.:
    Just out of sheer curiosity, I really wish to see this query in full.


    As a wise man once said, "Just remember: what has been seen, cannot be unseen."
  • boog 2011-10-24 10:28
    On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.
  • trtrwtf 2011-10-24 10:30
    Zylon:
    Obligatory grammar snark:

    "I don't think anyone can help me," the his colleague wrote.

    CHOOSE ONE OR THE OTHER ALEX.


    There's only one Alex writing this, grammar boy.

    (I gots to keep up my grammar Nazi rep, yo)
  • My Name Is Missing 2011-10-24 10:30
    I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.
  • The poop of DOOM 2011-10-24 10:30
    boog:
    On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

    To apply for Representative Line, it should be a line, not six pages.
  • boog 2011-10-24 10:35
    The poop of DOOM:
    boog:
    On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

    To apply for Representative Line, it should be a line, not six pages.
    One line wrapped by the printer is still one line.
  • Ken B. 2011-10-24 10:37
    Damien:
    The problem is on page 4, line 27. Isn't that obvious to everyone?
    You must be new around here. The problem is on line 42.
  • C-Octothorpe 2011-10-24 10:38
    The poop of DOOM:
    boog:
    On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

    To apply for Representative Line, it should be a line, not six pages.
    Line breaks don't count...

    EDIT: oops, boog beat me to it... DAMN YOU BOOG!!!
  • Bob 2011-10-24 10:40
    I. G. E.:
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

    Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

    Then again, at least I've been warned now.

    I have seen it, and I haven't a clue what scene they may be talking about. In a Van Damme movie, what kind of *censored* even notices anything except the fighting scenes?

    Please try to show some sensitivity. I had a son who was *censored*, and let me assure you: it is no laughing matter.
  • Daniil S. 2011-10-24 10:44
    trtrwtf:
    Daniil S.:
    Just out of sheer curiosity, I really wish to see this query in full.


    As a wise man once said, "Just remember: what has been seen, cannot be unseen."


    I wish I had a dime for each time I had that experience
  • chernobyl 2011-10-24 10:47
    As the BASIC interpreter would say: ?REDO FROM START
  • Me 2011-10-24 10:52
    They must be using Magento...
  • boog 2011-10-24 10:55
    C-Octothorpe:
    boog:
    I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.
    Which is exactly why he shouldn't try to RE the bitch and should just rewrite it from scratch based on specs (HA!) or expected behavior...
    That's exactly the direction I was headed, right down to the HA.
  • Nederlander 2011-10-24 10:57
    Looks like a great query for EXPLAIN.
    What kind of database is configured with a statementbuffer this big?
  • Jon E. 2011-10-24 11:05
    Presented to you by the movement, "Occupy Query Engine."
  • steenbergh 2011-10-24 11:05
    This query is bigger than my mother-in-law...
  • Ratnoz Bassackwards 2011-10-24 11:09
    You've been in programming 20 years and don't know what a *query* is?

    I think I smell some BS, but just in case I'm wrong about that, here goes: It's ONE friggin' SQL statement that's MANY pages long.
  • Coyne 2011-10-24 11:10
    boog:
    That's pretty bad, but in my experience such queries are usually a result of
    1) lack of SQL know-how and
    2) extensive CTRL+Cing-and-CTRL+Ving.

    This massive beast is most likely a handful of copies of several smaller-but-ever-so-slightly-different beasts all JOINed and UNIONed together. Example:

    SELECT (bunch of crappy columns)
    FROM (bunch of crappy tables)
    WHERE (bunch of crappy conditions)
    AND item_type = "XYZZY"
    UNION
    SELECT (the same crappy columns)
    FROM (the same crappy tables)
    WHERE (the same crappy conditions)
    AND item_type = "PLUGH"
    UNION
    ...

    I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.


    Yes. The only way it could possibly be worse is if they used column and table names that were keywords:

    SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

    (DB2 SQL has no reserved words; given the right table definitions, the above is legal!)
  • boog 2011-10-24 11:11
    steenbergh:
    This query is bigger than my mother-in-law...
    *ba dum tsh*
  • trtrwtf 2011-10-24 11:12
    Me:
    They must be using Magento...


    I read "Magneto" at first, and I thought, yeah, he's the one who erases disks, isn't he? PERfect.
  • AdamJS 2011-10-24 11:15
    Is there anything you could do *other* than just rewriting the damn thing?
  • QJo 2011-10-24 11:16
    Pleasant little exercise. Great fun. On a par with replacing a DB table full of hardwired SQL statements, very similar but with subtle differences, with a stored procedure that built the SQL dynamically. Utterly unmaintainably impossible to read, but saved an entire table.
  • trtrwtf 2011-10-24 11:18
    AdamJS:
    Is there anything you could do *other* than just rewriting the damn thing?


    Find the guy who wrote it and keelhaul him?
  • fardle 2011-10-24 11:20
    dave:
    I feel for the poor sap, his colleague.


    FTTFY

    captach: iusto... iusto care about efficiency, but now that nobody else does, why should I?
  • ObiWayneKenobi 2011-10-24 11:21
    Steve The Cynic:
    Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?


    We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.
  • Sadly, I've Seen This 2011-10-24 11:26
    Matt:
    Holy Fuck, no human could write that!


    I doubt a human wrote this SQL. It looks like a program-generated query. I could list some programs at our company that make such crappy SQL.

    We have a vendor whose software creates just that kind of SQL query. It has that "SELECT (SELECT COUNT(*)...)," sub-query in the SELECT-list pattern. It writes multiple JOINs to the same table to get (key, value) pairs.

    We hired an Oracle consultant firm to tune that vendor's SQL. Their response was a firm "Re-write all the queries."
  • bob 2011-10-24 11:28
    Take off and nuke the site from orbit; it's the only way to be sure.
  • someone else 2011-10-24 11:29
    someone:
    Oh my god... it's full of stars.


    My Stars.... Its full of Gods!!!
  • C-Octothorpe 2011-10-24 11:33
    someone else:
    someone:
    Oh my god... it's full of stars.


    My Stars.... Its full of Gods!!!
    They must be crazy!
  • Tangoman 2011-10-24 11:35
    Came across a query like that once when asked to track down a bug in a report.

    Spent a couple of hours reading through it before locating a comment with the Lead Dev's name on it - he was in the office the next day so I asked for his help on it.

    "Oh don't try and debug THAT" he said. "I just sat down one night and dumped my brain out in one big splat of SQL - will probably have to do it all again to fix this issue".
  • The "Z-Guy" 2011-10-24 11:40
    Daniil S.:
    trtrwtf:
    As a wise man once said, "Just remember: what has been seen, cannot be unseen."
    I wish I had a dime for each time I had that experience
    Then you could feel like a whore, too.
  • cappeca 2011-10-24 11:42
    That's no query. It's a space station.
  • Abdul Alhazred 2011-10-24 11:44
    Curses! Yet another failed attempt to summon the almighty Cthulhu.
  • justn 2011-10-24 11:45
    Ahh, so this is what happens when you use LINQ to SQL.
  • Steve The Cynic 2011-10-24 11:49
    ObiWayneKenobi:
    Steve The Cynic:
    Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?


    We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.

    That only explains why it is *still* that big. HTF did it get that big in the first place? Complex tasks aren't (shouldn't be) *that* complex. After all, how can you possibly know you have it right? And how do you manage to even write syntactically valid SQL for something that big?
  • L. 2011-10-24 11:52
    Meh .. why do people even try to debug that kind of crap .. you take the output, reproduce it with "saner" SQL and be done with it, it'll always be shorter and less of a mental health hazard than trying to deal with the squidmonster ...
  • kikito 2011-10-24 11:53
    Obviously, the solution is removing the query and starting again.

    It's just one query.
  • Chris 2011-10-24 12:16
    Coyne:

    Yes. The only way it could possibly be worse is if they used column and table names that were keywords:

    SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

    (DB2 SQL has no reserved words; given the right table definitions, the above is legal!)

    Ow, my eyes. At least in SQL Server you have to enclose field names if they're reserved words, e.g.
    select [select], [as] from [from]

  • GrammerSnarker 2011-10-24 12:17
    Let Alex have a the life, we humans can parse it.
  • someone else 2011-10-24 12:20
    Just one query? This thing has more selects than i care to count.

    This might even reach a kind of perverse nirvana where it doesn't deviate at all from the median frequency of SQL keywords used in an average project for selects. All joined together.
  • boog 2011-10-24 12:21
    Steve The Cynic:
    ObiWayneKenobi:
    Steve The Cynic:
    Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?

    We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.

    That only explains why it is *still* that big. HTF did it get that big in the first place? Complex tasks aren't (shouldn't be) *that* complex. After all, how can you possibly know you have it right? And how do you manage to even write syntactically valid SQL for something that big?
    copy-paste-copy-paste-copy-paste

    In your over 20 years of programming experience, have you really never watched a new programmer clumsily "write" their first big SQL query?
  • boog 2011-10-24 12:23
    Coyne:
    The only way it could possibly be worse is if they used column and table names that were keywords:

    SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

    (DB2 SQL has no reserved words; given the right table definitions, the above is legal!)
    Thanks. Now I hate DB2 even more.
  • Mojo Monkeyfish 2011-10-24 12:30
    Ironically, YAGNI.
  • Hortical 2011-10-24 12:32
    As a sociopath, I would like to imagine that this query returns a result set of two columns: the first column contains a unique id number and the second column contains some CSV or XML. The query collects all possible data you could be looking for in all possible forms you could want it. Any time one wants to look up anything in the database, just run this single query and get the row with the id number of the information you want. It will be right there, in easy-to-read CSV/XML form.

    No more error-prone writing of multiple queries! Just one, solid query that will receive rigorous testing as everything depends on it! Brillant!
  • J.C. Vandamme 2011-10-24 12:33
    All you need are my butt cheeks to crack this nut.
  • PedanticCurmudgeon 2011-10-24 12:39
    Abdul Alhazred:
    Curses! Yet another failed attempt to summon the almighty Cthulhu.
    How do you know it failed?
  • seriously? 2011-10-24 12:43
    Seriously? You can't figure out why an SQL query that is 6 full pages when printed would be a problem?
  • This is not my real name 2011-10-24 12:45
    Why are the worst WTFs on this site always in Dutch...
  • Rfoxmich 2011-10-24 12:59
    WC:
    Am I the only one here that wishes he (or she) could attempt to clean that up and then refactor it? :D

    Inside that ugly mess is some beautiful code, just trying to come out.


    I doubt it.

    Captcha "inhibeo" I inhibeo you from writing crap like that.
  • frits 2011-10-24 12:59
    This is not my real name:
    Why are the worst WTFs on this site always in Dutch...
    What do you expect from a country full of dikes that little boys have to save from bursting, by plugging them with their thumbs? ;)
  • I prefer both Microsoft's portable mp3 player and my sister 2011-10-24 13:03
    frits:
    This is not my real name:
    Why are the worst WTFs on this site always in Dutch...
    What do you expect from a cuntry full of dykes and little girl have them drolling, by plugging themselves with their thumbs? ;)
    I <3 your ;)
    Or maybe I 8=====D~~~ your E(o)3
  • Nagesh 2011-10-24 13:31
    none of you peepul have working experence with Kristal reports. That's one softwear that will write complex querees and mess with your mind all time.
  • Nagesh 2011-10-24 13:40
    Spivonious:
    captcha: populus - boring game

    Could be. Populous on the other hand is awesome!
  • Tangurena 2011-10-24 13:55
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?
    I've done a couple report queries over the years that were about half this length. The worst took about 45 minutes to run as it hit multiple servers to do all sorts of aggregates for various columns. About a dozen columns were subqueries themselves. It was also used for billing so it had some crazy logic like "which customers of type X have not submitted a transaction via partner Q during the last calendar month" (with the reasoning being that partner Q would subsidize them if they did 2+ transactions in a month, otherwise we charged the customers).
  • Anketam 2011-10-24 13:58
    My only hope is that a program generated most of this query and not an actual person. I would laugh if the error is because the query is so long that the program running it is truncating part of the query off, and it just happens to truncate in such a way that the query can still be run.

    On a side note I do not think I could even test or try debugging the sql query by running it from a cmd line without it running out of buffer.
  • wisran charmendrofa 2011-10-24 14:26
    saya sangat prihatin dengan apa yang sedang di alami.
  • An innocent abroad 2011-10-24 14:33
    C-Octothorpe:
    boog:
    I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.
    Which is exactly why he shouldn't try to RE the bitch and should just rewrite it from scratch based on specs (HA!) or expected behavior...
    But... that's how I wrote it in the first place! That, and trying to match expected and observed behaviour.
  • Kuba 2011-10-24 14:37
    wisran charmendrofa:
    saya sangat prihatin dengan apa yang sedang di alami.
    Thanks for your concern. I am very concerned with what is being experienced, too :) LOL.
  • Wonk 2011-10-24 14:51
    Can someone just take that query to Mordor, and toss it in the firs of Doom?
  • C-Octothorpe 2011-10-24 15:07
    Wonk:
    Can someone just take that query to Mordor, and toss it in the firs of Doom?
    What good would throwing that at a bunch of evil Christmas trees do?
  • trtrwtf 2011-10-24 15:16
    C-Octothorpe:
    Wonk:
    Can someone just take that query to Mordor, and toss it in the firs of Doom?
    What good would throwing that at a bunch of evil Christmas trees do?


    Perhaps he's hoping it would confound the forces of evil...
    might work, I'm sure it would confound just about anyone who looked at it.
  • Mr A 2011-10-24 15:17
    Steve The Cynic:
    SilentRunner:
    I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.

    Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?


    Actually it's easy. I was landed with a piss-ridden Access arse end of a system, 15 years in the making. I traced one query and found that it relied on >200 other queries. The system had 2000 records and would regularly take 30 minutes to run a report. It happens when you give a complex IT system to a guy in marketing because 'hey - I've done this at home and it's really easy yeah!'.
  • Sinisa Milivojevic 2011-10-24 16:12
    There is a cure. Redesign entire schema and rewrite all statements.
  • C-Octothorpe 2011-10-24 16:22
    Sinisa Milivojevic:
    There is a cure. Redesign entire schema and rewrite all statements.
    Thank god you didn't suggest something ridiculous...
  • Bill C. 2011-10-24 16:36
    A six page query isn't necessarily a problem if it's reasonably well-structured. You should be able to find several blocks (unioned sections or subqueries) in it that you can highlight and test separately.

    If that query was well-formatted, though, it would probably be about fifteen pages.
  • gloin 2011-10-24 16:39
    I have the misfortune of working with a certain sailboat enthusiast's software and, sad to say, queries like this are per normal from that company.

    Capta: abigo (related to the above-referenced company)
  • TheCPUWizard 2011-10-24 16:43
    C-Octothorpe:
    someone else:
    someone:
    Oh my god... it's full of stars.


    My Stars.... Its full of Gods!!!
    They must be crazy!


    Against Stupidity...
    ...The Gods Themselves....
    ......Contend in Vain.
  • Bad Eyes 2011-10-24 16:48
    The poop of DOOM:
    Fr1st two words: SELECT ( SELECT. That's already very promising.

    Also, not fr1st!
    Something like....
    SELECT(SELECT COUNT(NOmc_id) FROM (SELECT O_NEWCARAVNAS_NC LEFT JOIN
  • Jorg 2011-10-24 16:57
    Die Email sieht verdachtig nach germanischen ... Vielleicht (wie die Deutschen tun) es muss nur ein paar sehr lange Wörter (oder Tabellennamen)

    That is, when German's haven't got a word to describe something, they run a description together into one word, so they tend to have long words for some things. Perhaps all the tablenames were such long words....
  • Herby 2011-10-24 16:57
    Blasting this from space is not enough. One needs to call in a tactical nuclear strike with a remote drone, which ought to obliterate enough to get it off of the face of the earth.

    Of course, TRWTF is probably SQL in the first place. This company should be relegated to using RPG-II for the rest of time.
  • DBA 2011-10-24 17:01
    The poop of DOOM:
    boog:
    On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

    To apply for Representative Line, it should be a line, not six pages.
    Easy query - trivial...Just a one-liner (albeit a longish oneliner)
  • Grant Fritchey 2011-10-24 17:05
    I want to see the execution plan. Please.
  • Some one 2011-10-24 17:07
    This is not my real name:
    Why are the worst WTFs on this site always in Dutch...
    Because there's noone from India on this site to submit.
  • boog 2011-10-24 17:08
    Grant Fritchey:
    I want to see the execution plan. Please.
    1. Find the original programmer.
    2. Execute him.

    End of plan.
  • da Doctah 2011-10-24 17:16
    The reason this doesn't happen more often isn't because it's a WTF. It's because whoever's name is on the code only gets credit for writing one line of code.
  • Grant Fritchey 2011-10-24 17:16
    And imagine how long it takes to compile. I saw an 86 table join that took 3.5 minutes to compile (ran like a clock afterwards though). This monster could beat that easily I'm sure.
  • mudimba 2011-10-24 17:48
    It could be worse . . . at least it is not a regex
  • Ben 2011-10-24 17:49
    I wrote a query like that once. It was for a report that the business requested. It was the same query unioned about 12 times (one for each of the cases they had) where each query had about 30 joins to get the requested data. It also took about 30 minutes to run, but did do a job that 4 people used to take a week to do. I printed it out once and it took 8 pages (nicely formatted though).

    I was so proud of it when I finished I ended up stripping all newlines inverted the text colour so it was white on black and saved it into an image which I used as my wallpaper for a while.
  • airdrik 2011-10-24 18:19
    Jorg:
    Die Email sieht verdachtig nach germanischen ... Vielleicht (wie die Deutschen tun) es muss nur ein paar sehr lange Wörter (oder Tabellennamen)

    That is, when German's haven't got a word to describe something, they run a description together into one word, so they tend to have long words for some things. Perhaps all the tablenames were such long words....

    So to counter that the German's have been working on simplifying their language in a process with the fittingly concise and descriptive name of vereinfachungsbestrebung!

    So to fix this query they merely need to do some simplifylongquerybyrestructuringandremovingredundancy, or some similar.
  • Obscure Asimov Reference 2011-10-24 18:40
    Aye, Robot.
  • Nagesh 2011-10-24 21:31
    Jorg:
    Die Email sieht verdachtig nach germanischen ...

    Looks Dutch to me. Matches with the name "Jeroen", too.
  • Well.. 2011-10-24 21:52
    Sound like business as usual to me, nothing to see here folks move along...

    sino: a conjunction of sin and no, count me out.
  • scott 2011-10-24 22:00
    "SELECT ( SELECT" Nothing necessarily wrong with that part.
  • scott 2011-10-24 22:02
    Grant Fritchey:
    I want to see the execution plan. Please.


    Not like 98% of the developers that read this site ever give a crap about execution plans...
  • Salami 2011-10-25 00:11
    A query like that has to grow over time. It starts out as a complicated query, then someone spots a bug in a certain case or wants to add a field. Instead of fixing the query and risking breaking something else, it gets wrapped in parentheses as a subquery, because that is "safer". Repeat 10 or 12 times and you have a 6 page query.

  • Cheong 2011-10-25 00:27
    There IS a reason for programmers doing maintenance project have a temptation to rewrite everything from scratch.
  • My Name Is Here 2011-10-25 00:43
    My Name Is Missing:
    I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.
    I suspect that it was a problem with the management of the database (collecting stats, not indexed properly etc.) or skillset of the programmer (shit SQL).
    Even with a shitty DB design, if you can move the data off the disk, across the network and do it faster in memory on a separate computer something is obviously wrong isn't it?
  • Spontaneous 2011-10-25 00:58
    This is nothing. Teradata database and tools handle queries many times longer than this.
  • Digger 2011-10-25 01:13
    Complex programs should be complex. They cannot be composed out of simple 'Hello World' snippets.
    Work harder, and someday you will grow to that level.
  • Henk 2011-10-25 02:16
    Actually, it's Dutch - probably Flemish, given the way some names are spelled.

    Which brings me to another point: what happens when Monique Dewandelaer leaves the company?
  • Marcus Mönnig 2011-10-25 02:23
    I can beat this. I have a 50 pages printout of a SQL statement that was still running after 48 hours. (Hibernate...)
  • pjt33 2011-10-25 02:26
    Ben:
    I wrote a query like that once. It was for a report that the business requested. It was the same query unioned about 12 times (one for each of the cases they had) where each query had about 30 joins to get the requested data.

    Was it not possible to write it as a loop through the cases aggregating into a temporary table and then select from that?

    I have investigating a marginally less nasty legacy query on my list of things to do for one project. It provides the data for the dashboard page of the admin side of a website. One query to count the total number of users, users active in a given window, countries represented, sales in different categories, this, that, and the other. Unfortunately there is no documentation, and the client can't remember the precise details of half of the values it gets back ("Does this one include test users or not?") so it's probably going to be a case of gathering requirements and redoing it largely from scratch as a dozen or so queries stitched together in application code, using the subqueries of the existing SP as a guide.
  • Shinobu 2011-10-25 02:27
    The poop of DOOM:
    To apply for Representative Line, it should be a line, not six pages.
    And thus you casually opened up the possibility that this is a Representative Six Pages.
    Jorg:
    Die Email sieht verdachtig nach germanischen
    Nope. As has already been noted, it's Dutch.
  • pjt33 2011-10-25 02:29
    My Name Is Here:
    My Name Is Missing:
    I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.
    I suspect that it was a problem with the management of the database (collecting stats, not indexed properly etc.) or skillset of the programmer (shit SQL).
    Even with a shitty DB design, if you can move the data off the disk, across the network and do it faster in memory on a separate computer something is obviously wrong isn't it?

    I don't know about Oracle, but MS Sql Server is fairly limited in its string manipulation. I wouldn't be surprised if the missing item from the programmer's skillset was integrating .Net functions into Sql Server.
  • Bobs Lawn Service 2011-10-25 02:31
    This is what happens when SQL turns fully sentient and tries to crush the souls of us puny humans. I also think that you can fix something like that. It's just a matter of beautifying the code so that it is human readable and just take it one logical step at a time. Then you take two weeks of leave and a few visits to a shrink to recover.
  • Watson 2011-10-25 02:36
    The "Z-Guy":
    Daniil S.:
    trtrwtf:
    As a wise man once said, "Just remember: what has been seen, cannot be unseen."
    I wish I had a dime for each time I had that experience
    Then you could feel like a whore, too.
    The sort that turns ten cents for a trick.
  • +9 2011-10-25 03:07
    Spivonious:
    ...

    That's insane.

    captcha: populus - boring game


    Only mad scientist can repair something made by another mad scientist...
  • Severity One 2011-10-25 03:25
    Well, only yesterday I had to deal with a ten-way join in Oracle, with four of the joined 'tables' actually being 'with' clauses, one of which had another four-way join.

    And no, it can't be made any simpler. I blame the supplier.
  • Jigar 2011-10-25 03:26
    Yes Most probably it is Hibernate
  • ggeens 2011-10-25 03:49
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    You need a SQL programmer to achieve that.

    Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

    There are two ways to end up with such a query:

    - A query generator.

    - A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

    (BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)
  • Sir Robin The Not-So-Brave 2011-10-25 04:06
    frits:
    This is not my real name:
    Why are the worst WTFs on this site always in Dutch...
    What do you expect from a country full of dikes that little boys have to save from bursting, by plugging them with their thumbs? ;)

    It's from Belgium. The surname Vandamme is a lot more common in Belgium than in the Netherlands (Vandamme a toponym, it comes from the town of Damme in West-Flanders, near Bruges).
    If you look carefully at the query, you will find the name of a sales person, Monique Dewandeleer. She lives in Machelen according to Facebook. Unfortunately she doesn't seem to have a LinkedIn account, so I can't find out where she works. Probably some car leasing company in or near Brussels. Should be easy to find out with a bit more research.
  • Oneway 2011-10-25 04:22
    Confusing feelings of arousal?
  • just me 2011-10-25 04:29
    GrammerSnarker:
    Let Alex have a the life, we humans can parse it.

    That may be OK for you, but what about us spam bots?
  • Fadzlan 2011-10-25 04:42
    Considering that the query that long probably be deeply nested, even if you manage to beautify it, it would be severely indented that it wouldn't make a difference in readability anyway.

    Oh yeah, I don't think it will take 15 pages. My opinion is that it probably takes the whole wall. Hah!
  • Philosopher 2011-10-25 04:48
    Nagesh:
    none of you peepul have working experence with Kristal reports. That's one softwear that will write complex querees and mess with your mind all time.


    Oh, come on. If you're just going to misspell words to achieve the illusion that English is not your first language then I suggest not applying that to the name of a software product that after an install shows up on on your start menu as 'Crystal Reports' and would therefore be right in front of your nose.
  • L. 2011-10-25 05:01
    ggeens:
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    You need a SQL programmer to achieve that.

    Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

    There are two ways to end up with such a query:

    - A query generator.

    - A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

    (BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)


    Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

    And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

    Although I admit it's a lot of fun making huge queries to return the result using only one query ...
  • lucidfox 2011-10-25 05:19
    apaq11:
    The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...


    Because Hibernate is obviously the only ORM in existence.

    Look at the query closer. It uses table aliases like B and M1. It's an ORM generated query. It has to be.
  • geoffrey 2011-10-25 05:28
    well well well if it isn't SQL again rearing it's ugly little head

    generic constraint language (GCL) is far superior as queries are limited to 256 characters.
  • Gert 2011-10-25 05:42
    Time for map-reduce?
  • Rfoxmich 2011-10-25 06:13
    I have this image I can't shake in my mind of someone saying:

    CREATE VIEW MONSTER_QUERY_VIEW AS <insert the 6 pages of SQL here>

    And then the inheritors of that view wondering why it takes so long to query that 'table'... I have a further image of someone saying..no problem...this is Oracle...we'll just turn that into a materialized view..updated every ...oh...say 10 seconds...
  • Watson 2011-10-25 06:32
    Henk:
    Actually, it's Dutch - probably Flemish, given the way some names are spelled.

    Which brings me to another point: what happens when Monique Dewandelaer leaves the company?
    I agree; hardcoded strings like this are bad. Obviously the thing to do is have employee names in a table along with their roles and join that table to retrieve the name they actually want.
  • Manadar 2011-10-25 06:37
    The letter is Dutch and it says:

    Beste,

    Het platform werkt zeer traag en we krijgen opnieuw foutmeldingen.
    Kunnen jullie dit nakijken?

    Following "Error encountered" and the actual query.

    Translated this is:

    Dear,

    The platform is very sluggish and we are getting error messages again.
    Could you check this out?
  • NH 2011-10-25 06:42
    apaq11:
    The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...

    I wonder if that is something I have written...
  • Michael 2011-10-25 07:11
    SELECT ( SELECT.

    Nested queries start like that.
  • zmb 2011-10-25 07:16
    I know who wrote it: Chuck Norris.
  • JiP 2011-10-25 07:43
    There is a theory which states that the first thing you have to do when users complain about performance, is to replace the workstations and servers with far faster ones.
    Another theory states that this has already happened.
  • JiP 2011-10-25 07:46
    But seriously,

    it's probably just a badly-escaped or non-quoted smiley somewhere. Hard to pick out between all the semicolons and brackets that are supposed to be there...

    ;)
  • Not L. 2011-10-25 07:51
    L.:
    ggeens:
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    You need a SQL programmer to achieve that.

    Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

    There are two ways to end up with such a query:

    - A query generator.

    - A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

    (BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)


    Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

    And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

    Although I admit it's a lot of fun making huge queries to return the result using only one query ...

    Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
    Anyone with some knowledge of SQL will know that.
  • Watson 2011-10-25 08:21
    Not L.:
    L.:
    ggeens:
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    You need a SQL programmer to achieve that.

    Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

    There are two ways to end up with such a query:

    - A query generator.

    - A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

    (BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)


    Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

    And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

    Although I admit it's a lot of fun making huge queries to return the result using only one query ...

    Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
    Anyone with some knowledge of SQL will know that.

    In that case, for "temp tables" read "views".
  • L. 2011-10-25 08:32
    Not L.:
    L.:
    ggeens:
    Steve The Cynic:
    HTF does an SQL query get complex enough to be spread over six pages?


    You need a SQL programmer to achieve that.

    Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

    There are two ways to end up with such a query:

    - A query generator.

    - A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

    (BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)


    Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

    And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

    Although I admit it's a lot of fun making huge queries to return the result using only one query ...

    Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
    Anyone with some knowledge of SQL will know that.


    You sir, live in a world where optimizers rock the hell out of everything.

    Unfortunately, sometimes one has to work with MySQL (ouch) or with queries that land on the dark side of the optimizer (be it any optimizer really).

    Of course the first step is to let the Optimizer take its shot at it.

    But oftentimes, pre-processing (as in materialized views for example) is required to provide decent speed ;)
  • pjt33 2011-10-25 08:33
    Not L.:
    L.:
    Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

    And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

    Although I admit it's a lot of fun making huge queries to return the result using only one query ...

    Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
    Anyone with some knowledge of SQL will know that.

    You seem to assume that the aim is to write a query which wastes as little CPU time as possible. The rest of us are trying to waste as little maintenance programmer time as possible.
  • ņăĝęŠĥ 2011-10-25 09:27
    These queries are very common in ORM based architecture.
  • Ol' Bob 2011-10-25 10:26
    Yeah - so..? Six pages at about 68 lines per page = 408 lines. There's a query in an app that I have to maintain occasionally that's over 1800(!!!!) lines of SQL. Granted, though, they're not as dense as the lines in this query. :-)
  • Bertie Fraser 2011-10-25 12:18
    I have seen and dealt with, one of this length. My first change sent it over the maximum length for a SPROC. Then it occurred to me to redo it in parts, and it ended up as 4 or 5 separate procedures. I can't be sure -- that was 5 years ago.
    The problem could be bigger than just a stored procedure. In my case the orginal author had incrementally created a rules engine of sorts, implementing too much business logic in the SPROC.
    First one should understand what this one is trying to do, then break it out into smaller, more focused pieces. On the way, check to see that the tables are normalized sufficiently for the application.
    You might even need a DB Architect for this one.
  • Bertie Fraser 2011-10-25 12:21
    The answer too all computer questions and problems in the 1980's was "42"
  • trtrwtf 2011-10-25 12:36
    Why did you have to delete my man-on-man rape story?! Do you know how long that took to write?!

    I was feeling inspired, too... *kicks pebble*
  • Peter 2011-10-25 12:59
    pjt33:
    Ben:
    I wrote a query like that once. It was for a report that the business requested. It was the same query unioned about 12 times (one for each of the cases they had) where each query had about 30 joins to get the requested data.

    Was it not possible to write it as a loop through the cases aggregating into a temporary table and then select from that?
    I don't know about Ben's case, but in the one that I did, it replaced an Access routine that used to do what you're describing, although that report took about 7-8 hours to run. As crazy as that place was, the place I went to next has had 2 entries show up in this site (one of them), and at least one case that hit the US Supreme Court.
  • tom 2011-10-25 15:02
    Ross:
    Nuke the site from orbit. It's the only way to be sure.

    Thanks for the laugh :)
  • Matt Westwood 2011-10-25 15:21
    Bertie Fraser:
    The answer too all computer questions and problems in the 1980's was "42"


    Still is bloody 42 among those with no imagination.
  • Don L 2011-10-25 15:52
    It looks like DigiNotar's security plan.
    Since no managers understood it, they specifically told their employees to ignore security....
  • Ur zuney shadow 2011-10-25 16:37
    Zunesis disguised as trtrwtf:
    Why did you have to delete my man-on-man rape story?! Do you know how long that took to write?!

    I was feeling inspired, too... *kicks pebble*


    You have Pebbles around and your best idea is kicking her?

    Do you suffer from acratia?

    CAPTCHA: ideo - CAPTCHA also has a male idea!
  • Jeff Grigg 2011-10-25 18:33
    I want the source!
  • Xythar 2011-10-25 18:59
    Only six pages? Pfft, amateurs.
  • RobIII 2011-10-25 19:28
    According to Chrome this query is 56 pages large.

    I wish I had time to translate my 3-part blog about this some day; for now you'll have to do with Google-translate:

    Part I
    Part II
    Part III

    These blogentries are part of a larger concept, that's why they are actually part 5, 6 and 7.
  • Anonymouse 2011-10-25 20:52
    The real WTF: Pushpins in a whiteboard?!
  • Fernando 2011-10-25 21:54
    Almost as large as the 10K+ SQL text I found once (I know it was more then 10K because it overflowed a buffer that size) in a NYC city department.
  • skreidle 2011-10-25 23:58
    While I agree with that statement when it comes to specific programming languages that are occasionally posted here, I don't know much any DB programming--but I can see that that is a hellspawn of a single database query. :)
  • SEMI-HYBRID code 2011-10-26 05:26
    "The good news is that i've isolated the problem to a single database query. The bad news is that the application doesn't consist of anything else."
  • sxpert 2011-10-26 05:28
    the mere SIZE of the bloody SQL request is enough, I'd say... 5 pages goddamit !
  • SEMI-HYBRID code 2011-10-26 05:28
    RobIII:
    According to Chrome this query is 56 pages large.


    that was kind of disappointing, i hoped that at least half of it would be the WHERE part, would be much more fun
  • H. Walter 2011-10-26 06:47
    I think the real WTF is the fact that the query was printed out on paper.

    As stated in the text it is coming from a legacy system. This mostly implies having a little complexer data model than the usual modern Web 2.0 CRUD model on 4 tables (item, item_comment, item_attachment and user).
  • L. 2011-10-26 10:31
    RobIII:
    According to Chrome this query is 56 pages large.
    .


    I think it's time to consider our last line of defense .. let chuck norris roundhouse kick the guy who wrote that datamodel ...
  • L. 2011-10-26 10:34
    H. Walter:
    I think the real WTF is the fact that the query was printed out on paper.

    As stated in the text it is coming from a legacy system. This mostly implies having a little complexer data model than the usual modern Web 2.0 CRUD model on 4 tables (item, item_comment, item_attachment and user).


    lol .?

    Obviously the dm sucks as most datamodels used in about anything ever written . but saying older is better ?? man there's a lot of software that was written without even knowing about SQL, FK, triggers and all ...

    The older the applications, the more chances of major wtf, no relational integrity, major information duplication and overall corrupted corruptable shit quality data ...

    Web 2.0 my s, databases aren't meant as toys for webdevs anyway and people using the kind of DM you talk about are definitely not to be left close to a database.
  • Kwit 2011-10-26 11:28
    Looks like typical SQL query generated by SharePoint. Lenght of this query depends on amount of data in document library. Microsoft Support say that this is perfectly normal and recommends to remove some data from library.
  • Dave C. 2011-10-26 11:31
    My personal record is 600 lines. Since I know my limitations, I wrote a perl script to help. Otherwise, I'd probably still be debugging it.
  • Gregory 2011-10-26 11:57
    Probably this query is the result of some JOINS and subqueries between a few number of tables each one containing dozens of columns.

    Captcha: inhibeo
  • davey 2011-10-27 09:47
    Because then it would read

    SELECT 1=1

    and that's no fun at all.
  • Kenneth 2011-10-29 08:53
    Well, does if perform well ;0)
  • abdullah 2011-10-31 06:43
    I remember one time at a social function chatting with a snooty DBA. He showed contempt when I told him I prefer to do 'JOINs by code'. I know it's a bit lazy, and I do use simple joins these days, but I know why I do it.

    I should print out this article and keep it handy next time I need to defend my position.
  • Rootbeer 2011-10-31 10:35
    I had to squint at the image to determine whether it was the same six-page SQL query that was taped to a whiteboard in a former workplace of mine.

    No, it's not, and it's even worse. At least that one consisted mainly of a matrix of WHERE condition permutations, OR'ed together, such that the entire query only had to be evaulated in the most pessimal case.
  • DG 2011-10-31 14:38
    D:
    There is no hope. The best you can do is put the beast down. Burn the document, wipe the drives, torch the building... Oh, and flush the site with holy water just to be on the safe side.


    You should nuke the site from orbit, its the only way to be sure.
  • scav 2011-11-01 18:00
    I don't often say this, but Holy Cunting Fuck.
  • Jonathan Wilson 2011-11-01 21:11
    After seeing that monstrosity, I wonder if there needs to be a new entry in Guinness World Records for the "worlds longest SQL query"

    Although I bet there are probably longer monstrosities generated by "frameworks" like hibernate (the abuse of such frameworks seems like a WTF in and of itself IMO)
  • lulzSQL 2011-11-02 07:19
    Yes. And I have had to do that already. Massive views or fact tables for analytics. You have it.
  • Pablo 2011-11-09 09:22
    The wine is finished will you testing...
    "Spanish expression"

    Se te va el vino en cata

  • programer001 2011-11-09 09:49
    select * from death
  • brokentone 2011-11-09 09:55
    Yes, placing the pages inside the same barn would be really helpful!
  • Just guessing... 2011-11-09 09:59
    Something to do with fraud detection, maybe? We have a query about two pages long that chews recursively through member accounts and activities doing multiple deep joins and counts to find webs of collusion / fraudulent activity, and assign scores to each potential network.
  • Look upon me and despair 2011-11-09 10:01
    C-Octothorpe:
    frits:
    C-Octothorpe:
    frits:
    Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
    What, nausea?
    I think the colloquial term is "douche chills".
    I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
    Wallow in it: youtube.com/watch?v=CE8XKeN0zk4 - Kickboxer Jean Claude Van Damme Dance [HD]
  • Aleksey Tsalolikhin 2011-11-09 10:52
    Use htsql to collapse the query. htsql is a 5th generation language, an abstraction on top of SQL. http://htsql.org
  • jcromartie 2011-11-09 11:09
    Pfft... right here in front of me sits a query with 25 unions in it. It is ~1800 lines long, making it 37 printed pages in Calibri 10.

    Beat that.
  • jcromartie 2011-11-09 11:12
    I'm looking at my monster query here: 92 joins over 25 unions.
  • mgl 2011-11-09 11:19
    Really nasty piece of SQL code, definitely not for human-based processing. What do you think about tools that may decipher and visualize such complex queries in a more structured way, like DBClarity (http://www.microgen.com/dbclarity/)? Have you been using something similar recently?
  • Bah 2011-11-09 11:54
    Edit with TABs and LFs... half work done.
  • Shet 2011-11-09 12:26
    you're probably right in that no human wrote that
  • artuc 2011-11-09 17:06
    What was he coding: the world?
  • Eddy 2011-11-09 17:29
    This must be a generated Query from MS Access.
  • Larry Sheldon 2011-11-09 21:12
    I knew a COBOL programmer who wrote a major application program that used DMS1100 and which had one period after the period at the end of the PROCEDURE DIVISION. header.

    No, unfortunately I don't have a copy of the code-list.
  • The His 2011-11-09 21:45
    the his
  • BarofFoo 2011-11-10 01:13
    6 pages with lots of formatting and whitespace is ok.

    6 pages with no line breaks, formatting or whitespace is blurgh.
  • AJ 2011-11-10 04:50
    Probably took longer to run than debug
  • Abraham Crego 2011-11-10 09:37
    Try with DISTINCT or change all for a "HELLO WORLD" (as many Microsoft good solutions)
  • select name from users where 2011-11-10 14:09
    Oh. Dear. FAIL.

    You wrote a query to extract a ton of data, most of which you weren't interested in, and sent it over the wire (slow), in order to run your own unproven search algorithm against it.

    You have in one moment;

    1. Removed the ability of the solution to scale. The amount of data sent across the wire will determine the fastest speed of the query - and as that base set of 'relevant data' likely grows as a multiple of the desired result set, that query is likely slowing down.

    2. Removed the ability of the solution to scale. If the data exceeds your available RAM allocation, you're now writing to disk anyway. A disk on a web app platform that is probably already busy enough. Even slower.

    3. Removed the ability of the solution to scale. That 'relevant data' set is being sent over the wire every time the query is run. A web app you say? I hope you won't be surprised when your user base grow and your network grinds to a halt. Really slow.

    4. Removed the ability of the solution to scale. A good SQL engine optimises it's query execution plan according to the underlying data. It will use indexes when it makes sense, cache results when it makes sense, rewrite sub-queries into joins when it makes sense. As the underlying dataset changes, so does the optimisation. You're now at the mercy of your algorithm, your memory allocation system, your network bandwidth.

    I suggest you read the "Art of SQL" and get a clue. There were obviously some things wrong with the system before you touched it (eg schema flaws, or sql engine configurations, either of which could prevent optimisation), but you didn't fix them. You applied a band aid that will fail.

    Some people are best not allowed to do anything with a relational database.

  • daniel 2011-11-11 09:23
    All the fields from every joined table are written out in the SELECTs. Temporarily simplifying that to check out the WHERE joins or qualifiers would be useful in debugging.
  • Kavius 2011-11-12 11:21
    select name from users where:
    You wrote a query to extract a ton of data, most of which you weren't interested in, and sent it over the wire (slow), in order to run your own unproven search algorithm against it.


    I couldn't agree more. I was debugging an app once (it was taking 8 hours to pull up a weeks payroll, and 50% of the time it failed) and isolated the problem down to a query. The query looked something like this printout.

    When I requested time to study the query, I was told the query could not be the problem because the guy that wrote it had been a genius. As I studied the query, I realized the problem was that his "hand crafted" query, had been pulling all the records from the database, running them through a Java filter, and writing them back to the database. I rewrote the query in my spare time (with a where clause to only look at the current pay period), it was a fraction of the size, formatted to be readable: it took less than 2 seconds to run. I quit the next day with the statement "filter early, filter often".

    When I found out the original author was working for my prior employer, I phoned them and recommended immediate termination.
  • Dwain A Wuerfel 2011-11-24 12:08
    If he is able to find the line of code causing the error I would have to say he has a unique skill that is very marketable.
  • whar 2011-12-05 15:32
    Liz, is that you?
  • JRaymond 2011-12-22 13:57
    Last place I worked had a 76 page long stored procedure.... added one entry to the database. Not computer generated, but rather, shoddy offshore contractor generated.
  • Josiah 2012-02-03 05:49
    That is amazing. I am sure we will never know how that came to be.
  • Chris s. 2012-07-05 10:27
    That's an SQL query, which should be declarative - in other words describe what you want to get out of the system. They are usually short. This query seems to go over 10 foolscap pages. In short, it's a nightmare - so bad that I wonder how the query engine even *parsed* it, let alone ran it...
  • cy 2012-12-15 19:59
    Well at least the pages are numbered. I rarely use abbreviations but for this I make the exception. OMG
  • Cynep 2013-07-08 08:21
    It is fun way to demonstate to outsiders how tough your work is.

    As employer who debug such queries almost every day I can say only "Deal with it!"